Instructions and covered skills
Read each query pattern and plan detail carefully before choosing an answer. Keep your attention on PostgreSQL behavior rather than assumptions from another database system. Turn off notifications and avoid switching between unrelated tasks while completing the test. Consider the predicate, join condition, ordering requirement, and expected row count together. Choose the option that best explains the planner behavior or supports the stated workload. Review wording for details such as equality filters, range filters, and included columns before submitting a response.
Key Areas
This test evaluates practical PostgreSQL performance work centered on index design and execution-plan interpretation. Candidates analyze B-tree, GIN, GiST, BRIN, expression, partial, covering, and composite indexes in relation to concrete query patterns. They identify when an index can support equality predicates, range conditions, joins, ordering, grouping, and selective filters. They also recognize the significance of column order in composite B-tree indexes and the tradeoffs involved in adding included columns.
The assessment covers reading output from EXPLAIN and EXPLAIN ANALYZE. Candidates should understand scan nodes, join nodes, sort nodes, aggregate nodes, materialization, and bitmap operations. They should be able to compare estimated rows with actual rows, identify signs of stale or insufficient statistics, and distinguish planning time from execution time. Questions also address visibility-map effects on index-only scans, heap fetches, and why a sequential scan may be an appropriate plan for a large portion of a table.
Recommended Preparation
Prepare by creating representative tables and loading data with uneven distributions, duplicate values, nullable fields, and time-based columns. Run queries with EXPLAIN (ANALYZE, BUFFERS) before and after adding indexes, then compare node costs, actual rows, buffer activity, and elapsed execution time. Practice designing indexes from query requirements rather than creating an index for every column. Pay particular attention to equality predicates before range predicates in composite B-tree keys and to the conditions that permit ordered index scans.
Review PostgreSQL documentation for planner statistics, ANALYZE, extended statistics, index types, partial-index predicates, and operator classes. Work through plans involving nested loop, hash, and merge joins, and observe how table size, selectivity, available indexes, and memory settings can influence choices. The most useful preparation is repeated comparison of a query's logical requirements with the physical work shown in its plan.