دکتر کوئری SQL: تشخیص دهید چرا کوئری پایگاه‌داده شما کند است
چرا این پرامپت اهمیت دارد
Unindexed queries don't just slow down gracefully at scale — they cause cascading timeouts, exhaust connection pools, and can take down an entire service during peak traffic once row counts pass a threshold. Without a structured diagnosis, engineers often spend a full day trial-and-erroring index changes directly against a production replica. This prompt compresses that into a five-minute triage that mirrors what a senior database engineer would actually produce, including the risk/migration tradeoffs junior engineers tend to skip.
ما از آن برای چه استفاده می‌کنیم
A backend engineer notices a dashboard endpoint that used to load in under a second now takes over 4 seconds after the orders table crossed 50 million rows. They have the slow query and an EXPLAIN ANALYZE output in hand, but aren't sure whether the fix is a new index, a query rewrite, or a schema change — and don't want to guess on a production database.
پرامپت
Role: Act as a senior database performance engineer with 15 years of experience optimizing production PostgreSQL and MySQL systems at scale. Context: - Database engine and version: [e.g. PostgreSQL 16] - Relevant table schema and existing indexes: [PASTE YOUR CREATE TABLE AND CREATE INDEX STATEMENTS] - Approximate row counts for tables involved: [e.g. orders: 50M rows, users: 2M rows] - The slow query: [PASTE THE EXACT SQL QUERY] - Execution plan output: [PASTE EXPLAIN ANALYZE OUTPUT, OR WRITE "not available"] - Current runtime: [e.g. 4.2 seconds] - Target runtime: [e.g. under 200ms] Task: Diagnose why this query is slow and produce a prioritized fix plan. Constraints: - Do not suggest generic advice like "add an index" without specifying the exact index: columns, order, and type - Consider whether the query itself should be rewritten before recommending any schema change - Flag clearly which fixes require a schema migration versus which are a pure query rewrite, since migrations carry more risk and potential downtime - If the execution plan is not available, state explicitly which diagnosis steps require it and what to look for once the user provides it - Never recommend disabling data integrity constraints (foreign keys, NOT NULL, unique constraints) purely for speed Output Format: 1. Root Cause Analysis (2-4 sentences on what is actually causing the slowdown: sequential scan, missing index, N+1 pattern, bad join order, disk-spilled sort, etc.) 2. Ranked Fix List (numbered; each item states the specific change, an expected impact estimate, a risk level of low/medium/high, and whether it requires a migration) 3. Rewritten Query (if a query rewrite is part of the fix, show the complete rewritten SQL) 4. Index Recommendations (exact CREATE INDEX statements, if any are needed) 5. Verification Steps (what to check after applying the fix to confirm it actually worked)
نتیجه
Root Cause Analysis:
The query uses the existing index on customer_id, which correctly narrows the scan to this customer's roughly 180,000 historical orders. But Postgres then has to sort all 180,000 matching rows by created_at before it can apply LIMIT 50, because the index has no information about that ordering. This sort is the actual cost driver, not the initial row selection. The EXPLAIN ANALYZE output confirms it: a Sort node consuming 3.8 of the 4.2 total seconds, with "Sort Method: external merge Disk" — meaning the sort exceeded work_mem and spilled to disk, which is dramatically slower than an in-memory sort.
Ranked Fix List:
- [Low risk, no migration] Increase work_mem for this query session from 4MB to 64MB, to keep the sort in memory instead of spilling to disk. Expected impact: cuts sort time by roughly 60-70%, but does not eliminate the underlying full sort — it just makes it faster. Treat this as a stopgap, not the fix.
- [Medium risk, requires migration] Replace the single-column index on customer_id with a composite index on (customer_id, created_at DESC). This lets Postgres retrieve matching rows already sorted, eliminating the Sort node entirely. Expected impact: runtime drops from 4.2 seconds to under 50 milliseconds, based on comparable composite-index migrations at similar row counts.
Rewritten Query:
No rewrite is needed. The query itself is well-formed for what it's trying to do; the entire problem is that the index doesn't support the requested sort order.
Index Recommendations:
CREATE INDEX CONCURRENTLY idx_orders_customer_created
ON orders (customer_id, created_at DESC);Use CONCURRENTLY specifically to avoid taking a write lock on the orders table during index creation, since this is a live production table receiving continuous writes.
Verification Steps:
Re-run EXPLAIN ANALYZE once the index build completes. Confirm the plan now shows "Index Scan using idx_orders_customer_created" with no Sort node present anywhere in the plan, and confirm actual runtime is under the 200ms target. Because this adds a new composite index on a high-write table, monitor index bloat over the following 30 days and schedule a REINDEX if bloat exceeds 30%.
بیشتر توصیه‌های آنلاین برای «رفع کوئری کند من» عمومی هستند: یک ایندکس اضافه کنید، حافظه را افزایش دهید، تمام. این توصیه اغلب اشتباه است، زیرا یک علامت یکسان — یک کوئری کند — می‌تواند بسته به اینکه گلوگاه یک ایندکس گمشده، یک مرتب‌سازی سرریزشده به دیسک، یک ترتیب join نامناسب، یا یک الگوی N+1 پنهان در کد اپلیکیشن باشد، علل ریشه‌ای کاملاً متفاوتی داشته باشد. این پرامپت طوری طراحی شده که یک تشخیص واقعی را پیش از پیشنهاد هر راه‌حلی الزامی کند.
چرا این ساختار به این شکل کار می‌کند
بخش Context به‌صراحت درخواست پلن اجرا را می‌دهد، نه فقط خود کوئری را، زیرا متن کوئری به‌تنهایی تقریباً هیچ اطلاعاتی درباره دلیل کندی آن نمی‌دهد. بدون EXPLAIN ANALYZE، هر تشخیصی صرفاً یک حدس در لباس پاسخ است.
بخش Constraints به این دلیل وجود دارد که رایج‌ترین حالت شکست در توصیه‌های پایگاه‌داده، پیشنهاد «یک ایندکس اضافه کنید» بدون مشخص‌کردن ستون‌ها، ترتیب آن‌ها و دلیل آن است. ترتیب ستون‌ها در یک ایندکس مرکب صرفاً ظاهری نیست — ایندکسی روی (created_at, customer_id) کاملاً متفاوت از ایندکسی روی (customer_id, created_at) برای همان کوئری عمل می‌کند. محدودیتی که تمایز بین مهاجرت‌های اسکیمای دیتابیس و بازنویسی‌های خالص کوئری را الزامی می‌کند، اهمیت دارد چون مهاجرت اسکیما روی جدولی با ۵۰ میلیون ردیف می‌تواند به معنای دقایقی قفل‌شدگی یا ساعت‌ها ایندکس‌گذاری مجدد در پس‌زمینه باشد، در حالی که بازنویسی کوئری معمولاً تغییری بدون قطعی است.
تطبیق این پرامپت با استک شما
این پرامپت همان‌طور که نوشته شده برای PostgreSQL و MySQL کار می‌کند، اما همین ساختار با هر موتور کوئری‌ای که مفهوم پلن اجرا داشته باشد — از جمله Snowflake، BigQuery و SQL Server — با تغییر فرمت پلن اجرا در بخش Context، قابل تطبیق است.