PostgreSQL 18 changed SQL query optimization in September 2025 by adding skip scan on multicolumn B-tree indexes and an async I/O subsystem that delivers up to 3x read performance. If you write SQL against PostgreSQL in 2026 and have not updated your indexing strategy since 2023, you are leaving easy wins on the table. The same applies to MySQL, SQL Server, and Oracle: the engines moved, the AI tooling around them matured, but the human practice of "EXPLAIN ANALYZE then add an index" has not changed. See Foreign Keys in MySQL for more. See Microsoft's official blog post on the subject for more. See Oracle Autonomous Database for more. See Snowflake for more. See Redis for more. See Memcached for more. See AI's impact on SQL optimization and its future for more.
I work with engineering teams on database performance. The pattern is consistent. Most "slow query" problems are missing or misused indexes. The remaining 20% are query structure problems (Cartesian joins, N+1 patterns, OR conditions the planner cannot use). Below is the 2026 SQL optimization workflow, the indexes that matter, the AI tools that actually help, and the anti-patterns to delete.
Quick reference: SQL optimization in 2026
| Item | Detail |
|---|---|
| First diagnostic | EXPLAIN (ANALYZE, BUFFERS) |
| Postgres 18 unlock | Skip scan on multicolumn B-tree, async I/O |
| Best AI optimizer | EverSQL (free tier + paid) |
| Best EXPLAIN visualizer | pgMustard ($79/user/month team) |
| Common anti-patterns | SELECT *, OR conditions, missing indexes on FK joins |
The diagnostic workflow that always works
Five steps, in order:
1. Identify the slow query: Postgres `pg_stat_statements`, MySQL slow query log, SQL Server Query Store. Find the actual queries causing pain.
2. Run EXPLAIN ANALYZE BUFFERS: Not just EXPLAIN. The BUFFERS option shows real I/O, not estimated cost. ANALYZE actually executes the query.
3. Look for the most expensive operation: Sequential scans on large tables, hash joins with massive memory use, sorts that spill to disk.
4. Add or modify an index: Most of the time, the fix is an index that covers the WHERE clause and JOIN conditions.
5. Re-run EXPLAIN ANALYZE: Verify the planner now uses the index. Verify the actual time dropped.
This sequence solves 80% of slow query problems. Skip step 2 (BUFFERS) and you miss real I/O. Skip step 5 (verify) and you assume you fixed something you did not.
Indexes that matter in 2026
Five index types worth knowing:
B-tree (default): For equality and range queries. The default in every database. Covers most cases.
Covering index (INCLUDE columns): Index that includes additional columns to satisfy queries from the index alone, no table lookup. Massive performance win for frequently-run reads.
Partial index (WHERE clause): Index on a subset of rows. Smaller, faster, useful for queries that always filter by the same condition (e.g., `WHERE status = 'active'`).
Expression index: Index on the result of a function or expression (e.g., `LOWER(email)`). Useful when queries always wrap the column in a function.
GIN/GiST: For full-text search, JSONB, arrays. Postgres 18 added parallel GIN index builds.
The mistake I see: assuming "more indexes equals faster." Each index slows writes and bloats storage. Audit unused indexes quarterly with `pg_stat_user_indexes` (Postgres) and drop the ones that never trigger.
What PostgreSQL 18 changed for query optimization
Released September 2025. Three changes that matter:
Skip scan on multicolumn B-tree: An index on (A, B) can now be used efficiently when WHERE filters by B alone. Previously you needed an index on (B) or to add A to the WHERE. This eliminates a class of historic indexing pain.
Async I/O subsystem: Up to 3x read performance on workloads that benefit from concurrent disk reads. Especially valuable for analytical queries scanning large tables.
Parallel GIN index builds: Building GIN indexes on large JSONB or text columns is now parallelized. Schema migrations on large tables are dramatically faster.
If you run Postgres 17 or older in 2026, test Postgres 18 in staging. The performance lift on read-heavy workloads is substantial without query changes.
AI query optimization tools
Four worth knowing in 2026:
EverSQL: AI SQL optimizer. Free tier plus paid plans. Reads your query, suggests index changes and query rewrites. 100,000+ engineers use it. Strong for Postgres and MySQL.
pgai (Timescale): Open source Postgres extension for in-DB embeddings and LLM calls. Useful when you want to add AI capabilities directly in the database, not as a separate service.
pgMustard: EXPLAIN ANALYZE visualizer. $79/user/month team plan. Turns dense EXPLAIN output into actionable suggestions. The best EXPLAIN reader in 2026.
SQL Server Copilot: Bundled with Microsoft Fabric and Azure SQL. Suggests query rewrites and index changes. Best for Microsoft-native shops.
For most teams: EverSQL for AI suggestions, pgMustard for EXPLAIN reading. Both pay back in saved DBA time.
Common SQL anti-patterns to delete
Five patterns that hurt performance:
SELECT * in production code: Pulls every column. Hurts memory, network, and prevents the planner from using covering indexes. Always select specific columns.
OR conditions across columns: `WHERE a = 1 OR b = 2` often defeats indexing. Rewrite as `UNION ALL` of two queries when each can use an index.
Functions in WHERE clauses: `WHERE LOWER(email) = 'x'` cannot use a normal index on email. Either add an expression index on `LOWER(email)` or store data already lowercased.
N+1 query patterns: One query fetches a list, then a query per item fetches details. Should be a single JOIN. Most ORMs make this easy to write and easy to miss.
Implicit type casts in JOINs: Joining on columns of different types forces a cast that defeats indexes. Match types explicitly.
The mistake I see: focusing on query structure when the real problem is missing indexes on foreign-key columns. Most ORMs do not auto-index FKs. Audit and add them.
When to denormalize
Three cases where denormalization is right:
Read-heavy reporting workloads: Materialized views or pre-computed summary tables refreshed on schedule. Postgres 18's async I/O makes refresh faster.
Hot-path queries with stable joins: Cache the join result in a denormalized table. Update on write.
Analytics that always group by the same dimensions: Pre-aggregate at write time.
What does not work: denormalizing without strict update discipline. Stale denormalized data hurts more than slow normalized queries.
How to optimize for write-heavy workloads
Different tradeoffs from read optimization:
Minimize indexes: Each index slows inserts. Audit and drop unused ones.
Batch inserts: Use `INSERT ... VALUES (...), (...), (...)` or `COPY` instead of one INSERT per row.
UNLOGGED tables for ephemeral data: Skip WAL writes. Survives crashes by being empty, not corrupted.
Partition large tables: By date or by tenant. Drops on partitions are instant, queries on recent data scan less.
Tune `work_mem` and `maintenance_work_mem`: Defaults are conservative. For ETL workloads, raise both.
What changed in 2025-2026
Three real shifts:
PostgreSQL 18 skip scan eliminated a class of indexing pain: Multicolumn indexes are easier to design. The "always put the most selective column first" rule is less critical now.
EverSQL and pgMustard matured: AI tools that suggest query rewrites and index changes are now production-grade. Worth the cost for most engineering teams.
Async I/O changed read performance assumptions: Postgres 18 reads benefit from concurrent disk I/O in ways that earlier versions did not. Re-benchmark workloads after upgrade.
FAQ
How do I find slow SQL queries in Postgres?
Enable `pg_stat_statements`. Query it for the highest total time queries. Then run `EXPLAIN (ANALYZE, BUFFERS)` on each slow query to see actual I/O and execution time.
What is the biggest SQL optimization win in 2026?
For most teams: missing indexes on foreign-key columns. Most ORMs do not auto-index FKs. Adding them turns sequential scans into index lookups. The single biggest performance win on most production schemas.
Should I upgrade to PostgreSQL 18 in 2026?
Yes if you can test in staging first. Skip scan on multicolumn B-tree and async I/O deliver real performance lift on read-heavy workloads. Parallel GIN index builds speed up schema migrations.
Are AI SQL optimizers worth the cost?
EverSQL's free tier is enough to evaluate the value. For teams with multiple slow queries per week, paid plans (or pgMustard at $79/user/month) save real DBA time. Skip if your team is small and queries are stable.
What is the best way to learn SQL query optimization?
Practice with EXPLAIN ANALYZE on slow queries from your own production database. Read pgMustard or EverSQL suggestions and understand why each recommendation works. Specific knowledge of your schema beats generic SQL theory. See expert guide on how to optimize SQL queries for more.
Stop overpaying for AI tools you barely use. See how Dupple X helps your team adopt AI without the bloat.
