6.2 Analyzing Query Statistics & Reindexing

6.2 Analyzing Query Statistics & Reindexing

[!IMPORTANT] The Cost-Based Optimizer (CBO): PostgreSQL's SQL execution engine does not follow hardcoded heuristic rules. Instead, it relies on a Cost-Based Optimizer that simulates hundreds or thousands of alternative execution plans, calculates the estimated disk I/O and CPU cost for each, and executes the cheapest plan. The accuracy of these cost estimates depends entirely on the quality of statistical metadata stored in the system catalog. Stale or missing statistics are the single most common cause of poor database query performance.

Alongside table statistics, maintaining physical index structures is critical. Over time, heavy transactional workloads degrade index efficiency through page splits and dead index entries. This section explores how to generate accurate statistics with ANALYZE and how to rebuild degraded indexes using REINDEX.

---

The ANALYZE Command and Reservoir Sampling

The ANALYZE command inspects tables and collects statistical data regarding the distribution of values within each column.

-- Collect statistics for an entire database
ANALYZE;

-- Collect statistics for a specific table
ANALYZE customer_orders;

-- Collect statistics for specific columns with verbose logging
ANALYZE VERBOSE customer_orders (order_date, total_amount);

Why ANALYZE Does Not Scan the Whole Table

Scanning a multi-terabyte table sequentially just to count values would cause crippling disk I/O bottlenecks. Therefore, ANALYZE implements a mathematically rigorous two-stage reservoir sampling algorithm (based on Jeffrey Vitter's random sampling algorithm):

1. It selects a uniform random sample of 8KB data pages across the entire physical relation.
2. From those sampled pages, it extracts all rows into an in-memory reservoir.
3. It extrapolates data distributions, distinct values, and frequencies with high statistical confidence.

The number of rows sampled is determined by the statistics target (governed by default_statistics_target, default 100). By default, PostgreSQL samples approximately 300 * statistics_target rows (for example, 300 * 100 = 30,000 rows). Because of this sampling method, ANALYZE executes in seconds even on tables containing billions of rows.

---

Inside pg_statistic and the pg_stats View

The raw statistical metrics collected by ANALYZE are serialized into the low-level system catalog pg_statistic. Because pg_statistic contains raw binary data arrays and could expose sensitive business data to unauthorized users, it is readable only by superusers.

To allow regular developers and administrators to safely inspect planner statistics, PostgreSQL exposes the readable public view pg_stats:

-- Inspect planner statistics for a specific column
SELECT 
    tablename,
    attname AS column_name,
    null_frac,
    avg_width,
    n_distinct,
    most_common_vals,
    most_common_freqs,
    correlation
FROM pg_stats
WHERE tablename = 'customer_orders' AND attname = 'order_status';

Core Columns in pg_stats Explained

• null_frac: The estimated fraction of column entries that contain NULL values (e.g., 0.05 indicates 5% of rows are null).
• avg_width: The average stored physical width of the column's values in bytes.
• n_distinct: The estimated number of distinct values in the column:
  • If greater than zero (> 0): An exact distinct value count (e.g., 50 states in a US state column).
  • If less than zero (< 0): A negative number representing the ratio of distinct values to total table rows (e.g., -1.0 indicates a unique column where every row is distinct; -0.5 indicates distinct values equal 50% of the row count).
• most_common_vals (MCV): An array containing the most frequently occurring values in that column.
• most_common_freqs (MCF): An array of relative frequencies corresponding to each value in the most_common_vals list.
• histogram_bounds: A list of boundary values that divide the remaining column values (those outside the MCV list) into buckets of equal population.
• correlation: The statistical correlation between the physical storage order of rows on disk and the logical sort order of the column values. A value close to +1.0 or -1.0 means the data is neatly ordered on disk, making an Index Scan extremely cheap. A value close to 0.0 means the data is randomly scattered across pages, prompting the optimizer to favor Bitmap Index Scans or Sequential Scans.

---

Tuning Statistical Precision: default_statistics_target

By default, PostgreSQL calculates up to 100 Most Common Values and 100 histogram intervals, controlled by the configuration parameter:

default_statistics_target = 100 (Permitted range: 1 to 10,000)

When 100 Is Insufficient

For columns with uniform distributions or low cardinality, 100 is sufficient. However, for columns with extreme data skew or millions of distinct keys (e.g., postal codes, customer phone prefixes, or categorical status codes where 99% of rows have one status and 1% have another), a target of 100 may fail to capture rare values in the MCV list. The optimizer will assume the rare value has a standard average frequency, drastically miscalculating row counts.

Per-Column Tuning

Rather than globally increasing default_statistics_target in postgresql.conf (which increases ANALYZE execution time and memory usage for the entire cluster), administrators can tune individual columns:

-- Increase statistical precision on a skewed column
ALTER TABLE customer_orders ALTER COLUMN postal_code SET STATISTICS 500;

-- Re-analyze the table immediately to populate the expanded target
ANALYZE customer_orders;

Increasing the target to 500 expands the MCV and histogram arrays to 500 buckets, providing the planner with far higher resolution for complex cardinality estimations.

---

The Danger of Stale Statistics

What happens when a database undergoes heavy data modifications without running ANALYZE?

1. Sequential Scan vs. Index Scan Blunders: If a table grows from 1,000 rows to 10,000,000 rows, but statistics still report 1,000 rows, the query planner will choose a Sequential Scan (assuming reading a few pages is cheaper than traversing an index), scanning 10 million rows from disk and causing severe query lag.
2. Catastrophic Join Strategy Choices: The planner uses row estimates to choose between Nested Loop joins (optimal for few rows) and Hash Joins / Merge Joins (optimal for large sets). An inaccurate estimate of 10 rows when 500,000 rows actually match will lead to a Nested Loop with an Index Scan, executing 500,000 individual index lookups and causing queries that should take milliseconds to run for hours.

---

Index Degradation, Fragmentation, and Bloat in B-Trees

Standard PostgreSQL indexes are B-Trees. A B-Tree consists of a root page, internal navigation pages, and leaf pages. Leaf pages contain IndexTuple entries holding indexed key values paired with physical Heap Item Pointers ((block_number, offset_number)).

                    [ Root Page ]
                     /         |
           [ Internal 1 ]     [ Internal 2 ]
            /        |         /        |
        [ Leaf 1 ] [ Leaf 2 ] [ Leaf 3 ] [ Leaf 4 ]

The Anatomy of Page Splits

When new rows are inserted in sequential key order (e.g., auto-incrementing identity keys), new index tuples append cleanly to the rightmost leaf page. However, when rows are inserted or updated with random key values (e.g., UUIDs, hash strings, or timestamps across disparate entities), an index tuple must be placed into a specific leaf page based on sort order.

• If that target 8KB leaf page is already full, the engine must perform a 50/50 page split:
  1. It allocates a new 8KB index block.
  2. It moves half of the existing entries to the new page.
  3. It inserts the incoming entry into the appropriate half.
  4. It inserts a routing pointer into the parent internal page.

Why VACUUM Cannot Fix Index Bloat

When rows are deleted or updated, standard VACUUM removes the dead index pointers from leaf pages. However, standard VACUUM cannot coalesce or merge sparse B-Tree leaf pages. If thousands of leaf pages are only 30% full due to historical page splits and deletions, those pages remain in the B-Tree structure indefinitely. The index remains physically oversized on disk, forcing queries to read many more index pages into shared_buffers.

---

The REINDEX Command: Scope and Locking Constraints

The REINDEX command rebuilds an index from scratch, copying live index entries into new, tightly packed pages and discarding all bloat and internal fragmentation.

Syntactical Scopes of REINDEX

-- Rebuild a single specific index
REINDEX INDEX idx_orders_customer_id;

-- Rebuild all indexes on a specific table
REINDEX TABLE customer_orders;

-- Rebuild all indexes in an entire schema
REINDEX SCHEMA billing;

-- Rebuild all indexes in the current database
REINDEX DATABASE sales_db;

-- Rebuild all system catalog indexes in the database
REINDEX SYSTEM sales_db;

Locking Behavior of Standard REINDEX

Standard REINDEX TABLE acquires a SHARE lock on the underlying table:

• SELECT Reads Allowed: Concurrent user queries can continue reading from the table using sequential scans or other un-affected paths.
• Writes Strictly Blocked: Any INSERT, UPDATE, or DELETE attempting to mutate the table is blocked until REINDEX finishes.

In a production system processing continuous writes, standard REINDEX can cause application write queues to back up, leading to connection pool exhaustion and transaction timeouts.

---

Online Index Maintenance: REINDEX CONCURRENTLY

To resolve the write-blocking problem of standard reindexing, PostgreSQL 12 introduced REINDEX CONCURRENTLY.

-- Rebuild an index online without blocking concurrent DML writes
REINDEX INDEX CONCURRENTLY idx_orders_customer_id;

-- Rebuild all indexes on a table concurrently
REINDEX TABLE CONCURRENTLY customer_orders;

Multi-Phase Execution Mechanics of REINDEX CONCURRENTLY

REINDEX CONCURRENTLY operates across multiple transactions using a non-blocking workflow:

1. New Index Creation: A new, temporary index structure (named with a suffix like _ccnew) is registered in the system catalogs.
2. Initial Index Build: The new index is built using a snapshot of the table. During this phase, it acquires only a SHARE UPDATE EXCLUSIVE lock, allowing concurrent SELECT, INSERT, UPDATE, and DELETE operations to proceed completely unhindered.
3. Catch-Up & Synchronization: In subsequent transactions, the engine catches up on any modifications made by concurrent writes that occurred during the build pass.
4. Catalog Swap & Old Index Deprecation: Once the new index is fully synchronized and validated, PostgreSQL atomically swaps the new index into place in pg_class, marks the old index as invalid (_ccold), and routes all new queries to the replacement index.
5. Drop Old Index: In a final transaction, the old, bloated index is dropped and unlinked from disk.

Tradeoffs and Operational Limitations

• Disk Space Overhead: Because the new index is built completely alongside the old one before swapping, the database filesystem must have enough free space to accommodate both the old and new index simultaneously.
• Execution Duration: Due to multiple transactions and waiting for concurrent transactions to finish, REINDEX CONCURRENTLY takes substantially longer to complete than standard REINDEX.
• Transaction Block Prohibition: Like CREATE INDEX CONCURRENTLY, REINDEX CONCURRENTLY cannot be executed inside an explicit transaction block (BEGIN ... COMMIT). It must be executed as a standalone statement.

---

Practical Administrative Workflows and Monitoring

Administrators can inspect index usage, bloat, and reindexing progress using system views:

-- Monitor active REINDEX operations in real time
SELECT 
    pid,
    phase,
    blocks_total,
    blocks_done,
    tuples_total,
    tuples_done
FROM pg_stat_progress_create_index;

-- Inspect index scan frequency to identify unused or bloated indexes
SELECT 
    schemaname,
    relname AS tablename,
    indexrelname AS indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE indisunique IS FALSE
ORDER BY pg_relation_size(indexrelid) DESC;

---

Exam Tips and Common Pitfalls

• Exam Trap: ANALYZE Full Scans: If an exam question asks whether ANALYZE scans every row of a 1TB table, the answer is no. ANALYZE uses random reservoir sampling, reading only a statistically determined subset of pages governed by default_statistics_target.
• Exam Trap: Stored Statistics Location: Planner statistics are physically stored in the catalog pg_statistic, but administrators inspect them via the view pg_stats.
• Exam Trap: REINDEX Locking Modes: Standard REINDEX TABLE takes a SHARE lock, blocking all concurrent writes (INSERT, UPDATE, DELETE). To perform online reindexing that permits concurrent writes, you must use REINDEX TABLE CONCURRENTLY.
• Exam Trap: Negative Values in n_distinct: In pg_stats, if n_distinct is a negative number (such as -0.5), it does NOT mean a corrupted count; it represents the ratio of distinct values to total table rows (50% distinct).