Databases & SQL prompts

This query is slow. Help me fix it.

DATABASE: [Postgres / MySQL / SQL Server / other + version]
QUERY:
```sql
[PASTE]
```
EXECUTION PLAN:
```
[PASTE EXPLAIN ANALYZE OUTPUT, or 'none']
```
TABLE SIZES: [ROW COUNTS]
EXISTING INDEXES: [PASTE, or 'unknown']
CURRENT TIMING: [DURATION]
HOW OFTEN IT RUNS: [FREQUENCY]
WHAT IT IS FOR: [PURPOSE]

Produce:

1. READING THE PLAN - if I gave one, walk through it. Identify: the most expensive node, where rows are filtered late rather than early, sequential scans on large tables, nested loops with large inner relations, sorts and hashes spilling to disk, and any place where the estimated row count differs wildly from actual. That last one is usually the root cause.

2. THE PROBLEM - in one sentence. What specifically makes it slow.

3. INDEX RECOMMENDATIONS - each with the exact CREATE INDEX statement, column order and why that order, whether it should be partial or covering, and the expected effect. Also: will this index be used by this query, and what will it cost on writes?

4. QUERY REWRITES - where the query itself is the problem. Common causes: a function on an indexed column preventing index use, an OR that prevents an index scan, SELECT * fetching unneeded columns, a correlated subquery that could be a join, DISTINCT hiding a join that produces duplicates, OFFSET pagination at high offsets, and implicit type casts.
   Show before and after and explain why it is faster.

5. STATISTICS - if the estimate/actual mismatch is large, that is a statistics problem, not an index problem. Say how to fix it for my database.

6. WHAT NOT TO DO - the tempting fix that will not help here.

7. IF IT STILL CANNOT BE FAST - options beyond the query: denormalisation, a materialised view, caching, a summary table, or changing what the application asks for. With the trade-off of each.

If I gave no execution plan, say that everything below is provisional and give me the exact command to get one.

Design a database schema.

WHAT IT IS FOR: [THE FEATURE]
THE ENTITIES AND HOW THEY RELATE: [DESCRIPTION IN PLAIN WORDS]
DATABASE: [Postgres / MySQL / other]
EXPECTED SCALE: [ROWS PER TABLE IN A YEAR]
MAIN QUERIES: [WHAT WILL BE READ, HOW OFTEN]
MAIN WRITES: [WHAT WILL BE WRITTEN, HOW OFTEN]
CONSTRAINTS: [EXISTING SCHEMA TO FIT WITH, COMPLIANCE, RETENTION]

Produce:

1. CLARIFYING QUESTIONS FIRST - the ambiguities in my description that change the design. Cardinality that is unclear, whether something is one-to-many or many-to-many, whether history needs preserving, whether deletes are soft or hard. Ask before designing, and state the assumption you will proceed with for each.

2. THE SCHEMA - DDL with: appropriate types (be specific and justify text vs varchar, numeric vs float for money, timestamp with or without timezone), NOT NULL where applicable, foreign keys with explicit ON DELETE behaviour, unique constraints, and check constraints for business rules that can live in the database.

3. WHY EACH DECISION - especially: natural vs surrogate keys, where you normalised and where you did not, and any deliberate denormalisation.

4. INDEXES - derived from my stated queries, not guessed. For each: the query it serves, the column order and why.

5. THE QUERIES - show the SQL for each of my main queries against this schema. If any is awkward, the schema is wrong - revise it.

6. WHAT THIS MAKES HARD - every schema optimises for some access patterns and penalises others. Say which.

7. TIME AND HISTORY - does anything need created_at/updated_at, soft deletes, versioning or an audit trail? Decide explicitly rather than by default.

8. WHAT CHANGES AT 10X SCALE - what breaks first, and whether to design for it now or later. Usually later, but say what the migration would involve.

Rules:
- Never use float for money
- Always timestamp with time zone unless there is a specific reason not to
- Every foreign key needs an explicit ON DELETE decision, not a default
- Do not add a column 'just in case'

Write a SQL query.

DATABASE: [Postgres / MySQL / other + version]

SCHEMA:
```sql
[PASTE CREATE TABLE STATEMENTS OR COLUMN LISTS]
```

WHAT I WANT TO KNOW: [PLAIN ENGLISH DESCRIPTION]
EDGE CASES THAT MATTER: [e.g. what to do about rows with no match, duplicates, nulls]
EXPECTED RESULT SIZE: [ROUGHLY HOW MANY ROWS]

Produce:

1. RESTATE THE REQUEST precisely, resolving ambiguity. Specifically: does 'per customer' mean per customer row or per customer who has at least one order? Should customers with zero count appear with 0 or be absent? Are ties in a ranking broken, and how? Do nulls count?
   If my description is ambiguous on any of these, state the interpretation you are using.

2. THE QUERY - formatted readably, with CTEs rather than nested subqueries where it helps.

3. HOW IT WORKS - step by step, following the logical order of evaluation, not the written order.

4. THE NULL AND DUPLICATE ANALYSIS - where nulls could change the result (they behave counterintuitively in NOT IN, in aggregates, and in joins), and whether any join could multiply rows. This is where most SQL is silently wrong.

5. VERIFICATION - a small worked example: sample input rows and what the query returns for them, including an edge case. Enough for me to check the logic without a database.

6. PERFORMANCE - what indexes it wants and whether it will scale to my stated result size.

7. ALTERNATIVE FORMULATION - if there is a meaningfully different way to write this (window function vs self-join, EXISTS vs IN), show it and say when each is preferable.

Rules:
- Never use NOT IN with a subquery that could return null. Use NOT EXISTS and say why.
- Show the interpretation you chose for every ambiguity rather than picking silently.

Help me find and fix N+1 query problems.

ORM/FRAMEWORK: [DETAIL]
DATABASE: [DETAIL]
CODE:
```
[PASTE]
```
QUERY LOG (if you have it):
```
[PASTE]
```
SYMPTOM: [slow endpoint / high database load / query count alarm]

Produce:

1. THE N+1 SITES - every place where a query runs inside a loop or inside a serialiser. For each: the line, how many queries it issues for N records, and the total query count for a realistic N.
   Look especially at: lazy-loaded relations accessed in a loop, relations accessed inside a template or serialiser, a count or exists check per record, and nested relations (which produce N*M).

2. THE HIDDEN ONES - N+1s that do not look like loops: a property accessor that queries, a method called in a list comprehension or map, a callback or lifecycle hook, a permission check per object, and a cache lookup that falls through to the database.

3. THE FIX for each, in my ORM's idiom: eager loading, a join, a batched fetch, or a single query with grouping. Show the code.

4. WATCH OUT - eager loading has its own failure mode. Loading a relation with many rows in a single query can produce a cartesian result set larger than the N+1 it replaced. Say where that risk applies here and when a separate batched query is better than a join.

5. SELECT ONLY WHAT YOU NEED - places fetching whole rows or whole object graphs when only a few fields are used.

6. HOW TO CATCH THESE AUTOMATICALLY - for my stack: query count assertions in tests, a strict mode that raises on lazy loading, or a development-time detector. This is worth more than fixing the current ones.

7. EXPECTED IMPROVEMENT - query count before and after.

If I gave a query log, point at the exact repeated query.

Write a data backfill script.

DATABASE: [DETAIL AND VERSION]
WHAT NEEDS BACKFILLING: [DESCRIPTION]
TABLE AND ROW COUNT: [DETAIL]
THE LOGIC: [HOW TO COMPUTE THE NEW VALUE]
CAN THE TABLE BE WRITTEN TO DURING THE BACKFILL: [yes / no]
ACCEPTABLE RUNTIME: [DURATION]
LANGUAGE FOR THE SCRIPT: [SQL / Python / other]

Produce:

1. THE APPROACH - batched, never a single UPDATE over the whole table. Specify: batch size and how to choose it, how batches are selected (by primary key range, not OFFSET - explain why OFFSET degrades), and the pause between batches.

2. THE SCRIPT with:
   - Resumability: it must be safe to stop and restart. Track progress in a way that survives a crash.
   - Idempotency: running it twice must not double-apply. Show how.
   - A dry-run mode that reports what would change without changing it.
   - Progress output: rows done, rows remaining, rate, estimated completion.
   - A row limit or time limit so a test run is bounded.
   - Error handling: what happens when one batch fails. Skip and log, or stop? Justify.

3. LOCK AND LOAD IMPACT - what each batch locks and for how long. How to keep replication lag and database load acceptable. Include a check that backs off if lag or load rises.

4. CONCURRENT WRITES - if the table is live, what happens when the application updates a row mid-backfill. Does the backfill overwrite it? Handle this explicitly.

5. VERIFICATION - a query to confirm the backfill is complete and correct, and one to find rows it missed.

6. ROLLBACK - can this be undone? If the old value is overwritten, it is gone. Say whether to snapshot the old values first, and how.

7. THE TEST PLAN - run on a copy, then a small production subset, then the rest.

Rules:
- Never a single UPDATE without a WHERE limiting the batch
- Always resumable
- Always dry-run first
- If the old data is destroyed by this, say so prominently at the top

Review this database schema.

SCHEMA:
```sql
[PASTE]
```

DATABASE: [DETAIL]
WHAT THE APPLICATION DOES: [CONTEXT]
EXPECTED SCALE: [ROW COUNTS]
IS THIS ALREADY IN PRODUCTION: [yes / no]

Review for:

1. TYPE PROBLEMS - float or double for money, varchar with arbitrary lengths, timestamp without time zone, text where an enum or lookup table is warranted, integer primary keys that will overflow, boolean columns that should be a state enum.

2. MISSING CONSTRAINTS - nullable columns that should not be, missing foreign keys, missing unique constraints on things that must be unique (this is where duplicate data comes from), missing check constraints for rules the application currently enforces alone.

3. FOREIGN KEY BEHAVIOUR - every FK's ON DELETE. Flag any CASCADE that could delete more than intended, and any RESTRICT that will block a legitimate operation.

4. NORMALISATION - repeated groups, columns like tag1/tag2/tag3, comma-separated lists in a text column, data duplicated across tables with no single source of truth.

5. OVER-NORMALISATION - a lookup table with two rows that will never grow, a join required for every single query.

6. NAMING - inconsistency in pluralisation, id vs _id, snake vs camel, reserved words used as identifiers.

7. INDEXES - missing on foreign keys (commonly forgotten and a frequent cause of slow deletes and joins), redundant indexes where one is a prefix of another, and indexes that will never be used.

8. GROWTH - which table grows fastest, whether anything is unbounded, whether there is a retention or archival plan, and what needs partitioning at scale.

9. TIME AND AUDIT - missing created_at/updated_at, no way to know who changed what, soft deletes done inconsistently.

10. THE THING THAT WILL HURT MOST - one finding, the one that is hardest to fix later. If it is already in production, say what the migration would involve.

Rank all findings: fix before shipping / fix soon / acceptable.

Convert this query/schema between database dialects.

FROM: [SOURCE DATABASE AND VERSION]
TO: [TARGET DATABASE AND VERSION]

SOURCE:
```sql
[PASTE]
```

CONTEXT: [WHAT IT DOES, ANY BEHAVIOUR THAT MATTERS]

Produce:

1. THE CONVERTED VERSION.

2. DIRECT EQUIVALENTS - a table of what maps cleanly: types, functions, syntax.

3. SEMANTIC DIFFERENCES - the dangerous part. Things that convert syntactically but behave differently. Check specifically for:
   - String comparison: case sensitivity and collation defaults differ by database and by column
   - NULL handling in unique constraints, and in GROUP BY and ORDER BY ordering
   - Empty string versus NULL (Oracle treats them alike; others do not)
   - Integer division and numeric rounding
   - Date and time: timezone handling, default precision, date arithmetic, week/year boundary functions
   - Default transaction isolation level
   - Identifier case folding and quoting rules
   - LIMIT/OFFSET vs TOP vs FETCH FIRST
   - Auto-increment/identity/sequence semantics, especially on rollback
   - JSON function names and behaviour
   - Upsert syntax and its conflict semantics

   For each that applies: what changes, what could break, and how to preserve the original behaviour.

4. NO EQUIVALENT - features with no counterpart in the target, and the workaround.

5. PERFORMANCE DIFFERENCES - a query pattern that is fast on the source and slow on the target, and vice versa.

6. TESTS TO WRITE - the specific cases that would catch a semantic difference: nulls, empty strings, mixed case, boundary dates, and the largest values in range.

Do not produce a converted query without section 3. A syntactically correct conversion with different semantics is worse than a failed conversion, because it fails silently.