Main Facts: The Elusive Cost of PostgreSQL Query Volatility

In enterprise software engineering, few moments are as jarring as the midnight Pages-Duty alarm triggered by a production query that has functioned correctly for months, suddenly seizing up a database cluster. Recently, a deep-dive investigation into a 60-million-row JSONB table tracking United States Federal Election Commission (FEC) financial disclosures exposed a fascinating and unnerving reality of the PostgreSQL query planner: the cost-based optimizer can experience dramatic personality changes between identical executions, even when schema, indexes, and underlying row counts remain entirely unchanged.

The core issue centers on a standard lookup querying itemized political contributions. Given two runs of the identical query against statistically identical data, the PostgreSQL planner formulated two drastically different execution strategies. One execution plan touched a meager 73 heap blocks, returning in milliseconds, while the alternative plan touched 3,449 heap blocks. At scale, this misjudgment balloons into significant latency variations—and in production environments, it marks the difference between a sub-second response and a high-priority P1 emergency ticket.

Crucially, this volatility is not a software bug. PostgreSQL is behaving precisely as designed. The root cause lies in how the optimizer estimates selectivity, balances index versus heap costs, and struggles with compound queries involving text search operators like ILIKE over JSONB expressions.

Chronology: Tracing the Discovery of Plan Instability

The investigation began organically when a data engineer posted to Reddit regarding performance bottlenecks on a massive silver_fec_efiling_itemizations table containing over 60 million rows. Each record stored detailed FEC itemization metadata as a JSONB payload within a record_data column.

The Diagnostic Journey

1. The Baseline Approach: Initially, developers relied on a parallel sequential scan, which required reading over 124,000 buffers (many of them cold reads) and clocking in at nearly 200 milliseconds per query on a modest 1-million-row test dataset. Scaling this linearly to 60 gigabytes of data pointed directly toward severe performance degradation.
2. The Trigram and Expression Index Attempts: Standard community reflexes suggested applying expression indexes, partial indexes, or trigram GIN indices to speed up pattern matching on the contributor_employer field. However, testing revealed that individual indexes frequently lured the planner into suboptimal paths. For instance, an expression B-tree index on (state, date) convinced the planner to estimate a return of just one row, prompting an index scan that ultimately fetched over 4,100 heap blocks and discarded 99% of them.
3. The BitmapAnd Flip: When using a combination of a state index and a trigram GIN index, subsequent benchmark draws at 2.4 million rows produced two entirely different execution plans. The first run utilized a trigram-only bitmap heap scan touching 3,449 heap blocks, while a subsequent run of the exact same query utilized a BitmapAnd intersection touching just 73 heap blocks.

This unexpected divergence proved that the planner’s internal calculations—expressed in abstract "cost units"—hinged on fractional cost margins that wobbled wildly based on minor variances in selectivity estimation.

Supporting Data: Benchmarks, Selectivity, and Heap Blocks

To validate these findings empirically, researchers utilized ExoBench, an automated benchmarking framework that provisions throwaway PostgreSQL instances, injects synthetic FEC datasets matching real-world distributions, and captures verbose execution profiles via EXPLAIN (ANALYZE, BUFFERS).

Comparative Performance Metrics

The Math Behind the Wobble

The divergence between the two plans at 2.4 million rows came down to a razor-thin margin of 19 cost units on a total estimated cost of roughly 1,250 units:

• Trigram-alone plan: 724.66 (index work) + 538.42 (heap/CPU work) = 1,263.08 total cost.
• BitmapAnd plan: 1,231.62 (index work) + 11.98 (heap/CPU work) = 1,243.60 total cost.

The intersection plan paid more upfront in index work to save massively on heap block fetches. However, because the optimizer’s estimated row count for the employer ILIKE '%MICROSOFT%' condition fluctuated wildly between draws (estimating 141 rows while actual matches exceeded 3,500), the cost calculation regularly crossed the threshold, forcing the planner to toss a coin between exceptional performance and a heavy heap-scanning penalty.

Official Responses and Technical Analysis

Database experts and PostgreSQL internals engineers emphasize that this behavior highlights a fundamental limitation of cost-based optimizers when dealing with unexpressed correlations and loose statistical samples.

When queries involve JSONB extraction operators (->> or #>>), PostgreSQL cannot natively track cross-column or intra-document correlations through standard column statistics. Consequently, the planner falls back on default selectivity guesses. If an autovacuum cycle re-samples the table, these guesses shift.

Eliminating the Decision: The Composite GIN Solution

The definitive engineering fix discovered during testing was to remove the planner’s ability to choose between conflicting indexes altogether by introducing a composite GIN index combining the btree_gin and pg_trgm extensions:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS btree_gin;

CREATE INDEX CONCURRENTLY idx_fec_state_emp_gin
  ON silver_fec_efiling_itemizations
  USING gin (
    (record_data->>'contributor_state'),
    (record_data->>'contributor_employer'::text) gin_trgm_ops
  );

By merging both predicates into a single multi-column GIN index, the database executes the intersection natively inside the index structure itself via a single Bitmap Index Scan. The planner is left with nothing to debate, dropping heap block accesses down to just 30 blocks at 1 million rows and cutting execution times down to 0.76 milliseconds—a staggering 255x performance improvement over the raw sequential scan baseline.

Implications for Production Environments

The lessons learned from debugging the FEC JSONB query extend far beyond federal election datasets. Modern application architectures frequently lean on unstructured or semi-structured data types like JSONB for flexibility, but they often neglect the hidden traps of database optimization.

Key Takeaways for Database Administrators

1. Beware the Multi-Index Trap: Providing the PostgreSQL planner with multiple independent indexes for a single query opens the door to volatile BitmapAnd or BitmapOr plan selections. If selectivity estimates wobble, plan shapes will flip.
2. Compound GIN Indexes Eliminate Ambiguity: For queries requiring filtering across multiple attributes within JSONB documents, composite GIN indices leveraging btree_gin ensure that intersections happen inside the index layer rather than at the heap level.
3. Audit Production Variance: Engineers should regularly monitor query latency stability using extensions like pg_stat_statements, calculating the ratio of standard deviation to mean execution time. High variance often signals a query living "two lives" under alternating execution plans.
4. Tune Configuration Parameters Wisely: Default settings such as random_page_cost = 4.0 (or legacy defaults) can heavily bias the planner against index scans on modern NVMe solid-state drives. However, adjusting knobs will not fix structural estimation errors caused by uncorrelated JSONB expressions.

Ultimately, query optimization is not just about writing syntax that works; it is about providing the database engine with deterministic pathways that leave zero room for mathematical coin flips.