I ran the same query twice and Postgres disagreed with itself

Two EXPLAIN ANALYZE runs on statistically identical data produced opposite plans, 73 heap blocks against 3,449. The margin was 1.5%. Here's the mechanism, measured with ExoBench, and the index shape that takes the coin out of the planner's hand.

By Alexander Ioffe

Summary for the Impatient

Two runs of the same query. Same schema, same indexes, same row count, statistically identical data. One plan touched 73 heap blocks, the other touched 3,449. Out at ~3 million rows that misjudgement turns into 9.3 ms when the intersection holds and 17.8 ms when it doesn't. The two candidate plans were priced 19 cost units apart on a total near 1,250, and the selectivity estimate feeding that price ranged from 140 to 14,215 rows across draws while the true count sat near 3,500. The fix that held in every run: one multicolumn GIN combining btree_gin and pg_trgm, which moves the intersection inside the index and leaves the planner nothing to decide. On the original 1M-row JSONB table it cut the query from 193.8 ms to 0.76 ms. If you want to run this loop yourself, setup is about 30 seconds: getting started.

One bright morning you execute a EXPLAIN ANALYZE and you get this:

Bitmap Heap Scan on fec_filing_lineitems  (actual time=1.670..6.821 rows=14)
  Recheck Cond: (contributor_employer ~~* '%MICROSOFT%')
  🔴 Heap Blocks: exact=3449

For no reason at all you try it again and this time you get:

Bitmap Heap Scan on fec_filing_lineitems  (actual time=4.436..4.568 rows=19)
  Recheck Cond: ((entity_state = 'MD') AND (contributor_employer ~~* '%MICROSOFT%'))
  🔴 Heap Blocks: exact=73

That's a ~50x improvement you get for free, but if it goes one way for free, it'll probably go the other way for free too! The latter runs into a P1 production call.

Same query, the same table definition. Same three indexes, same VACUUM ANALYZE, same 2.4 million rows from the same generator. For some perspective, the whole darned table only has around 30k of those heap blocks and on a whim we might pump out 10% of it for just 14 rows!

This isn't a bug, Postgres behaves exactly as designed.

The Question

Somewhere on Reddit, a data engineer deep within the bowels of the US executive branch, is wrangling a silver_fec_efiling_itemizations table with: 60M+ rows.

Here's the somewhere: PostgreSQL query on 60M-row JSONB table is slow? and the OP's identity is just my guess...

Each row in this table is a full FEC (that's the Federal Election Commission by the way) itemization record stored as JSONB in a record_data column. The workhorse query pulls individual contributions for one state, one year, one employer:

SELECT
  record_data->>'contributor_first_name' AS first_name,
  record_data->>'contributor_last_name'  AS last_name,
  record_data->>'contributor_state'      AS state,
  record_data->>'contributor_employer'   AS employer,
  (record_data->>'contribution_amount')::numeric AS amount,
  LEFT(record_data->>'contribution_date',10)::date AS contribution_date
FROM silver_fec_efiling_itemizations
WHERE record_type = 'Schedule A'
  AND record_data->>'entity_type' = 'IND'
  AND record_data->>'contributor_state' = 'MD'
  AND record_data->>'contributor_employer' ILIKE '%MICROSOFT%'
  AND record_data->>'contribution_date' >= '2025-01-01'
  AND record_data->>'contribution_date' <  '2026-01-01';

Only record_type has an index. Their question, and it's a good one: is it worth adding expression indexes and a trigram GIN, or is 60M JSONB rows fundamentally the wrong place for these queries regardless of indexing?

The comment-section reflexes write themselves. Promote everything to typed columns. Add expression indexes. Slap a trigram GIN on it. I decided to measure each one instead of arguing, and the measuring is what led to the two plans at the top.

The Received Wisdom, Weighed and Measured

All numbers below are EXPLAIN (ANALYZE, BUFFERS) execution time at 1M rows on synthetic data matching the shapes in the question, roughly 1 KB of JSONB per row across 28 keys, with realistic selectivities: 80% Schedule A, 90% individuals, about 2% per state, and a 0.15% needle for the employer match.

The baseline is a parallel seq scan: 193.8 ms and 124,706 buffers at 1M, the whole table every time. The 250K point ran entirely from cache in 46.8 ms, so at 60M that's roughly 11 seconds if the table were fully cached, and 60M rows at 1 KB is about 60 GB, so it won't be.

Benchmark SQL
benchmarkSql complete
MCP Tool✓
Benchmark
postgres28.31s compute
EXPLAIN (ANALYZE, BUFFERS) SELECT
  record_data->>'contributor_first_name' AS first_name,
  record_data->>'contributor_last_name'  AS last_name,
  record_data->>'contributor_state'      AS state,
  record_data->>'contributor_employer'   AS employer,
  (record_data->>'contribution_amount')::numeric AS amount,
  LEFT(record_data->>'contribution_date',10)::date AS contribution_date
FROM silver_fec_efiling_itemizations
WHERE record_type = 'Schedule A'
  AND record_data->>'entity_type' = 'IND'
  AND record_data->>'contributor_state' = 'MD'
  AND record_data->>'contributor_employer' ILIKE '%MICROSOFT%'
  AND record_data->>'contribution_date' >= '2025-01-01'
  AND record_data->>'contribution_date' <  '2026-01-01'
CREATE TABLE silver_fec_efiling_itemizations (
  id          bigint PRIMARY KEY,
  record_type text,
  record_data jsonb
);

INSERT INTO silver_fec_efiling_itemizations (id, record_type, record_data)
SELECT
  i,
  CASE WHEN random() < 0.80 THEN 'Schedule A'
       WHEN random() < 0.50 THEN 'Schedule B'
       ELSE 'Schedule E' END,
  jsonb_build_object(
    'form_type', 'SA11AI',
    'filer_committee_id_number', 'C00' || lpad(((random()*99999)::int)::text, 6, '0'),
    'transaction_id', 'SA11AI.' || i,
    'entity_type', CASE WHEN random() < 0.90 THEN 'IND' WHEN random() < 0.5 THEN 'ORG' ELSE 'PAC' END,
    'contributor_organization_name', '',
    'contributor_last_name', (ARRAY['SMITH','JOHNSON','WILLIAMS','BROWN','JONES','GARCIA','MILLER','DAVIS','RODRIGUEZ','MARTINEZ','HERNANDEZ','LOPEZ','GONZALEZ','WILSON','ANDERSON','THOMAS','TAYLOR','MOORE','JACKSON','MARTIN'])[(random()*19)::int+1],
    'contributor_first_name', (ARRAY['JAMES','MARY','ROBERT','PATRICIA','JOHN','JENNIFER','MICHAEL','LINDA','DAVID','ELIZABETH','WILLIAM','BARBARA','RICHARD','SUSAN','JOSEPH','JESSICA','THOMAS','SARAH','CHARLES','KAREN'])[(random()*19)::int+1],
    'contributor_middle_name', '',
    'contributor_street_1', ((random()*9999)::int)::text || ' MAIN STREET',
    'contributor_city', (ARRAY['BALTIMORE','BETHESDA','ROCKVILLE','SILVER SPRING','ANNAPOLIS','COLUMBIA','FREDERICK','GAITHERSBURG','TOWSON','BOWIE'])[(random()*9)::int+1],
    'contributor_state', (ARRAY['AL','AK','AZ','AR','CA','CO','CT','DE','FL','GA','HI','ID','IL','IN','IA','KS','KY','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ','NM','NY','NC','ND','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY'])[(random()*49)::int+1],
    'contributor_zip_code', lpad(((random()*99999)::int)::text, 5, '0'),
    'election_code', 'P2026',
    'contribution_date', to_char('2023-01-01'::timestamp + (random() * 1300 * interval '1 day'), 'YYYY-MM-DD"T"HH24:MI:SS'),
    'contribution_amount', round((random()*2900)::numeric, 2),
    'contribution_aggregate', round((random()*5800)::numeric, 2),
    'contribution_purpose_descrip', 'CONTRIBUTION',
    'contributor_employer', CASE WHEN random() < 0.0015
         THEN (ARRAY['MICROSOFT','MICROSOFT CORPORATION','MICROSOFT CORP'])[(random()*2)::int+1]
         ELSE (ARRAY['ACME','GLOBEX','INITECH','UMBRELLA','STARK','WAYNE','TYRELL','SOYLENT','MASSIVE','VANDELAY','HOOLI','CYBERDYNE','OSCORP','ABSTERGO','APERTURE','WEYLAND','NAKATOMI','SABRE','LUMON','ENCOM','RETIRED','NOT EMPLOYED','SELF EMPLOYED','NORTHROP','LOCKHEED','JOHNS HOPKINS','MARRIOTT','UNDER ARMOUR','T ROWE PRICE','MCCORMICK'])[(random()*29)::int+1]
              || ' ' || (ARRAY['INC','LLC','CORPORATION','GROUP','HOLDINGS','SYSTEMS','TECHNOLOGIES','PARTNERS','SERVICES','INTERNATIONAL'])[(random()*9)::int+1]
       END,
    'contributor_occupation', (ARRAY['ENGINEER','ATTORNEY','PHYSICIAN','TEACHER','CONSULTANT','EXECUTIVE','RETIRED','ANALYST','MANAGER','SALES'])[(random()*9)::int+1],
    'memo_code', '',
    'memo_text_description', 'EARMARKED CONTRIBUTION REPORTED ON LINE 11AI',
    'image_number', ((random()*999999999999999)::bigint)::text,
    'file_number', ((random()*9999999)::int)::text,
    'sub_id', ((random()*9999999999)::bigint)::text
  )
FROM generate_series(1, ${SCALE}) i;

CREATE INDEX idx_record_type ON silver_fec_efiling_itemizations (record_type);
VACUUM ANALYZE;
ScaleTimePlanCompute
250K46.8 ms
Parallel Seq Scan over the whole table, 31,159 buffers, every one a cache hit. This draw's needle count at 250K rounds to zero rows returned. Execution Time 46.805 ms.
QUERY PLAN
Gather  (cost=1000.00..36065.39 rows=1 width=164) (actual time=38.706..46.780 rows=0 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=31159
  ->  Parallel Seq Scan on silver_fec_efiling_itemizations  (cost=0.00..35065.29 rows=1 width=164) (actual time=36.228..36.229 rows=0 loops=3)
        Filter: ((record_type = 'Schedule A'::text) AND ((record_data ->> 'contributor_employer'::text) ~~* '%MICROSOFT%'::text) AND ((record_data ->> 'contribution_date'::text) >= '2025-01-01'::text) AND ((record_data ->> 'contribution_date'::text) < '2026-01-01'::text) AND ((record_data ->> 'entity_type'::text) = 'IND'::text) AND ((record_data ->> 'contributor_state'::text) = 'MD'::text))
        Rows Removed by Filter: 83333
        Buffers: shared hit=31159
Planning:
  Buffers: shared hit=46 dirtied=1
Planning Time: 0.188 ms
Execution Time: 46.805 ms
6.79s
1M193.8 ms
Same Parallel Seq Scan at 1M rows: 124,706 buffers, 67,513 of them read cold, 7 rows survive the filter. Execution Time 193.820 ms.
QUERY PLAN
Gather  (cost=1000.00..141330.63 rows=1 width=164) (actual time=184.943..193.793 rows=7 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=57193 read=67513
  ->  Parallel Seq Scan on silver_fec_efiling_itemizations  (cost=0.00..140330.53 rows=1 width=164) (actual time=118.601..181.945 rows=2 loops=3)
        Filter: ((record_type = 'Schedule A'::text) AND ((record_data ->> 'contributor_employer'::text) ~~* '%MICROSOFT%'::text) AND ((record_data ->> 'contribution_date'::text) >= '2025-01-01'::text) AND ((record_data ->> 'contribution_date'::text) < '2026-01-01'::text) AND ((record_data ->> 'entity_type'::text) = 'IND'::text) AND ((record_data ->> 'contributor_state'::text) = 'MD'::text))
        Rows Removed by Filter: 333331
        Buffers: shared hit=57193 read=67513
Planning:
  Buffers: shared hit=41 read=4
Planning Time: 0.240 ms
Execution Time: 193.820 ms
21.52s
Raw
Raw benchmark JSON

The wrong-table reflex dies quickly. One index, described below, took this exact query to 0.76 ms with no schema change and no query rewrite (its card sits in that section). Whatever the problem is here, it isn't JSONB.

The expression btree on (state, date) made things worse by existing. The trigram GIN was sitting right there; the planner picked the btree anyway and landed at 25.7 ms, fetching 4,154 rows from the heap and keeping 9 of them, four times slower than the index it passed over. Stacked ->> extractions get estimated at rows=1 because Postgres has no correlation stats across JSONB expressions, and an index that looks free to a planner gets used. If you build expression btrees on JSONB, pair them with CREATE STATISTICS on the expressions.

Benchmark SQL
benchmarkSql complete
MCP Tool✓
Benchmark
postgres25.09s compute
EXPLAIN (ANALYZE, BUFFERS) SELECT
  record_data->>'contributor_first_name' AS first_name,
  record_data->>'contributor_last_name'  AS last_name,
  record_data->>'contributor_state'      AS state,
  record_data->>'contributor_employer'   AS employer,
  (record_data->>'contribution_amount')::numeric AS amount,
  LEFT(record_data->>'contribution_date',10)::date AS contribution_date
FROM silver_fec_efiling_itemizations
WHERE record_type = 'Schedule A'
  AND record_data->>'entity_type' = 'IND'
  AND record_data->>'contributor_state' = 'MD'
  AND record_data->>'contributor_employer' ILIKE '%MICROSOFT%'
  AND record_data->>'contribution_date' >= '2025-01-01'
  AND record_data->>'contribution_date' <  '2026-01-01'
-- Portable no-op on standard Postgres; appends the schema ExoBench installs extensions into
SELECT set_config('search_path', current_setting('search_path') || ',extensions', false);

CREATE TABLE silver_fec_efiling_itemizations (
  id          bigint PRIMARY KEY,
  record_type text,
  record_data jsonb
);

INSERT INTO silver_fec_efiling_itemizations (id, record_type, record_data)
SELECT
  i,
  CASE WHEN random() < 0.80 THEN 'Schedule A'
       WHEN random() < 0.50 THEN 'Schedule B'
       ELSE 'Schedule E' END,
  jsonb_build_object(
    'form_type', 'SA11AI',
    'filer_committee_id_number', 'C00' || lpad(((random()*99999)::int)::text, 6, '0'),
    'transaction_id', 'SA11AI.' || i,
    'entity_type', CASE WHEN random() < 0.90 THEN 'IND' WHEN random() < 0.5 THEN 'ORG' ELSE 'PAC' END,
    'contributor_organization_name', '',
    'contributor_last_name', (ARRAY['SMITH','JOHNSON','WILLIAMS','BROWN','JONES','GARCIA','MILLER','DAVIS','RODRIGUEZ','MARTINEZ','HERNANDEZ','LOPEZ','GONZALEZ','WILSON','ANDERSON','THOMAS','TAYLOR','MOORE','JACKSON','MARTIN'])[(random()*19)::int+1],
    'contributor_first_name', (ARRAY['JAMES','MARY','ROBERT','PATRICIA','JOHN','JENNIFER','MICHAEL','LINDA','DAVID','ELIZABETH','WILLIAM','BARBARA','RICHARD','SUSAN','JOSEPH','JESSICA','THOMAS','SARAH','CHARLES','KAREN'])[(random()*19)::int+1],
    'contributor_middle_name', '',
    'contributor_street_1', ((random()*9999)::int)::text || ' MAIN STREET',
    'contributor_city', (ARRAY['BALTIMORE','BETHESDA','ROCKVILLE','SILVER SPRING','ANNAPOLIS','COLUMBIA','FREDERICK','GAITHERSBURG','TOWSON','BOWIE'])[(random()*9)::int+1],
    'contributor_state', (ARRAY['AL','AK','AZ','AR','CA','CO','CT','DE','FL','GA','HI','ID','IL','IN','IA','KS','KY','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ','NM','NY','NC','ND','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY'])[(random()*49)::int+1],
    'contributor_zip_code', lpad(((random()*99999)::int)::text, 5, '0'),
    'election_code', 'P2026',
    'contribution_date', to_char('2023-01-01'::timestamp + (random() * 1300 * interval '1 day'), 'YYYY-MM-DD"T"HH24:MI:SS'),
    'contribution_amount', round((random()*2900)::numeric, 2),
    'contribution_aggregate', round((random()*5800)::numeric, 2),
    'contribution_purpose_descrip', 'CONTRIBUTION',
    'contributor_employer', CASE WHEN random() < 0.0015
         THEN (ARRAY['MICROSOFT','MICROSOFT CORPORATION','MICROSOFT CORP'])[(random()*2)::int+1]
         ELSE (ARRAY['ACME','GLOBEX','INITECH','UMBRELLA','STARK','WAYNE','TYRELL','SOYLENT','MASSIVE','VANDELAY','HOOLI','CYBERDYNE','OSCORP','ABSTERGO','APERTURE','WEYLAND','NAKATOMI','SABRE','LUMON','ENCOM','RETIRED','NOT EMPLOYED','SELF EMPLOYED','NORTHROP','LOCKHEED','JOHNS HOPKINS','MARRIOTT','UNDER ARMOUR','T ROWE PRICE','MCCORMICK'])[(random()*29)::int+1]
              || ' ' || (ARRAY['INC','LLC','CORPORATION','GROUP','HOLDINGS','SYSTEMS','TECHNOLOGIES','PARTNERS','SERVICES','INTERNATIONAL'])[(random()*9)::int+1]
       END,
    'contributor_occupation', (ARRAY['ENGINEER','ATTORNEY','PHYSICIAN','TEACHER','CONSULTANT','EXECUTIVE','RETIRED','ANALYST','MANAGER','SALES'])[(random()*9)::int+1],
    'memo_code', '',
    'memo_text_description', 'EARMARKED CONTRIBUTION REPORTED ON LINE 11AI',
    'image_number', ((random()*999999999999999)::bigint)::text,
    'file_number', ((random()*9999999)::int)::text,
    'sub_id', ((random()*9999999999)::bigint)::text
  )
FROM generate_series(1, ${SCALE}) i;

CREATE INDEX idx_record_type ON silver_fec_efiling_itemizations (record_type);

-- Candidate A: partial composite expression btree on (state, date)
CREATE INDEX idx_fec_state_date ON silver_fec_efiling_itemizations
  ((record_data->>'contributor_state'), (record_data->>'contribution_date'))
  WHERE record_type = 'Schedule A' AND record_data->>'entity_type' = 'IND';

-- Candidate B: trigram GIN on employer
CREATE INDEX idx_fec_employer_trgm ON silver_fec_efiling_itemizations
  USING gin ((record_data->>'contributor_employer') gin_trgm_ops);

VACUUM ANALYZE;
ScaleTimePlanCompute
1M25.7 ms
The lure, reproduced. With the trigram GIN sitting right there, the planner picks the (state, date) expression btree at an estimated rows=1, fetches 4,154 rows, and discards 4,145 of them. Execution Time 25.715 ms across 4,178 buffers.
QUERY PLAN
Index Scan using idx_fec_state_date on silver_fec_efiling_itemizations  (cost=0.42..8.48 rows=1 width=164) (actual time=3.323..25.688 rows=9 loops=1)
  Index Cond: (((record_data ->> 'contributor_state'::text) = 'MD'::text) AND ((record_data ->> 'contribution_date'::text) >= '2025-01-01'::text) AND ((record_data ->> 'contribution_date'::text) < '2026-01-01'::text))
  Filter: ((record_data ->> 'contributor_employer'::text) ~~* '%MICROSOFT%'::text)
  Rows Removed by Filter: 4145
  Buffers: shared hit=1780 read=2398 written=2220
Planning:
  Buffers: shared hit=72 read=5
Planning Time: 0.492 ms
Execution Time: 25.715 ms
25.09s
Raw
Raw benchmark JSON

The trigram GIN alone was my own first answer, and it's wrong in a way that took a second opinion to expose. It runs in 6.5 ms, but the plan fetches 1,080 heap blocks, essentially the nationwide population of rows matching %MICROSOFT%, then filters that pile down to 5 rows. Its cost scales with how many Microsofts exist in the country, and it doesn't care how narrow your state and date filters are.

Benchmark SQL
benchmarkSql complete
MCP Tool✓
Benchmark
postgres23.97s compute
EXPLAIN (ANALYZE, BUFFERS) SELECT
  record_data->>'contributor_first_name' AS first_name,
  record_data->>'contributor_last_name'  AS last_name,
  record_data->>'contributor_state'      AS state,
  record_data->>'contributor_employer'   AS employer,
  (record_data->>'contribution_amount')::numeric AS amount,
  LEFT(record_data->>'contribution_date',10)::date AS contribution_date
FROM silver_fec_efiling_itemizations
WHERE record_type = 'Schedule A'
  AND record_data->>'entity_type' = 'IND'
  AND record_data->>'contributor_state' = 'MD'
  AND record_data->>'contributor_employer' ILIKE '%MICROSOFT%'
  AND record_data->>'contribution_date' >= '2025-01-01'
  AND record_data->>'contribution_date' <  '2026-01-01'
-- Portable no-op on standard Postgres; appends the schema ExoBench installs extensions into
SELECT set_config('search_path', current_setting('search_path') || ',extensions', false);

CREATE TABLE silver_fec_efiling_itemizations (
  id          bigint PRIMARY KEY,
  record_type text,
  record_data jsonb
);

INSERT INTO silver_fec_efiling_itemizations (id, record_type, record_data)
SELECT
  i,
  CASE WHEN random() < 0.80 THEN 'Schedule A'
       WHEN random() < 0.50 THEN 'Schedule B'
       ELSE 'Schedule E' END,
  jsonb_build_object(
    'form_type', 'SA11AI',
    'filer_committee_id_number', 'C00' || lpad(((random()*99999)::int)::text, 6, '0'),
    'transaction_id', 'SA11AI.' || i,
    'entity_type', CASE WHEN random() < 0.90 THEN 'IND' WHEN random() < 0.5 THEN 'ORG' ELSE 'PAC' END,
    'contributor_organization_name', '',
    'contributor_last_name', (ARRAY['SMITH','JOHNSON','WILLIAMS','BROWN','JONES','GARCIA','MILLER','DAVIS','RODRIGUEZ','MARTINEZ','HERNANDEZ','LOPEZ','GONZALEZ','WILSON','ANDERSON','THOMAS','TAYLOR','MOORE','JACKSON','MARTIN'])[(random()*19)::int+1],
    'contributor_first_name', (ARRAY['JAMES','MARY','ROBERT','PATRICIA','JOHN','JENNIFER','MICHAEL','LINDA','DAVID','ELIZABETH','WILLIAM','BARBARA','RICHARD','SUSAN','JOSEPH','JESSICA','THOMAS','SARAH','CHARLES','KAREN'])[(random()*19)::int+1],
    'contributor_middle_name', '',
    'contributor_street_1', ((random()*9999)::int)::text || ' MAIN STREET',
    'contributor_city', (ARRAY['BALTIMORE','BETHESDA','ROCKVILLE','SILVER SPRING','ANNAPOLIS','COLUMBIA','FREDERICK','GAITHERSBURG','TOWSON','BOWIE'])[(random()*9)::int+1],
    'contributor_state', (ARRAY['AL','AK','AZ','AR','CA','CO','CT','DE','FL','GA','HI','ID','IL','IN','IA','KS','KY','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ','NM','NY','NC','ND','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY'])[(random()*49)::int+1],
    'contributor_zip_code', lpad(((random()*99999)::int)::text, 5, '0'),
    'election_code', 'P2026',
    'contribution_date', to_char('2023-01-01'::timestamp + (random() * 1300 * interval '1 day'), 'YYYY-MM-DD"T"HH24:MI:SS'),
    'contribution_amount', round((random()*2900)::numeric, 2),
    'contribution_aggregate', round((random()*5800)::numeric, 2),
    'contribution_purpose_descrip', 'CONTRIBUTION',
    'contributor_employer', CASE WHEN random() < 0.0015
         THEN (ARRAY['MICROSOFT','MICROSOFT CORPORATION','MICROSOFT CORP'])[(random()*2)::int+1]
         ELSE (ARRAY['ACME','GLOBEX','INITECH','UMBRELLA','STARK','WAYNE','TYRELL','SOYLENT','MASSIVE','VANDELAY','HOOLI','CYBERDYNE','OSCORP','ABSTERGO','APERTURE','WEYLAND','NAKATOMI','SABRE','LUMON','ENCOM','RETIRED','NOT EMPLOYED','SELF EMPLOYED','NORTHROP','LOCKHEED','JOHNS HOPKINS','MARRIOTT','UNDER ARMOUR','T ROWE PRICE','MCCORMICK'])[(random()*29)::int+1]
              || ' ' || (ARRAY['INC','LLC','CORPORATION','GROUP','HOLDINGS','SYSTEMS','TECHNOLOGIES','PARTNERS','SERVICES','INTERNATIONAL'])[(random()*9)::int+1]
       END,
    'contributor_occupation', (ARRAY['ENGINEER','ATTORNEY','PHYSICIAN','TEACHER','CONSULTANT','EXECUTIVE','RETIRED','ANALYST','MANAGER','SALES'])[(random()*9)::int+1],
    'memo_code', '',
    'memo_text_description', 'EARMARKED CONTRIBUTION REPORTED ON LINE 11AI',
    'image_number', ((random()*999999999999999)::bigint)::text,
    'file_number', ((random()*9999999)::int)::text,
    'sub_id', ((random()*9999999999)::bigint)::text
  )
FROM generate_series(1, ${SCALE}) i;

CREATE INDEX idx_record_type ON silver_fec_efiling_itemizations (record_type);

CREATE INDEX idx_fec_employer_trgm ON silver_fec_efiling_itemizations
  USING gin ((record_data->>'contributor_employer') gin_trgm_ops)
  WHERE record_type = 'Schedule A' AND record_data->>'entity_type' = 'IND';

VACUUM ANALYZE;
ScaleTimePlanCompute
1M6.5 ms
Bitmap Heap Scan fed by the partial trigram GIN alone. The index hands back every MICROSOFT row it covers, 1,082 against an estimate of 1, and the heap filter discards 1,077 of them across 1,080 blocks. Execution Time 6.474 ms.
QUERY PLAN
Bitmap Heap Scan on silver_fec_efiling_itemizations  (cost=373.33..377.40 rows=1 width=164) (actual time=1.332..6.432 rows=5 loops=1)
  Recheck Cond: (((record_data ->> 'contributor_employer'::text) ~~* '%MICROSOFT%'::text) AND (record_type = 'Schedule A'::text) AND ((record_data ->> 'entity_type'::text) = 'IND'::text))
  Filter: (((record_data ->> 'contribution_date'::text) >= '2025-01-01'::text) AND ((record_data ->> 'contribution_date'::text) < '2026-01-01'::text) AND ((record_data ->> 'contributor_state'::text) = 'MD'::text))
  Rows Removed by Filter: 1077
  Heap Blocks: exact=1080
  Buffers: shared hit=495 read=609 written=476
  ->  Bitmap Index Scan on idx_fec_employer_trgm  (cost=0.00..373.33 rows=1 width=0) (actual time=0.347..0.347 rows=1082 loops=1)
        Index Cond: ((record_data ->> 'contributor_employer'::text) ~~* '%MICROSOFT%'::text)
        Buffers: shared hit=24
Planning:
  Buffers: shared hit=54 read=7
Planning Time: 0.276 ms
Execution Time: 6.474 ms
23.97s
Raw
Raw benchmark JSON

A Word on Apparatus

Everything in this post ran through ExoBench, a benchmarking loop I built for exactly this kind of argument. I described the schema and the data shape in a prompt, and it spun up a throwaway Postgres, generated the rows at each scale I named, ran EXPLAIN (ANALYZE, BUFFERS), and handed the plans back. No production database was involved anywhere... the FEC records are synthetic, built to match the distributions in the question. The tool cards throughout this post are those payloads, verbatim, timestamps and worker ids included.

Use ExoBench to get performance stats and make the query fast. Find a good index, a better query or both.

The mechanics are documented in how it works, and the comparison page covers where this sits relative to asking a chatbot to read your SQL. The property that matters for this post is the rerun: every benchmark builds a fresh database from the same generator, an independent draw of the same distribution. That's what turned a routine double-check into the two plans at the top.

One more piece of setup. To rule out JSONB expression estimation as the culprit, I built a control: the same table with plain typed columns, same generator, same three indexes. The headline flip at the top of this post comes from that control, which is the stronger result. Plain columns, textbook indexes, and the planner still couldn't make up its mind even with no JSONB anywhere in sight.

The Planner Tosses a Coin

The first crack showed up when I grew the control table. At 1M rows the pair produced the BitmapAnd and finished in 1.91 ms over 27 heap blocks. At 2.9M, same schema, same indexes, the BitmapAnd was gone: the planner estimated 169 rows from the trigram scan, 4,420 arrived, and having priced the intersection as pointless it fell back to the trigram bitmap alone... 17.79 ms, 4,176 heap blocks, a 9x regression from 3x data growth.

Benchmark SQL
benchmarkSql complete
MCP Tool✓
Benchmark
postgres28.45s compute
EXPLAIN (ANALYZE, BUFFERS) SELECT
  contributor_first_name AS first_name,
  contributor_last_name  AS last_name,
  entity_state           AS state,
  contributor_employer   AS employer,
  contribution_amount    AS amount,
  transaction_date       AS contribution_date
FROM fec_filing_lineitems
WHERE schedule_code = 'Schedule A'
  AND entity_type   = 'IND'
  AND entity_state  = 'MD'
  AND contributor_employer ILIKE '%MICROSOFT%'
  AND transaction_date >= DATE '2025-01-01'
  AND transaction_date <  DATE '2026-01-01'
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE fec_filing_lineitems (
  id                     bigint PRIMARY KEY,
  schedule_code          text,
  entity_type            text,
  entity_state           text,
  contributor_first_name text,
  contributor_last_name  text,
  contributor_employer   text,
  contribution_amount    numeric,
  transaction_date       date
);

INSERT INTO fec_filing_lineitems
SELECT
  i,
  CASE WHEN random() < 0.80 THEN 'Schedule A'
       WHEN random() < 0.50 THEN 'Schedule B'
       ELSE 'Schedule E' END,
  CASE WHEN random() < 0.90 THEN 'IND' WHEN random() < 0.5 THEN 'ORG' ELSE 'PAC' END,
  (ARRAY['AL','AK','AZ','AR','CA','CO','CT','DE','FL','GA','HI','ID','IL','IN','IA','KS','KY','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ','NM','NY','NC','ND','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY'])[(random()*49)::int+1],
  (ARRAY['JAMES','MARY','ROBERT','PATRICIA','JOHN','JENNIFER','MICHAEL','LINDA','DAVID','ELIZABETH','WILLIAM','BARBARA','RICHARD','SUSAN','JOSEPH','JESSICA','THOMAS','SARAH','CHARLES','KAREN'])[(random()*19)::int+1],
  (ARRAY['SMITH','JOHNSON','WILLIAMS','BROWN','JONES','GARCIA','MILLER','DAVIS','RODRIGUEZ','MARTINEZ','HERNANDEZ','LOPEZ','GONZALEZ','WILSON','ANDERSON','THOMAS','TAYLOR','MOORE','JACKSON','MARTIN'])[(random()*19)::int+1],
  CASE WHEN random() < 0.0015
     THEN (ARRAY['MICROSOFT','MICROSOFT CORPORATION','MICROSOFT CORP'])[(random()*2)::int+1]
     ELSE (ARRAY['ACME','GLOBEX','INITECH','UMBRELLA','STARK','WAYNE','TYRELL','SOYLENT','MASSIVE','VANDELAY','HOOLI','CYBERDYNE','OSCORP','ABSTERGO','APERTURE','WEYLAND','NAKATOMI','SABRE','LUMON','ENCOM','RETIRED','NOT EMPLOYED','SELF EMPLOYED','NORTHROP','LOCKHEED','JOHNS HOPKINS','MARRIOTT','UNDER ARMOUR','T ROWE PRICE','MCCORMICK'])[(random()*29)::int+1]
          || ' ' || (ARRAY['INC','LLC','CORPORATION','GROUP','HOLDINGS','SYSTEMS','TECHNOLOGIES','PARTNERS','SERVICES','INTERNATIONAL'])[(random()*9)::int+1]
   END,
  round((random()*2900)::numeric, 2),
  ('2023-01-01'::date + (random() * 1300)::int)
FROM generate_series(1, ${SCALE}) i;

CREATE INDEX idx_li_emp_trgm ON fec_filing_lineitems USING gin (contributor_employer gin_trgm_ops);
CREATE INDEX idx_li_state ON fec_filing_lineitems (entity_state);
CREATE INDEX idx_li_date  ON fec_filing_lineitems (transaction_date);

VACUUM ANALYZE;
ScaleTimePlanCompute
1M1.9 ms
The same three indexes on the plain-column control. BitmapAnd of idx_li_state (19,367 est, 20,320 actual) and idx_li_emp_trgm (5,916 est, 1,499 actual), 27 heap blocks. Execution Time 1.909 ms.
QUERY PLAN
Bitmap Heap Scan on fec_filing_lineitems  (cost=575.60..1005.27 rows=23 width=44) (actual time=1.829..1.876 rows=6 loops=1)
  Recheck Cond: ((entity_state = 'MD'::text) AND (contributor_employer ~~* '%MICROSOFT%'::text))
  Filter: ((transaction_date >= '2025-01-01'::date) AND (transaction_date < '2026-01-01'::date) AND (schedule_code = 'Schedule A'::text) AND (entity_type = 'IND'::text))
  Rows Removed by Filter: 21
  Heap Blocks: exact=27
  Buffers: shared hit=49 read=20
  ->  BitmapAnd  (cost=575.60..575.60 rows=115 width=0) (actual time=1.790..1.791 rows=0 loops=1)
        Buffers: shared hit=22 read=20
        ->  Bitmap Index Scan on idx_li_state  (cost=0.00..213.68 rows=19367 width=0) (actual time=0.968..0.968 rows=20320 loops=1)
              Index Cond: (entity_state = 'MD'::text)
              Buffers: shared read=20
        ->  Bitmap Index Scan on idx_li_emp_trgm  (cost=0.00..361.66 rows=5916 width=0) (actual time=0.467..0.467 rows=1499 loops=1)
              Index Cond: (contributor_employer ~~* '%MICROSOFT%'::text)
              Buffers: shared hit=22
Planning:
  Buffers: shared hit=55
Planning Time: 0.255 ms
Execution Time: 1.909 ms
7.46s
2.9M17.8 ms
Grown to 2.9M, the BitmapAnd is gone. The trigram estimate came in at 169 against 4,420 actual, the intersection priced as pointless, 4,176 heap blocks. Execution Time 17.786 ms.
QUERY PLAN
Bitmap Heap Scan on fec_filing_lineitems  (cost=856.81..1502.28 rows=1 width=44) (actual time=2.423..17.428 rows=22 loops=1)
  Recheck Cond: (contributor_employer ~~* '%MICROSOFT%'::text)
  Filter: ((transaction_date >= '2025-01-01'::date) AND (transaction_date < '2026-01-01'::date) AND (schedule_code = 'Schedule A'::text) AND (entity_type = 'IND'::text) AND (entity_state = 'MD'::text))
  Rows Removed by Filter: 4398
  Heap Blocks: exact=4176
  Buffers: shared hit=4229
  ->  Bitmap Index Scan on idx_li_emp_trgm  (cost=0.00..856.80 rows=169 width=0) (actual time=1.481..1.481 rows=4420 loops=1)
        Index Cond: (contributor_employer ~~* '%MICROSOFT%'::text)
        Buffers: shared hit=53
Planning:
  Buffers: shared hit=75
Planning Time: 0.325 ms
Execution Time: 17.786 ms
20.98s
Raw
Raw benchmark JSON

Then, still in the first sitting, I did a second control run at a fixed 2.4M: the pair of plans that opens this post. Here is that run verbatim, receipts attached: one call, two scale points, two opposite plans.

Benchmark SQL
benchmarkSql complete
MCP Tool✓
Benchmark
postgres34.73s compute
EXPLAIN (ANALYZE, BUFFERS) SELECT
  contributor_first_name AS first_name,
  contributor_last_name  AS last_name,
  entity_state           AS state,
  contributor_employer   AS employer,
  contribution_amount    AS amount,
  transaction_date       AS contribution_date
FROM fec_filing_lineitems
WHERE schedule_code = 'Schedule A'
  AND entity_type   = 'IND'
  AND entity_state  = 'MD'
  AND contributor_employer ILIKE '%MICROSOFT%'
  AND transaction_date >= DATE '2025-01-01'
  AND transaction_date <  DATE '2026-01-01'
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE fec_filing_lineitems (
  id                     bigint PRIMARY KEY,
  schedule_code          text,
  entity_type            text,
  entity_state           text,
  contributor_first_name text,
  contributor_last_name  text,
  contributor_employer   text,
  contribution_amount    numeric,
  transaction_date       date
);

INSERT INTO fec_filing_lineitems
SELECT
  i,
  CASE WHEN random() < 0.80 THEN 'Schedule A'
       WHEN random() < 0.50 THEN 'Schedule B'
       ELSE 'Schedule E' END,
  CASE WHEN random() < 0.90 THEN 'IND' WHEN random() < 0.5 THEN 'ORG' ELSE 'PAC' END,
  (ARRAY['AL','AK','AZ','AR','CA','CO','CT','DE','FL','GA','HI','ID','IL','IN','IA','KS','KY','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ','NM','NY','NC','ND','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY'])[(random()*49)::int+1],
  (ARRAY['JAMES','MARY','ROBERT','PATRICIA','JOHN','JENNIFER','MICHAEL','LINDA','DAVID','ELIZABETH','WILLIAM','BARBARA','RICHARD','SUSAN','JOSEPH','JESSICA','THOMAS','SARAH','CHARLES','KAREN'])[(random()*19)::int+1],
  (ARRAY['SMITH','JOHNSON','WILLIAMS','BROWN','JONES','GARCIA','MILLER','DAVIS','RODRIGUEZ','MARTINEZ','HERNANDEZ','LOPEZ','GONZALEZ','WILSON','ANDERSON','THOMAS','TAYLOR','MOORE','JACKSON','MARTIN'])[(random()*19)::int+1],
  CASE WHEN random() < 0.0015
     THEN (ARRAY['MICROSOFT','MICROSOFT CORPORATION','MICROSOFT CORP'])[(random()*2)::int+1]
     ELSE (ARRAY['ACME','GLOBEX','INITECH','UMBRELLA','STARK','WAYNE','TYRELL','SOYLENT','MASSIVE','VANDELAY','HOOLI','CYBERDYNE','OSCORP','ABSTERGO','APERTURE','WEYLAND','NAKATOMI','SABRE','LUMON','ENCOM','RETIRED','NOT EMPLOYED','SELF EMPLOYED','NORTHROP','LOCKHEED','JOHNS HOPKINS','MARRIOTT','UNDER ARMOUR','T ROWE PRICE','MCCORMICK'])[(random()*29)::int+1]
          || ' ' || (ARRAY['INC','LLC','CORPORATION','GROUP','HOLDINGS','SYSTEMS','TECHNOLOGIES','PARTNERS','SERVICES','INTERNATIONAL'])[(random()*9)::int+1]
   END,
  round((random()*2900)::numeric, 2),
  ('2023-01-01'::date + (random() * 1300)::int)
FROM generate_series(1, ${SCALE}) i;

CREATE INDEX idx_li_emp_trgm ON fec_filing_lineitems USING gin (contributor_employer gin_trgm_ops);
CREATE INDEX idx_li_state ON fec_filing_lineitems (entity_state);
CREATE INDEX idx_li_date  ON fec_filing_lineitems (transaction_date);

VACUUM ANALYZE;
ScaleTimePlanCompute
2.4M#16.9 ms
First 2.4M-row draw. Bitmap Heap Scan fed by idx_li_emp_trgm alone, Recheck Cond is just the ILIKE, 3,449 heap blocks, 3,675 rows removed by filter. The trigram estimate was 141 against 3,689 actual. Execution Time 6.856 ms, total cost 1,263.08.
QUERY PLAN
Bitmap Heap Scan on fec_filing_lineitems  (cost=724.66..1263.08 rows=1 width=44) (actual time=1.670..6.821 rows=14 loops=1)
  Recheck Cond: (contributor_employer ~~* '%MICROSOFT%'::text)
  Filter: ((transaction_date >= '2025-01-01'::date) AND (transaction_date < '2026-01-01'::date) AND (schedule_code = 'Schedule A'::text) AND (entity_type = 'IND'::text) AND (entity_state = 'MD'::text))
  Rows Removed by Filter: 3675
  Heap Blocks: exact=3449
  Buffers: shared hit=3498
  ->  Bitmap Index Scan on idx_li_emp_trgm  (cost=0.00..724.66 rows=141 width=0) (actual time=1.102..1.103 rows=3689 loops=1)
        Index Cond: (contributor_employer ~~* '%MICROSOFT%'::text)
        Buffers: shared hit=49
Planning:
  Buffers: shared hit=55
Planning Time: 0.256 ms
Execution Time: 6.856 ms
17.24s
2.4M#24.6 ms
Second 2.4M-row draw of the same generator. BitmapAnd of idx_li_state (46,720 est, 49,257 actual) and idx_li_emp_trgm (141 est, 3,507 actual), 73 heap blocks. Execution Time 4.604 ms, total cost 1,243.60, 19 units under the other plan.
QUERY PLAN
Bitmap Heap Scan on fec_filing_lineitems  (cost=1231.62..1243.60 rows=1 width=44) (actual time=4.436..4.568 rows=19 loops=1)
  Recheck Cond: ((entity_state = 'MD'::text) AND (contributor_employer ~~* '%MICROSOFT%'::text))
  Filter: ((transaction_date >= '2025-01-01'::date) AND (transaction_date < '2026-01-01'::date) AND (schedule_code = 'Schedule A'::text) AND (entity_type = 'IND'::text))
  Rows Removed by Filter: 54
  Heap Blocks: exact=73
  Buffers: shared hit=122 read=44
  ->  BitmapAnd  (cost=1231.62..1231.62 rows=3 width=0) (actual time=4.377..4.378 rows=0 loops=1)
        Buffers: shared hit=49 read=44
        ->  Bitmap Index Scan on idx_li_state  (cost=0.00..510.83 rows=46720 width=0) (actual time=2.388..2.389 rows=49257 loops=1)
              Index Cond: (entity_state = 'MD'::text)
              Buffers: shared read=44
        ->  Bitmap Index Scan on idx_li_emp_trgm  (cost=0.00..720.54 rows=141 width=0) (actual time=1.063..1.063 rows=3507 loops=1)
              Index Cond: (contributor_employer ~~* '%MICROSOFT%'::text)
              Buffers: shared hit=49
Planning:
  Buffers: shared hit=57
Planning Time: 0.266 ms
Execution Time: 4.604 ms
17.50s
Raw
Raw benchmark JSON

To see why identical inputs split, put the two prices side by side:

trigram alone:   724.66 index work  +  538.42 heap and CPU  =  1263.08
the BitmapAnd:  1231.62 index work  +   11.98 heap and CPU  =  1243.60

The intersection pays about 507 more in index work to save about 526 in heap work. Net margin: 19 cost units on 1,250, about 1.5%.

If you're a sane human being, you're probably wondering how "19 cost units" means the difference between 73 and 3449 heap fetches. Back when I was sane (i.e. before I started reading query plans) I had similar questions. The answer is that "cost units" is an invented concept that does not directly connect to anything in reality, they exist only because query-planners need some kind of pure-mathematical measuring-unit. In this case the planner used them to rank two futures it has not run, and the difference between a utopia and a zombie apocalypse came down to just 19 of them. Every input to those two numbers (i.e. 507 versus 526) wobbles from draw to draw, and 1.5% of a number with no clock attached is well inside the wobble.

The wobble itself is massive. Cost is built from a row guess, in this case the guess of how many rows the planner thought %MICROSOFT% would match against the trigram index. Let's compare that to how many rows actually came back, just for kicks.

estimated rows     141      actual rows  3,689
estimated rows     141      actual rows  3,507
estimated rows  14,215      actual rows  3,619
estimated rows     140      actual rows  3,525

The truth wobbles by 5%. The estimate wobbles by 100x.

So the planner read a wobbling guess as a measurement, every single time, with total confidence. Nobody told it. This is a different failure from a histogram that cannot see join-crossing correlation. That one is structural and stable. This one re-rolls every ANALYZE.

If you're thinking your production table doesn't re-draw its data, correct, but ANALYZE re-draws its sample of that data on every autovacuum cycle, and the estimates come from the sample. These dice get rolled on your table too, on a schedule you didn't pick.

NOTE: What's really crazy is that this whole inconsistency is itself inconsistent. Back on the original JSONB table (i.e. the silver_fec_efiling_itemizations), the pair held in every original-session run, and I went back to find out why. At 2.9M the trigram estimate there was 17,176 against an actual of 4,381, wrong by 4x in the direction that happens to keep the BitmapAnd alive. The stable plan I nearly recommended was stable by luck of which way the estimate missed.

Benchmark SQL
benchmarkSql complete
MCP Tool✓
Benchmark
postgres98.44s compute
EXPLAIN (ANALYZE, BUFFERS) SELECT
  record_data->>'contributor_first_name' AS first_name,
  record_data->>'contributor_last_name'  AS last_name,
  record_data->>'contributor_state'      AS state,
  record_data->>'contributor_employer'   AS employer,
  (record_data->>'contribution_amount')::numeric AS amount,
  LEFT(record_data->>'contribution_date',10)::date AS contribution_date
FROM silver_fec_efiling_itemizations
WHERE record_type = 'Schedule A'
  AND record_data->>'entity_type' = 'IND'
  AND record_data->>'contributor_state' = 'MD'
  AND record_data->>'contributor_employer' ILIKE '%MICROSOFT%'
  AND record_data->>'contribution_date' >= '2025-01-01'
  AND record_data->>'contribution_date' <  '2026-01-01'
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE silver_fec_efiling_itemizations (
  id          bigint PRIMARY KEY,
  record_type text,
  record_data jsonb
);

INSERT INTO silver_fec_efiling_itemizations (id, record_type, record_data)
SELECT
  i,
  CASE WHEN random() < 0.80 THEN 'Schedule A'
       WHEN random() < 0.50 THEN 'Schedule B'
       ELSE 'Schedule E' END,
  jsonb_build_object(
    'form_type', 'SA11AI',
    'filer_committee_id_number', 'C00' || lpad(((random()*99999)::int)::text, 6, '0'),
    'transaction_id', 'SA11AI.' || i,
    'entity_type', CASE WHEN random() < 0.90 THEN 'IND' WHEN random() < 0.5 THEN 'ORG' ELSE 'PAC' END,
    'contributor_organization_name', '',
    'contributor_last_name', (ARRAY['SMITH','JOHNSON','WILLIAMS','BROWN','JONES','GARCIA','MILLER','DAVIS','RODRIGUEZ','MARTINEZ','HERNANDEZ','LOPEZ','GONZALEZ','WILSON','ANDERSON','THOMAS','TAYLOR','MOORE','JACKSON','MARTIN'])[(random()*19)::int+1],
    'contributor_first_name', (ARRAY['JAMES','MARY','ROBERT','PATRICIA','JOHN','JENNIFER','MICHAEL','LINDA','DAVID','ELIZABETH','WILLIAM','BARBARA','RICHARD','SUSAN','JOSEPH','JESSICA','THOMAS','SARAH','CHARLES','KAREN'])[(random()*19)::int+1],
    'contributor_middle_name', '',
    'contributor_street_1', ((random()*9999)::int)::text || ' MAIN STREET',
    'contributor_city', (ARRAY['BALTIMORE','BETHESDA','ROCKVILLE','SILVER SPRING','ANNAPOLIS','COLUMBIA','FREDERICK','GAITHERSBURG','TOWSON','BOWIE'])[(random()*9)::int+1],
    'contributor_state', (ARRAY['AL','AK','AZ','AR','CA','CO','CT','DE','FL','GA','HI','ID','IL','IN','IA','KS','KY','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ','NM','NY','NC','ND','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY'])[(random()*49)::int+1],
    'contributor_zip_code', lpad(((random()*99999)::int)::text, 5, '0'),
    'election_code', 'P2026',
    'contribution_date', to_char('2023-01-01'::timestamp + (random() * 1300 * interval '1 day'), 'YYYY-MM-DD"T"HH24:MI:SS'),
    'contribution_amount', round((random()*2900)::numeric, 2),
    'contribution_aggregate', round((random()*5800)::numeric, 2),
    'contribution_purpose_descrip', 'CONTRIBUTION',
    'contributor_employer', CASE WHEN random() < 0.0015
         THEN (ARRAY['MICROSOFT','MICROSOFT CORPORATION','MICROSOFT CORP'])[(random()*2)::int+1]
         ELSE (ARRAY['ACME','GLOBEX','INITECH','UMBRELLA','STARK','WAYNE','TYRELL','SOYLENT','MASSIVE','VANDELAY','HOOLI','CYBERDYNE','OSCORP','ABSTERGO','APERTURE','WEYLAND','NAKATOMI','SABRE','LUMON','ENCOM','RETIRED','NOT EMPLOYED','SELF EMPLOYED','NORTHROP','LOCKHEED','JOHNS HOPKINS','MARRIOTT','UNDER ARMOUR','T ROWE PRICE','MCCORMICK'])[(random()*29)::int+1]
              || ' ' || (ARRAY['INC','LLC','CORPORATION','GROUP','HOLDINGS','SYSTEMS','TECHNOLOGIES','PARTNERS','SERVICES','INTERNATIONAL'])[(random()*9)::int+1]
       END,
    'contributor_occupation', (ARRAY['ENGINEER','ATTORNEY','PHYSICIAN','TEACHER','CONSULTANT','EXECUTIVE','RETIRED','ANALYST','MANAGER','SALES'])[(random()*9)::int+1],
    'memo_code', '',
    'memo_text_description', 'EARMARKED CONTRIBUTION REPORTED ON LINE 11AI',
    'image_number', ((random()*999999999999999)::bigint)::text,
    'file_number', ((random()*9999999)::int)::text,
    'sub_id', ((random()*9999999999)::bigint)::text
  )
FROM generate_series(1, ${SCALE}) i;

CREATE INDEX idx_record_type ON silver_fec_efiling_itemizations (record_type);

-- THE OTHER AGENT'S EXACT PROPOSAL
CREATE INDEX idx_emp_trgm ON silver_fec_efiling_itemizations
  USING gin ((record_data->>'contributor_employer') gin_trgm_ops);

CREATE INDEX idx_state ON silver_fec_efiling_itemizations
  ((record_data->>'contributor_state'));

CREATE INDEX idx_date ON silver_fec_efiling_itemizations
  ((record_data->>'contribution_date'));

VACUUM ANALYZE;
ScaleTimePlanCompute
1M2.8 ms
The other agent's exact proposal on the JSONB table. BitmapAnd of idx_state (19,066 est, 20,505 actual) and idx_emp_trgm (59 est, 1,502 actual), 34 heap blocks. Execution Time 2.793 ms.
QUERY PLAN
Bitmap Heap Scan on silver_fec_efiling_itemizations  (cost=680.17..684.24 rows=1 width=164) (actual time=2.659..2.754 rows=6 loops=1)
  Recheck Cond: (((record_data ->> 'contributor_state'::text) = 'MD'::text) AND ((record_data ->> 'contributor_employer'::text) ~~* '%MICROSOFT%'::text))
  Filter: ((record_type = 'Schedule A'::text) AND ((record_data ->> 'contribution_date'::text) >= '2025-01-01'::text) AND ((record_data ->> 'contribution_date'::text) < '2026-01-01'::text) AND ((record_data ->> 'entity_type'::text) = 'IND'::text))
  Rows Removed by Filter: 28
  Heap Blocks: exact=34
  Buffers: shared hit=42 read=38
  ->  BitmapAnd  (cost=680.17..680.17 rows=1 width=0) (actual time=2.512..2.513 rows=0 loops=1)
        Buffers: shared hit=26 read=20
        ->  Bitmap Index Scan on idx_state  (cost=0.00..211.42 rows=19066 width=0) (actual time=1.350..1.351 rows=20505 loops=1)
              Index Cond: ((record_data ->> 'contributor_state'::text) = 'MD'::text)
              Buffers: shared read=20
        ->  Bitmap Index Scan on idx_emp_trgm  (cost=0.00..468.50 rows=59 width=0) (actual time=0.513..0.513 rows=1502 loops=1)
              Index Cond: ((record_data ->> 'contributor_employer'::text) ~~* '%MICROSOFT%'::text)
              Buffers: shared hit=26
Planning:
  Buffers: shared hit=82 read=5
Planning Time: 0.367 ms
Execution Time: 2.793 ms
26.78s
2.9M9.3 ms
Same call at 2.9M: the BitmapAnd held. idx_state 59,545 est against 59,439 actual, idx_emp_trgm 17,176 est against 4,381 actual, 4x high, the lucky direction. 104 heap blocks. Execution Time 9.275 ms.
QUERY PLAN
Bitmap Heap Scan on silver_fec_efiling_itemizations  (cost=1968.48..3360.68 rows=1 width=164) (actual time=8.606..9.235 rows=24 loops=1)
  Recheck Cond: (((record_data ->> 'contributor_state'::text) = 'MD'::text) AND ((record_data ->> 'contributor_employer'::text) ~~* '%MICROSOFT%'::text))
  Filter: ((record_type = 'Schedule A'::text) AND ((record_data ->> 'contribution_date'::text) >= '2025-01-01'::text) AND ((record_data ->> 'contribution_date'::text) < '2026-01-01'::text) AND ((record_data ->> 'entity_type'::text) = 'IND'::text))
  Rows Removed by Filter: 80
  Heap Blocks: exact=104
  Buffers: shared hit=66 read=154 written=23
  ->  BitmapAnd  (cost=1968.48..1968.48 rows=353 width=0) (actual time=8.201..8.202 rows=0 loops=1)
        Buffers: shared hit=56 read=60
        ->  Bitmap Index Scan on idx_state  (cost=0.00..651.02 rows=59545 width=0) (actual time=5.161..5.161 rows=59439 loops=1)
              Index Cond: ((record_data ->> 'contributor_state'::text) = 'MD'::text)
              Buffers: shared read=53
        ->  Bitmap Index Scan on idx_emp_trgm  (cost=0.00..1317.21 rows=17176 width=0) (actual time=1.374..1.374 rows=4381 loops=1)
              Index Cond: ((record_data ->> 'contributor_employer'::text) ~~* '%MICROSOFT%'::text)
              Buffers: shared hit=56 read=7
Planning:
  Buffers: shared hit=82 read=5
Planning Time: 0.371 ms
Execution Time: 9.275 ms
71.66s
Raw
Raw benchmark JSON

The Index with Nothing to Decide

The fix I ended up suggesting removes the decision instead of biasing it:

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') gin_trgm_ops
  );

The naming trips people up, so briefly: GIN is the access method, and btree_gin and pg_trgm are operator classes that tell it how to cut a value into keys. The state column goes in as whole-value keys (stock GIN can't index scalar text at all; btree_gin exists to fill exactly this gap, and no B-tree is involved despite the name). The employer column goes in as trigrams. At query time GIN fetches the posting lists for 'MD' and for each trigram of MICROSOFT, and intersects them inside the index, before the heap exists as a concern.

A pair of indexes gives the planner a decision. A composite index gives it mechanics.

Measured on the JSONB table, no query rewrite:

Benchmark SQL
benchmarkSql complete
MCP Tool✓
Benchmark
postgres96.40s compute
EXPLAIN (ANALYZE, BUFFERS) SELECT
  record_data->>'contributor_first_name' AS first_name,
  record_data->>'contributor_last_name'  AS last_name,
  record_data->>'contributor_state'      AS state,
  record_data->>'contributor_employer'   AS employer,
  (record_data->>'contribution_amount')::numeric AS amount,
  LEFT(record_data->>'contribution_date',10)::date AS contribution_date
FROM silver_fec_efiling_itemizations
WHERE record_type = 'Schedule A'
  AND record_data->>'entity_type' = 'IND'
  AND record_data->>'contributor_state' = 'MD'
  AND record_data->>'contributor_employer' ILIKE '%MICROSOFT%'
  AND record_data->>'contribution_date' >= '2025-01-01'
  AND record_data->>'contribution_date' <  '2026-01-01'
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS btree_gin;

CREATE TABLE silver_fec_efiling_itemizations (
  id          bigint PRIMARY KEY,
  record_type text,
  record_data jsonb
);

INSERT INTO silver_fec_efiling_itemizations (id, record_type, record_data)
SELECT
  i,
  CASE WHEN random() < 0.80 THEN 'Schedule A'
       WHEN random() < 0.50 THEN 'Schedule B'
       ELSE 'Schedule E' END,
  jsonb_build_object(
    'form_type', 'SA11AI',
    'filer_committee_id_number', 'C00' || lpad(((random()*99999)::int)::text, 6, '0'),
    'transaction_id', 'SA11AI.' || i,
    'entity_type', CASE WHEN random() < 0.90 THEN 'IND' WHEN random() < 0.5 THEN 'ORG' ELSE 'PAC' END,
    'contributor_organization_name', '',
    'contributor_last_name', (ARRAY['SMITH','JOHNSON','WILLIAMS','BROWN','JONES','GARCIA','MILLER','DAVIS','RODRIGUEZ','MARTINEZ','HERNANDEZ','LOPEZ','GONZALEZ','WILSON','ANDERSON','THOMAS','TAYLOR','MOORE','JACKSON','MARTIN'])[(random()*19)::int+1],
    'contributor_first_name', (ARRAY['JAMES','MARY','ROBERT','PATRICIA','JOHN','JENNIFER','MICHAEL','LINDA','DAVID','ELIZABETH','WILLIAM','BARBARA','RICHARD','SUSAN','JOSEPH','JESSICA','THOMAS','SARAH','CHARLES','KAREN'])[(random()*19)::int+1],
    'contributor_middle_name', '',
    'contributor_street_1', ((random()*9999)::int)::text || ' MAIN STREET',
    'contributor_city', (ARRAY['BALTIMORE','BETHESDA','ROCKVILLE','SILVER SPRING','ANNAPOLIS','COLUMBIA','FREDERICK','GAITHERSBURG','TOWSON','BOWIE'])[(random()*9)::int+1],
    'contributor_state', (ARRAY['AL','AK','AZ','AR','CA','CO','CT','DE','FL','GA','HI','ID','IL','IN','IA','KS','KY','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ','NM','NY','NC','ND','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY'])[(random()*49)::int+1],
    'contributor_zip_code', lpad(((random()*99999)::int)::text, 5, '0'),
    'election_code', 'P2026',
    'contribution_date', to_char('2023-01-01'::timestamp + (random() * 1300 * interval '1 day'), 'YYYY-MM-DD"T"HH24:MI:SS'),
    'contribution_amount', round((random()*2900)::numeric, 2),
    'contribution_aggregate', round((random()*5800)::numeric, 2),
    'contribution_purpose_descrip', 'CONTRIBUTION',
    'contributor_employer', CASE WHEN random() < 0.0015
         THEN (ARRAY['MICROSOFT','MICROSOFT CORPORATION','MICROSOFT CORP'])[(random()*2)::int+1]
         ELSE (ARRAY['ACME','GLOBEX','INITECH','UMBRELLA','STARK','WAYNE','TYRELL','SOYLENT','MASSIVE','VANDELAY','HOOLI','CYBERDYNE','OSCORP','ABSTERGO','APERTURE','WEYLAND','NAKATOMI','SABRE','LUMON','ENCOM','RETIRED','NOT EMPLOYED','SELF EMPLOYED','NORTHROP','LOCKHEED','JOHNS HOPKINS','MARRIOTT','UNDER ARMOUR','T ROWE PRICE','MCCORMICK'])[(random()*29)::int+1]
              || ' ' || (ARRAY['INC','LLC','CORPORATION','GROUP','HOLDINGS','SYSTEMS','TECHNOLOGIES','PARTNERS','SERVICES','INTERNATIONAL'])[(random()*9)::int+1]
       END,
    'contributor_occupation', (ARRAY['ENGINEER','ATTORNEY','PHYSICIAN','TEACHER','CONSULTANT','EXECUTIVE','RETIRED','ANALYST','MANAGER','SALES'])[(random()*9)::int+1],
    'memo_code', '',
    'memo_text_description', 'EARMARKED CONTRIBUTION REPORTED ON LINE 11AI',
    'image_number', ((random()*999999999999999)::bigint)::text,
    'file_number', ((random()*9999999)::int)::text,
    'sub_id', ((random()*9999999999)::bigint)::text
  )
FROM generate_series(1, ${SCALE}) i;

CREATE INDEX idx_record_type ON silver_fec_efiling_itemizations (record_type);

-- SINGLE multicolumn GIN: btree_gin for state equality + pg_trgm for employer ILIKE
CREATE INDEX idx_fec_multi_gin ON silver_fec_efiling_itemizations
  USING gin (
    (record_data->>'contributor_state'),
    (record_data->>'contributor_employer') gin_trgm_ops
  );

VACUUM ANALYZE;
ScaleTimePlanCompute
1M0.8 ms
Composite btree_gin + pg_trgm on the JSONB table at 1M rows. One Bitmap Index Scan on idx_fec_multi_gin, both predicates in the Index Cond, 30 heap blocks. Index scan 0.559 ms, Execution Time 0.760 ms.
QUERY PLAN
Bitmap Heap Scan on silver_fec_efiling_itemizations  (cost=497.41..501.47 rows=1 width=164) (actual time=0.653..0.721 rows=2 loops=1)
  Recheck Cond: (((record_data ->> 'contributor_state'::text) = 'MD'::text) AND ((record_data ->> 'contributor_employer'::text) ~~* '%MICROSOFT%'::text))
  Filter: ((record_type = 'Schedule A'::text) AND ((record_data ->> 'contribution_date'::text) >= '2025-01-01'::text) AND ((record_data ->> 'contribution_date'::text) < '2026-01-01'::text) AND ((record_data ->> 'entity_type'::text) = 'IND'::text))
  Rows Removed by Filter: 28
  Heap Blocks: exact=30
  Buffers: shared hit=55 read=15
  ->  Bitmap Index Scan on idx_fec_multi_gin  (cost=0.00..497.41 rows=1 width=0) (actual time=0.559..0.560 rows=30 loops=1)
        Index Cond: (((record_data ->> 'contributor_state'::text) = 'MD'::text) AND ((record_data ->> 'contributor_employer'::text) ~~* '%MICROSOFT%'::text))
        Buffers: shared hit=40
Planning:
  Buffers: shared hit=67 read=5 dirtied=1
Planning Time: 0.924 ms
Execution Time: 0.760 ms
27.05s
2.9M11.5 ms
Same composite GIN at 2.9M rows, same plan shape: one Bitmap Index Scan, both predicates in the Index Cond, 90 heap blocks. Index scan 1.642 ms. Execution Time 11.503 ms is mostly cold reads, 77 of 189 buffers off disk.
QUERY PLAN
Bitmap Heap Scan on silver_fec_efiling_itemizations  (cost=1293.54..1305.66 rows=1 width=164) (actual time=1.846..11.453 rows=22 loops=1)
  Recheck Cond: (((record_data ->> 'contributor_state'::text) = 'MD'::text) AND ((record_data ->> 'contributor_employer'::text) ~~* '%MICROSOFT%'::text))
  Filter: ((record_type = 'Schedule A'::text) AND ((record_data ->> 'contribution_date'::text) >= '2025-01-01'::text) AND ((record_data ->> 'contribution_date'::text) < '2026-01-01'::text) AND ((record_data ->> 'entity_type'::text) = 'IND'::text))
  Rows Removed by Filter: 68
  Heap Blocks: exact=90
  Buffers: shared hit=112 read=77
  ->  Bitmap Index Scan on idx_fec_multi_gin  (cost=0.00..1293.54 rows=3 width=0) (actual time=1.642..1.643 rows=90 loops=1)
        Index Cond: (((record_data ->> 'contributor_state'::text) = 'MD'::text) AND ((record_data ->> 'contributor_employer'::text) ~~* '%MICROSOFT%'::text))
        Buffers: shared hit=99
Planning:
  Buffers: shared hit=69 read=2
Planning Time: 0.344 ms
Execution Time: 11.503 ms
69.35s
Raw
Raw benchmark JSON

0.76 ms and 30 heap blocks at 1M, then 11.5 ms and 90 blocks at 2.9M, with the index scan itself at 0.56 and 1.64 ms. The 2.9M wall clock is mostly cold reads on a fresh database (77 of 189 buffers came off disk); the index-work and heap-block numbers are the ones that predict scaling. Against the 194 ms baseline at 1M, that's a 255x cut from one CREATE INDEX. And across every run I did with this index, at every scale, the plan came out the same shape: one Bitmap Index Scan, both predicates in the Index Cond, no BitmapAnd node anywhere, because there's no second index to intersect with.

Two keys stayed out of the index deliberately. entity_type is 90% of rows and record_type is 80%, and every key in a GIN costs a posting-list read proportional to that key's frequency in the table, not to your result size. Low-selectivity predicates belong in the heap filter, which is where the plans put them, cheaply, over ~30 candidate rows.

A Most Unpleasant Knob

For anyone married to the two-index pair, there is one knob that addressed the flip in my runs: random_page_cost. Raising it to 10 widened the heap-versus-index margin enough that the BitmapAnd held on both draws, including the draw where the estimate came back 25x under.

Benchmark SQL
benchmarkSql complete
MCP Tool✓
Benchmark
postgres34.90s compute
EXPLAIN (ANALYZE, BUFFERS) SELECT
  contributor_first_name AS first_name,
  contributor_last_name  AS last_name,
  entity_state           AS state,
  contributor_employer   AS employer,
  contribution_amount    AS amount,
  transaction_date       AS contribution_date
FROM fec_filing_lineitems
WHERE schedule_code = 'Schedule A'
  AND entity_type   = 'IND'
  AND entity_state  = 'MD'
  AND contributor_employer ILIKE '%MICROSOFT%'
  AND transaction_date >= DATE '2025-01-01'
  AND transaction_date <  DATE '2026-01-01'
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE fec_filing_lineitems (
  id                     bigint PRIMARY KEY,
  schedule_code          text,
  entity_type            text,
  entity_state           text,
  contributor_first_name text,
  contributor_last_name  text,
  contributor_employer   text,
  contribution_amount    numeric,
  transaction_date       date
);

INSERT INTO fec_filing_lineitems
SELECT
  i,
  CASE WHEN random() < 0.80 THEN 'Schedule A'
       WHEN random() < 0.50 THEN 'Schedule B'
       ELSE 'Schedule E' END,
  CASE WHEN random() < 0.90 THEN 'IND' WHEN random() < 0.5 THEN 'ORG' ELSE 'PAC' END,
  (ARRAY['AL','AK','AZ','AR','CA','CO','CT','DE','FL','GA','HI','ID','IL','IN','IA','KS','KY','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ','NM','NY','NC','ND','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY'])[(random()*49)::int+1],
  (ARRAY['JAMES','MARY','ROBERT','PATRICIA','JOHN','JENNIFER','MICHAEL','LINDA','DAVID','ELIZABETH','WILLIAM','BARBARA','RICHARD','SUSAN','JOSEPH','JESSICA','THOMAS','SARAH','CHARLES','KAREN'])[(random()*19)::int+1],
  (ARRAY['SMITH','JOHNSON','WILLIAMS','BROWN','JONES','GARCIA','MILLER','DAVIS','RODRIGUEZ','MARTINEZ','HERNANDEZ','LOPEZ','GONZALEZ','WILSON','ANDERSON','THOMAS','TAYLOR','MOORE','JACKSON','MARTIN'])[(random()*19)::int+1],
  CASE WHEN random() < 0.0015
     THEN (ARRAY['MICROSOFT','MICROSOFT CORPORATION','MICROSOFT CORP'])[(random()*2)::int+1]
     ELSE (ARRAY['ACME','GLOBEX','INITECH','UMBRELLA','STARK','WAYNE','TYRELL','SOYLENT','MASSIVE','VANDELAY','HOOLI','CYBERDYNE','OSCORP','ABSTERGO','APERTURE','WEYLAND','NAKATOMI','SABRE','LUMON','ENCOM','RETIRED','NOT EMPLOYED','SELF EMPLOYED','NORTHROP','LOCKHEED','JOHNS HOPKINS','MARRIOTT','UNDER ARMOUR','T ROWE PRICE','MCCORMICK'])[(random()*29)::int+1]
          || ' ' || (ARRAY['INC','LLC','CORPORATION','GROUP','HOLDINGS','SYSTEMS','TECHNOLOGIES','PARTNERS','SERVICES','INTERNATIONAL'])[(random()*9)::int+1]
   END,
  round((random()*2900)::numeric, 2),
  ('2023-01-01'::date + (random() * 1300)::int)
FROM generate_series(1, ${SCALE}) i;

CREATE INDEX idx_li_emp_trgm ON fec_filing_lineitems USING gin (contributor_employer gin_trgm_ops);
CREATE INDEX idx_li_state ON fec_filing_lineitems (entity_state);
CREATE INDEX idx_li_date  ON fec_filing_lineitems (transaction_date);

VACUUM ANALYZE;
ScaleTimePlanCompute
2.4M#14.8 ms
random_page_cost = 10, first draw. BitmapAnd held even though the trigram estimate came back 14,215 against 3,619 actual, 4x high. 67 heap blocks, Execution Time 4.830 ms.
QUERY PLAN
Bitmap Heap Scan on fec_filing_lineitems  (cost=2596.14..5123.92 rows=56 width=44) (actual time=4.592..4.797 rows=12 loops=1)
  Recheck Cond: ((entity_state = 'MD'::text) AND (contributor_employer ~~* '%MICROSOFT%'::text))
  Filter: ((transaction_date >= '2025-01-01'::date) AND (transaction_date < '2026-01-01'::date) AND (schedule_code = 'Schedule A'::text) AND (entity_type = 'IND'::text))
  Rows Removed by Filter: 55
  Heap Blocks: exact=67
  Buffers: shared hit=116 read=44
  ->  BitmapAnd  (cost=2596.14..2596.14 rows=277 width=0) (actual time=4.525..4.525 rows=0 loops=1)
        Buffers: shared hit=49 read=44
        ->  Bitmap Index Scan on idx_li_state  (cost=0.00..750.83 rows=46720 width=0) (actual time=2.365..2.365 rows=48669 loops=1)
              Index Cond: (entity_state = 'MD'::text)
              Buffers: shared read=44
        ->  Bitmap Index Scan on idx_li_emp_trgm  (cost=0.00..1845.03 rows=14215 width=0) (actual time=1.120..1.120 rows=3619 loops=1)
              Index Cond: (contributor_employer ~~* '%MICROSOFT%'::text)
              Buffers: shared hit=49
Planning:
  Buffers: shared hit=57
Planning Time: 0.292 ms
Execution Time: 4.830 ms
17.32s
2.4M#24.6 ms
random_page_cost = 10, second draw. BitmapAnd held again, this time with the trigram estimate at 140 against 3,525 actual, 25x low. 70 heap blocks, Execution Time 4.562 ms. Same plan shape as the first draw.
QUERY PLAN
Bitmap Heap Scan on fec_filing_lineitems  (cost=2558.34..2588.14 rows=1 width=44) (actual time=4.401..4.530 rows=17 loops=1)
  Recheck Cond: ((entity_state = 'MD'::text) AND (contributor_employer ~~* '%MICROSOFT%'::text))
  Filter: ((transaction_date >= '2025-01-01'::date) AND (transaction_date < '2026-01-01'::date) AND (schedule_code = 'Schedule A'::text) AND (entity_type = 'IND'::text))
  Rows Removed by Filter: 54
  Heap Blocks: exact=70
  Buffers: shared hit=119 read=44
  ->  BitmapAnd  (cost=2558.34..2558.34 rows=3 width=0) (actual time=4.338..4.339 rows=0 loops=1)
        Buffers: shared hit=49 read=44
        ->  Bitmap Index Scan on idx_li_state  (cost=0.00..783.43 rows=48400 width=0) (actual time=2.296..2.296 rows=48671 loops=1)
              Index Cond: (entity_state = 'MD'::text)
              Buffers: shared read=44
        ->  Bitmap Index Scan on idx_li_emp_trgm  (cost=0.00..1774.66 rows=140 width=0) (actual time=1.096..1.096 rows=3525 loops=1)
              Index Cond: (contributor_employer ~~* '%MICROSOFT%'::text)
              Buffers: shared hit=49
Planning:
  Buffers: shared hit=55
Planning Time: 0.268 ms
Execution Time: 4.562 ms
17.58s
Raw
Raw benchmark JSON

You should mess with this knob locally because if like everyone else you're using SSDs, the random_page_cost should be 1.1 globally. I measured that as the right fix when the planner is rejecting a good index in favor of a seq scan. Here the problem goes the other way: 1.1 prices a random heap page at almost free, and on a 60 GB table, fetching 3,449 of them is almost free times 3,449.

Caveats

The row cap on an ExoBench run is 3M, a cap I set, so everything I've said about 60M is a linear-esque fit from points at 250K, 1M, 2.4M, and 2.9M. Index work and heap blocks scaled about linearly in every configuration. Wall clocks less so: the seq-scan 2.9M point came in at 718 ms against ~543 ms if you scale the cached 250K point, because that draw read 358,191 buffers cold. The projection puts the composite index around 34 ms of index work plus ~1,800 heap blocks at 60M. I'd defend the ordering of the options hard, and the absolute numbers only as far as your storage resembles mine.

The eight tool cards are byte-verbatim payloads. Two of the rerun schemas carry one extra set_config line appending an extensions schema to the search_path, a no-op on standard Postgres. The data is synthetic, matched to the question's shapes; the real FEC employer field is messier and its true Microsoft population sets the cost of the trigram-alone plan, so that one number transfers least. And btree_gin ships in contrib and the major managed platforms carry it, but confirm on yours before planning around it.

The Answer

Here's the whole circus on one grid, heap blocks touched from 1M to 2.9M rows:

Heap blocks touched as the table grows. Two index shapes crawl along the floor. The red line is the identical two-index pair on the control table after one growth step convinced the planner to drop the BitmapAnd: 27 blocks to 4,176.
Two indexes on the JSONB table (held): 34 heap blocks at 1M rows, 104 at 2.9M. Two indexes on the plain-column control (flipped when grown): 27 at 1M, 4,176 at 2.9M. Composite btree_gin: 30 at 1M, 90 at 2.9M.
Two index shapes hold near the floor from 1M to 2.9M rows: the pair on the JSONB table (34 to 104 heap blocks) and the composite (30 to 90). The identical pair on the control table flipped plans at 2.9M and went from 27 blocks to 4,176.
Two indexes on the JSONB table (held): 34 heap blocks at 1M rows, 104 at 2.9M. Two indexes on the plain-column control (flipped when grown): 27 at 1M, 4,176 at 2.9M. Composite btree_gin: 30 at 1M, 90 at 2.9M.

The two solid lines crawl along the floor. The dashed line is the same index pair on the control table, after one growth step dropped the intersection... and in milliseconds:

Approach1M2.9MPlan across runs
Seq scan baseline193.8 ms718 msone plan, always
Expression btree (state, date)25.7 ms102.8 msstable, and worse than nothing
Trigram GIN alone6.5 ms17.2 msstable, cost tracks the nationwide term
Trigram GIN + state btree, JSONB2.8 ms9.3 msheld, on a 4x over-estimate
Trigram GIN + state btree, control1.9 ms17.8 msflipped when the table grew
Composite btree_gin0.76 ms11.5 msidentical in every run

The JSONB pair held because the estimate missed 4x high. The control, same indexes, same generator, flipped at 2.9M and paid 4,176 heap blocks.

The ship-it block:

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

SET maintenance_work_mem = '2GB';  -- GIN builds on 60M rows crawl without this

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

DROP INDEX CONCURRENTLY IF EXISTS idx_state;
DROP INDEX CONCURRENTLY IF EXISTS idx_date;
DROP INDEX CONCURRENTLY IF EXISTS idx_emp_trgm;

SHOW random_page_cost;  -- if this says 1.1, that's a problem on a 60 GB table

Go and Inspect Your Own Coin Flips

A plan flip in production doesn't announce itself. It shows up as a query whose latency has two personalities, and there's a cheap smell test for that in pg_stat_statements:

SELECT left(query, 60) AS query,
       calls,
       round(mean_exec_time::numeric, 2)   AS mean_ms,
       round(stddev_exec_time::numeric, 2) AS stddev_ms
FROM pg_stat_statements
WHERE calls > 100
ORDER BY stddev_exec_time / GREATEST(mean_exec_time, 0.01) DESC
LIMIT 10;

A stddev that rivals the mean has several possible causes, cache state, load, parameter skew, and one of them is a query living two plans. Another is a prepared statement that switched to a generic plan on its sixth execution, which looks the same in pg_stat_statements and happens on a schedule. The way to find out which, without touching production, is the loop I ran above: rebuild the shape on a throwaway database, at more than one scale, more than once, and read the plans.

btree_gin should be working now in ExoBench. Try it

That prompt, by the way, is me shipping a sandbox fix mid-investigation, the extension wasn't grantable when I started, which is the kind of thing you get to do when you built the sandbox. ExoBench is the loop in this post: you describe the schema and the scales, it spins up an ephemeral Postgres with synthetic data, runs the real plans, and hands them back, and your production data is never part of the conversation. Setup takes about 30 seconds: getting started.

A cost-based planner is exactly as stable as the gap between its top two options. I gave it two plans 19 units apart and it flipped a coin. I gave it one, and there was nothing left to flip.

Appendix: schemas and index DDL

Full tool payloads for the flip pair, the random_page_cost pair, the control-table growth run, the original-session JSONB pair, and the composite-index run are embedded in the cards above, byte for byte, worker ids included. What follows is the readable DDL.

The synthetic FEC generator (JSONB silver table)

${SCALE} is the row count per scale point (250K, 1M, 2.4M, and 2.9M in this post). Distributions: 80% Schedule A, 90% IND, uniform across 50 states (~2% each), dates spread over ~3.5 years, and a 0.15% employer needle split across MICROSOFT / MICROSOFT CORPORATION / MICROSOFT CORP.

CREATE TABLE silver_fec_efiling_itemizations (
  id          bigint PRIMARY KEY,
  record_type text,
  record_data jsonb
);

INSERT INTO silver_fec_efiling_itemizations (id, record_type, record_data)
SELECT
  i,
  CASE WHEN random() < 0.80 THEN 'Schedule A'
       WHEN random() < 0.50 THEN 'Schedule B'
       ELSE 'Schedule E' END,
  jsonb_build_object(
    'form_type', 'SA11AI',
    'filer_committee_id_number', 'C00' || lpad(((random()*99999)::int)::text, 6, '0'),
    'transaction_id', 'SA11AI.' || i,
    'entity_type', CASE WHEN random() < 0.90 THEN 'IND'
                        WHEN random() < 0.5 THEN 'ORG' ELSE 'PAC' END,
    'contributor_organization_name', '',
    'contributor_last_name', (ARRAY['SMITH','JOHNSON','WILLIAMS','BROWN','JONES','GARCIA','MILLER','DAVIS','RODRIGUEZ','MARTINEZ','HERNANDEZ','LOPEZ','GONZALEZ','WILSON','ANDERSON','THOMAS','TAYLOR','MOORE','JACKSON','MARTIN'])[(random()*19)::int+1],
    'contributor_first_name', (ARRAY['JAMES','MARY','ROBERT','PATRICIA','JOHN','JENNIFER','MICHAEL','LINDA','DAVID','ELIZABETH','WILLIAM','BARBARA','RICHARD','SUSAN','JOSEPH','JESSICA','THOMAS','SARAH','CHARLES','KAREN'])[(random()*19)::int+1],
    'contributor_middle_name', '',
    'contributor_street_1', ((random()*9999)::int)::text || ' MAIN STREET',
    'contributor_city', (ARRAY['BALTIMORE','BETHESDA','ROCKVILLE','SILVER SPRING','ANNAPOLIS','COLUMBIA','FREDERICK','GAITHERSBURG','TOWSON','BOWIE'])[(random()*9)::int+1],
    'contributor_state', (ARRAY['AL','AK','AZ','AR','CA','CO','CT','DE','FL','GA','HI','ID','IL','IN','IA','KS','KY','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ','NM','NY','NC','ND','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY'])[(random()*49)::int+1],
    'contributor_zip_code', lpad(((random()*99999)::int)::text, 5, '0'),
    'election_code', 'P2026',
    'contribution_date', to_char('2023-01-01'::timestamp + (random() * 1300 * interval '1 day'), 'YYYY-MM-DD"T"HH24:MI:SS'),
    'contribution_amount', round((random()*2900)::numeric, 2),
    'contribution_aggregate', round((random()*5800)::numeric, 2),
    'contribution_purpose_descrip', 'CONTRIBUTION',
    'contributor_employer', CASE WHEN random() < 0.0015
         THEN (ARRAY['MICROSOFT','MICROSOFT CORPORATION','MICROSOFT CORP'])[(random()*2)::int+1]
         ELSE (ARRAY['ACME','GLOBEX','INITECH','UMBRELLA','STARK','WAYNE','TYRELL','SOYLENT','MASSIVE','VANDELAY','HOOLI','CYBERDYNE','OSCORP','ABSTERGO','APERTURE','WEYLAND','NAKATOMI','SABRE','LUMON','ENCOM','RETIRED','NOT EMPLOYED','SELF EMPLOYED','NORTHROP','LOCKHEED','JOHNS HOPKINS','MARRIOTT','UNDER ARMOUR','T ROWE PRICE','MCCORMICK'])[(random()*29)::int+1]
              || ' ' || (ARRAY['INC','LLC','CORPORATION','GROUP','HOLDINGS','SYSTEMS','TECHNOLOGIES','PARTNERS','SERVICES','INTERNATIONAL'])[(random()*9)::int+1]
       END,
    'contributor_occupation', (ARRAY['ENGINEER','ATTORNEY','PHYSICIAN','TEACHER','CONSULTANT','EXECUTIVE','RETIRED','ANALYST','MANAGER','SALES'])[(random()*9)::int+1],
    'memo_code', '',
    'memo_text_description', 'EARMARKED CONTRIBUTION REPORTED ON LINE 11AI',
    'image_number', ((random()*999999999999999)::bigint)::text,
    'file_number', ((random()*9999999)::int)::text,
    'sub_id', ((random()*9999999999)::bigint)::text
  )
FROM generate_series(1, ${SCALE}) i;

CREATE INDEX idx_record_type ON silver_fec_efiling_itemizations (record_type);
VACUUM ANALYZE;

The plain-column control table

Same generator logic promoted into typed columns, used to demonstrate the flip without JSONB expression estimation in the picture. The full INSERT is in the flip card's schemaTemplate above.

CREATE TABLE fec_filing_lineitems (
  id                     bigint PRIMARY KEY,
  schedule_code          text,
  entity_type            text,
  entity_state           text,
  contributor_first_name text,
  contributor_last_name  text,
  contributor_employer   text,
  contribution_amount    numeric,
  transaction_date       date
);
-- INSERT mirrors the JSONB generator's CASE expressions, one per column.

CREATE INDEX idx_li_emp_trgm ON fec_filing_lineitems USING gin (contributor_employer gin_trgm_ops);
CREATE INDEX idx_li_state    ON fec_filing_lineitems (entity_state);
CREATE INDEX idx_li_date     ON fec_filing_lineitems (transaction_date);
VACUUM ANALYZE;

Every index variant tested

-- baseline
CREATE INDEX idx_record_type ON silver_fec_efiling_itemizations (record_type);

-- expression btree (lured the planner off the trigram index; 122 ms in the
-- original session, 25.7 ms in the rerun, same ~4,180 buffers)
CREATE INDEX idx_fec_state_date ON silver_fec_efiling_itemizations
  ((record_data->>'contributor_state'), (record_data->>'contribution_date'))
  WHERE record_type = 'Schedule A' AND record_data->>'entity_type' = 'IND';

-- trigram alone (measured 6.5 ms; heap work tracks the nationwide term)
CREATE INDEX idx_fec_employer_trgm ON silver_fec_efiling_itemizations
  USING gin ((record_data->>'contributor_employer') gin_trgm_ops)
  WHERE record_type = 'Schedule A' AND record_data->>'entity_type' = 'IND';

-- the two-index pair (34 heap blocks at 1M, 104 at 2.9M, BitmapAnd held
-- in both original-session points)
CREATE INDEX idx_emp_trgm ON silver_fec_efiling_itemizations
  USING gin ((record_data->>'contributor_employer') gin_trgm_ops);
CREATE INDEX idx_state ON silver_fec_efiling_itemizations
  ((record_data->>'contributor_state'));

-- the composite (measured 0.76 ms, one plan shape in every run)
CREATE INDEX idx_fec_state_emp_gin ON silver_fec_efiling_itemizations
  USING gin ((record_data->>'contributor_state'),
             (record_data->>'contributor_employer') gin_trgm_ops);