3.2Performing tuning

When parameter tuning alone hits a ceiling, you need to dig into the execution plan and index design itself. The Gold exam probes the knowledge needed to steer execution plans: why a given scan method is chosen, and how to design so the planner picks the plan you want.

3.2.1Index Only Scan, function indexes, partial indexes

• Index Only Scan is a scan method that can skip accessing the table itself (the heap) when every column the query needs is already in the index. However, visibility must still be confirmed: if the target page is not marked in the Visibility Map as "all rows visible to all transactions," the heap must be consulted anyway.
• The Visibility Map is an auxiliary structure recording, per table page, one bit for whether all tuples are visible to all transactions—updated when VACUUM runs. Tables with heavy row churn rarely get the bit set, limiting Index Only Scan's benefit, so regular VACUUM (properly functioning autovacuum) directly determines how much Index Only Scan helps.
• A function index (e.g. CREATE INDEX ... ON t (lower(col))) is built on the result of an expression or function, speeding up searches like WHERE lower(email) = 'x' that a plain index on the column cannot serve. A partial index (e.g. CREATE INDEX ... ON t (col) WHERE status = 'active') covers only rows meeting a condition, making searches limited to that subset lighter and faster.

3.2.2Partitioning, planner control, and non-disruptive operations

• Partitioning splits a large table into multiple physical tables by RANGE, LIST, HASH, or similar criteria. Partition pruning lets the planner exclude irrelevant partitions from a scan based on the query's conditions, and dropping an entire old partition is a fast way to lighten maintenance.
• enable_* parameters (e.g. enable_seqscan, enable_indexscan, enable_hashjoin) are debugging/diagnostic switches that temporarily remove a plan method from the planner's options. SET enable_seqscan = off is mainly used to investigate why a sequential scan is being chosen, not as a permanent production setting.
• random_page_cost is the planner's parameter for the relative cost of random I/O such as index-based access (default 4.0, versus seq_page_cost's default 1.0). On SSD storage, random access is not as costly as on HDD, so lowering it to around 1.1 is a standard tuning move to make the planner favor index scans more readily. hash_mem_multiplier controls how many multiples of work_mem hash-based operations (Hash Join, Hash Aggregate) are permitted to use (default 1.0).
• CREATE INDEX CONCURRENTLY avoids the write lock on the table that a normal CREATE INDEX takes, building the index without blocking ongoing reads and writes. It is the standard approach for adding an index to a live production table, but it takes longer and, if it fails partway, can leave an INVALID, incomplete index that must be cleaned up (dropped and rebuilt).
• FILLFACTOR is the percentage of page space deliberately left free on a table (or index) — the table default is 100 (fill completely). Lowering it to roughly 70-90 for update-heavy tables favors HOT (Heap-Only Tuple) updates, letting new row versions land on the same page and reducing index churn and bloat. REINDEX rebuilds an existing index (for a bloated index, or to repair corruption). The plain form locks the target, so in production consider REINDEX CONCURRENTLY (available from PostgreSQL 12 onward).

The real-world challenge is that these techniques often only pay off in combination. Consider: "we search frequently on a specific status value, but a B-tree index on that column keeps bloating with every update." The first candidate is a partial index (e.g. WHERE status = 'pending') narrowing the target. If the table also sees heavy updates, lowering FILLFACTOR to favor HOT updates compounds the benefit by also reducing how often the partial index itself needs updating. On the execution-plan side, if a report says "the aggregate query's selected columns are all in the index, yet table access still happens," the first suspect should be the Visibility Map: right after a bulk update, before autovacuum has run, the Visibility Map bits may not be set, so Index Only Scan gets no benefit. The fix is running VACUUM explicitly, or revisiting autovacuum parameters like autovacuum_vacuum_scale_factor. On planner control, if "the index is never used and sequential scans keep being chosen despite SSD storage," the cause is often random_page_cost left at its default 4.0, overestimating random I/O as if it were HDD-costly. Lowering it to around 1.1 brings the cost estimate closer to reality given the underlying statistics, but going too low can make the planner over-favor index scans and actually slow things down, so adjustments should be made incrementally while measuring with EXPLAIN ANALYZE. Adding an index in production should always use CREATE INDEX CONCURRENTLY, but it cannot run inside a transaction block, and if it errors partway it can leave an INVALID, incomplete index behind—so checking status via \d or the system catalogs and re-running after a DROP INDEX if needed is standard practice. Likewise, REINDEX CONCURRENTLY (from PostgreSQL 12 onward) is the standard way to rebuild a bloated index without downtime, preferred in production over the exclusive lock a plain REINDEX takes.

3.2.3Section summary

• Index Only Scan depends on the Visibility Map (implying regular VACUUM). Function index = index on an expression; partial index = only qualifying rows. Partitioning benefits from pruning and bulk DROP
• enable_* are temporary investigative switches; lower random_page_cost on SSD to favor index scans; hash_mem_multiplier is the work_mem multiplier for hash operations. CREATE INDEX CONCURRENTLY / REINDEX CONCURRENTLY enable non-disruptive operations; lowering FILLFACTOR favors HOT updates