Optimise a slow SQL query

Query tuning without a plan is guessing. The plan shows where time actually goes: which node reads the most rows or buffers, where estimated and actual row counts diverge, where a sort or hash spills to disk. Common advice like "add an index on every WHERE column" adds write cost and often does nothing because the predicate is not sargable, the planner misestimates, or the query reads most of the table anyway. Warehouse engines have no indexes at all, so their fixes are different.

Make this postgres query faster: [QUERY] Only if [PLAN_OUTPUT] is given: Execution plan: [PLAN_OUTPUT]

1. If there is no plan, give the exact command to capture one for postgres with actual timings (for example EXPLAIN (ANALYZE, BUFFERS) on Postgres, EXPLAIN ANALYZE on MySQL 8, the actual execution plan on SQL Server, EXPLAIN QUERY PLAN on SQLite, the query profile or execution details on BigQuery and Snowflake). Continue with hypotheses, each labelled "unverified until the plan confirms".
2. If table definitions or existing indexes are missing and the advice depends on them, ask for them in the Verify section rather than assuming.
3. Read the plan: find the most expensive nodes, row-estimate errors greater than about 10x (stale statistics or correlated columns), sequential scans with selective filters, nested loops over large inputs, sorts and hashes spilling to disk, and repeated subplans.
4. Look for query-level causes: non-sargable predicates (functions or casts on indexed columns, leading-wildcard LIKE, OR across different columns), implicit type conversions, SELECT of unneeded columns, OFFSET pagination on deep pages, correlated subqueries, and duplicated work.
5. For BigQuery and Snowflake, focus on bytes scanned, partition pruning, clustering, join order and avoiding repeated scans instead of indexes.
6. Propose changes in order of expected gain. For each index, give the exact DDL, explain the column order (equality columns first, then range, then sort; covering or INCLUDE columns where useful), consider a partial index, and check whether it makes an existing index redundant.

• Every rewrite must return the same results. Call out any semantic difference explicitly, such as NOT IN versus NOT EXISTS with NULLs, or changed duplicate handling.
• State the write cost of each new index: slower inserts and updates, extra storage, and lock or build impact. For production, use the online or concurrent build option where postgres has one.
• Expected gains are estimates unless the plan proves them. Say which.
• Read the relevant code before making a claim about it. Do not guess what a file, function or config contains.
• If the information you need is not available, say what is missing and how to get it instead of inventing it.

Diagnosis

Where the time goes, citing plan nodes and their actual numbers.

Changes

Numbered, ranked: the change, expected gain, confidence (high/medium/low).

Rewritten query

A fenced sql block, or "No rewrite needed".

Index changes

Fenced DDL for indexes to add or drop, or "None".

Trade-offs

Write cost, storage, and any semantic changes.

Verify

How to confirm the gain: the plan to re-run, the numbers to compare, and any information still needed.