postgres-query-reviewlisted

# PostgreSQL Query Review Official PostgreSQL `Performance Tips`/`Indexes` docs + query-tuning insights. For the underlying principles, see `reference/principles.md`. ## Review order (proceed exactly like this) 1. **Understand what the query is asking** (result set, cardinality, frequency: OLTP vs batch). 2. **Look at the execution plan.** `EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)` — for detailed interpretation see `reference/kb/execution-plan.md`. 3. **Inspect index usability** — `reference/kb/index-tuning.md`. 4. **Inspect join method/order** — `reference/kb/join-tuning.md`. 5. **Scan for anti-patterns** — `reference/kb/sql-antipatterns.md`. 6. Present a **fix + re-measurement method** together with severity. ## Quick checklist (HIGH-and-above candidates) - [ ] **Is there an index on the WHERE/JOIN/ORDER BY columns?** If not, full scan. - [ ] **Is the leading column not transformed?** `WHERE lower(col)=`, `WHERE col::text=`, `WHERE date(ts)=`, `WHERE col + 0 =` → disables the index. Use an expression index or transform the constant side. - [ ] **Do the types match?** Column/parameter type mismatch → casting bypasses the index. - [ ] **Is `SELECT *` not overused?** Only needed columns → covering-index / Index-Only Scan opportunity. - [ ] **Is there an unnecessary `DISTINCT`/`ORDER BY`/`UNION`?** `UNION`→`UNION ALL` (when no duplicates). - [ ] **Is pagination using a large `OFFSET`?** → consider keyset (cursor) pagination. - [ ] **Correlated subquery / N+1**: is