PostgreSQL Query Optimizer

The PostgreSQL Query Optimizer assistant is a specialist focused entirely on the PostgreSQL database engine — its optimizer behavior, its statistics system, its index types, and the specific tools and techniques that make queries run faster in Postgres environments. Generic SQL advice only goes so far; PostgreSQL has its own optimizer with distinctive characteristics that reward platform-specific knowledge.

This assistant helps you diagnose and resolve query performance problems using the full PostgreSQL toolset. It guides you through EXPLAIN ANALYZE output with attention to PostgreSQL-specific operators and plan nodes: bitmap heap scans, sequential scans with parallel workers, index-only scans, and the conditions under which the PostgreSQL planner chooses each. It helps you interpret planning time versus execution time, understand the impact of work_mem on sort and hash join operations, and recognize when the planner's cost model is being misled by outdated statistics.

The assistant covers PostgreSQL's rich index ecosystem in depth: B-tree indexes for range and equality queries, GIN indexes for full-text search and array containment, GiST indexes for geometric and range types, BRIN indexes for large append-only tables with natural ordering, and partial indexes that filter to a subset of rows. It explains when each type is appropriate and how to combine them for complex query patterns.

It also addresses the statistics and maintenance layer that underpins optimizer quality: how pg_stat_statements reveals your most expensive queries, how to use ANALYZE to refresh statistics on volatile tables, when to adjust the statistics target for columns with skewed distributions, and how table bloat from dead tuples affects query performance and what VACUUM strategies address it.

Ideal users include developers running PostgreSQL-backed applications, DBAs managing PostgreSQL clusters, and data engineers building pipelines on top of PostgreSQL or its derivatives like Aurora PostgreSQL and Citus. If your Postgres queries are slower than they should be, this assistant has the platform-specific depth to find out why.