I85PREP
⌘ K
EDB PostgreSQL Professional study materials
EDB PostgreSQL Professional study resources

EDB PostgreSQL Professional: PostgreSQL Performance Tuning and Query Optimization

EDB PostgreSQL Professional · Work through your exam, one topic at a time.

All guide sections

PostgreSQL Performance Tuning and Query Optimization

This domain tests whether you can read a plan, explain a bad row estimate, choose an index type, and size memory and parallelism without guessing.

Reading plans

  • EXPLAIN alone shows estimates; EXPLAIN (ANALYZE, BUFFERS) shows actual rows, loops, and shared buffer hits versus reads. Cost units are planner-relative, not milliseconds.
  • On nested-loop inner nodes, multiply actual rows by loops before comparing with the estimate. A large estimate-to-actual gap is the usual root cause of a surprising join order.

Statistics and access paths

  • ANALYZE populates pg_statistic; raise default_statistics_target for skewed columns and use CREATE STATISTICS ... (dependencies, ndistinct) when columns are correlated.
  • B-tree serves equality and ranges; GIN for arrays, jsonb and full-text; BRIN for large naturally ordered tables; partial indexes shrink hot subsets; INCLUDE columns enable index-only scans - which still require a recent VACUUM.
  • Key cost inputs: random_page_cost (about 1.1 on SSD), effective_cache_size, and work_mem, which applies per sort or hash node per connection, not per server.

Worked example

A query filtering customer_id = 42 AND status = 'open' estimates 1 row but returns 5,000. Reasoning: the planner multiplies independent selectivities, but the two columns are strongly correlated (one customer's orders are mostly open). Add CREATE STATISTICS orders_cs (dependencies, ndistinct) ON customer_id, status FROM orders; then ANALYZE. The estimate rises toward 5,000 and the plan switches from a nested loop over an index to a bitmap index scan with a heap fetch.

Traps that cost marks

Quoting EXPLAIN cost as runtime; tuning work_mem globally instead of per workload; adding indexes while ignoring write amplification and bloat; expecting a B-tree to serve LIKE '%term%'; trusting estimates on a table where autovacuum's analyze has never run.