JSON in Postgres: still fast at 1M rows of JSONB?

It's a full-table-scan 283 MB on a GIN index compared to 369 KB if it were a normal column! Real runs recorded with ExoBench.

By Alexander Ioffe

November 11, 2026

Summary for the Impatient

At 1M rows, counting orders with units_shipped > 950 in a jsonb column read the whole table, GIN index or not. With GIN, that was 34,598 pages, 283 MB, in 76 ms. A plain index on details->>'units_shipped' changed nothing: Postgres never used it. An index with the ::int cast read 25,595 pages. A normal column read 45 pages, 369 KB, in 8 ms. At 10K rows, every version finished in under 3 ms. Measured on Postgres 17.11 with ExoBench, 10 runs each.

It's always fast on dev

Like anyone else sane in 2026, you store the part of each row that keeps changing in a jsonb column, because half the fields in your order table will be irrelevant next quarter. Your admin dashboard reads it three ways: a count of big orders, a count for one region, and the top 100 by units shipped.

The column has a GIN index like Postgres docs lead with, and on your dev database every tile comes back instantly. With 10K orders, so does no index at all.

Adding zeros to whatever benchmark comes out of data at this scale is a fool's measuring-stick. Stopwatch-ing data on 10K row-scale tells you close to nothing about a million rows.

Here's the table and its index:

CREATE TABLE orders (
  id           bigint PRIMARY KEY,
  purchaser_id bigint NOT NULL,
  created_at   timestamptz NOT NULL,
  total_cost   int NOT NULL,
  details      jsonb
);

CREATE INDEX orders_details_gin ON orders USING GIN (details jsonb_path_ops);

And a typical details value. Each of its 10 fields is missing from about 10% of orders.

{
  "region": "region-07",
  "status": "paid",
  "channel": "web",
  "customer_tier": "gold",
  "units_shipped": 847,
  "payment_method": "card",
  "country": "US",
  "currency": "USD",
  "device": "desktop",
  "item_count": 3
}

I pasted this question into Claude Code with ExoBench connected. This is the recording.

The recorded run

Hi ExoBench, here's my query. Find me a better index or a faster rewrite:

SELECT count(*)
FROM orders
WHERE (details->>'units_shipped')::int > 950;

…then benchmark it until it can't get any faster.

Schema

SELECT setseed(0.42);

CREATE TABLE orders (
  id           bigint PRIMARY KEY,
  purchaser_id bigint NOT NULL,
  created_at   timestamptz NOT NULL,
  total_cost   int NOT NULL,
  details      jsonb
);

INSERT INTO orders
SELECT i,
       floor(random() * GREATEST(${SCALE} / 10, 1))::bigint + 1,
       now() - random() * interval '365 days',
       floor(random() * 100000)::int,
       jsonb_strip_nulls(jsonb_build_object(
         'region',         CASE WHEN random() < 0.1 THEN NULL ELSE 'region-' || lpad((floor(random() * 50)::int + 1)::text, 2, '0') END,
         'status',         CASE WHEN random() < 0.1 THEN NULL ELSE (ARRAY['paid','pending','shipped','delivered','refunded'])[floor(random() * 5)::int + 1] END,
         'channel',        CASE WHEN random() < 0.1 THEN NULL ELSE (ARRAY['web','mobile','store','phone'])[floor(random() * 4)::int + 1] END,
         'customer_tier',  CASE WHEN random() < 0.1 THEN NULL ELSE (ARRAY['bronze','silver','gold','platinum'])[floor(random() * 4)::int + 1] END,
         'units_shipped',  CASE WHEN random() < 0.1 THEN NULL ELSE floor(random() * 1001)::int END,
         'payment_method', CASE WHEN random() < 0.1 THEN NULL ELSE (ARRAY['card','paypal','bank_transfer','gift_card'])[floor(random() * 4)::int + 1] END,
         'country',        CASE WHEN random() < 0.1 THEN NULL ELSE (ARRAY['US','CA','GB','DE','FR','JP','AU','BR'])[floor(random() * 8)::int + 1] END,
         'currency',       CASE WHEN random() < 0.1 THEN NULL ELSE (ARRAY['USD','CAD','GBP','EUR','JPY','AUD','BRL'])[floor(random() * 7)::int + 1] END,
         'device',         CASE WHEN random() < 0.1 THEN NULL ELSE (ARRAY['desktop','mobile','tablet'])[floor(random() * 3)::int + 1] END,
         'item_count',     CASE WHEN random() < 0.1 THEN NULL ELSE floor(random() * 10)::int + 1 END
       ))
FROM generate_series(1, ${SCALE}) AS g(i);

CREATE INDEX orders_details_gin ON orders USING GIN (details jsonb_path_ops);

VACUUM ANALYZE;

Recorded ExoBench run of this statement on a fresh database. Replays in about 32 seconds; a live run takes about 2 minutes.

Get Started with ExoBench

It ran on the same seeded data as the tables below, so its page counts match them exactly. Its milliseconds differ by run-to-run noise.

The GIN index sat this one out

GIN index, count of units_shipped > 950

Orders	Mean time	Plan
10K	2.01 ms	Seq Scan
100K	12.62 ms	Parallel Seq Scan
1M	76.19 ms	Parallel Seq Scan

The time grew with the table. Here's the count:

SELECT count(*)
FROM orders
WHERE (details->>'units_shipped')::int > 950;

From the 1M plan:

->  Parallel Seq Scan on orders  (avg_cost=0.00..42931.41 avg_rows=138890 avg_width=0) (actual avg_time=0.044..67.680 stddev_time=0.058..0.673 avg_rows=14994 avg_loops=3)
      Filter: (((details ->> 'units_shipped'::text))::integer > 950)
      Rows Removed by Filter: avg=318339
      Buffers: shared avg_hit=34598

A jsonb_path_ops GIN index answers one kind of question: does this JSON contain that value (@>). It can't answer greater-than, so Postgres opened every row in the table, all 34,598 pages. It threw away about 955,000 rows, 318,339 in each of three processes, to count 44,982.

At 10K rows, the same count took 2 ms. Nobody notices 2 ms.

Surely an index on the field fixes it

A btree index on the exact field the count filters on:

CREATE INDEX orders_units_shipped_text ON orders ((details->>'units_shipped'));

Here's what we get:

Orders	Mean time	Plan
10K	1.42 ms	Seq Scan
100K	9.69 ms	Parallel Seq Scan
1M	56.01 ms	Parallel Seq Scan

Same scan, same filter, and same 34,598 pages. 56 vs 61 ms is just noise. The page count didn't even wobble.

Casting versus Normal Columns

With the same cast in the index as in the query, Postgres actually uses it:

CREATE INDEX orders_units_shipped_int ON orders (((details->>'units_shipped')::int));

Here's what we get:

Orders	Mean time	Plan
10K	0.32 ms	Bitmap Heap Scan
100K	3.07 ms	Bitmap Heap Scan
1M	31.95 ms	Bitmap Heap Scan

Mind you it'll still read 74% of the table, because the 44,982 matching orders are spread all over it.

Now let's get serious and try 10 normal columns plus a jsonb column extra, for anything else. Notice units_shipped is going to be a normal int field.

CREATE TABLE orders (
  id             bigint PRIMARY KEY,
  purchaser_id   bigint NOT NULL,
  created_at     timestamptz NOT NULL,
  total_cost     int NOT NULL,
  region         text,
  status         text,
  channel        text,
  customer_tier  text,
  units_shipped  int,
  payment_method text,
  country        text,
  currency       text,
  device         text,
  item_count     int,
  extra          jsonb
);

CREATE INDEX orders_units_shipped ON orders (units_shipped);

Fun fact, we don't need to cast anymore!

SELECT count(*)
FROM orders
WHERE units_shipped > 950;

Here's what we get:

Orders	Mean time	Plan
10K	0.08 ms	Index Only Scan
100K	0.55 ms	Index Only Scan
1M	8.02 ms*	Index Only Scan

Let's eyeball the plan for a second.

->  Index Only Scan using orders_units_shipped on orders  (avg_cost=0.42..965.54 avg_rows=45778 avg_width=0) (actual avg_time=0.015..4.021 stddev_time=0.009..2.239 avg_rows=44982 avg_loops=1)
      Index Cond: (units_shipped > 950)
      Heap Fetches: avg=0
      Buffers: shared avg_hit=45

See that? Postgres answered the count from the index alone and never opened the table.

Setup	Pages read at 1M	Data	Mean time
GIN	34,598	283 MB	76.19 ms
Typed path index	25,595	210 MB	31.95 ms
Normal column	45	369 KB	8.02 ms

Same question, same 44,982 orders, 769 times less data.

Your query has its own casts, operators and sort orders. If Prisma or Drizzle writes it, you may never have read the SQL, doing log: ['query'] in Prisma or logger: true in Drizzle helps. You can also have a look at pg_stat_statements and pg_indexes tables. Like most of us, you'll probably be doing this with Claude Code or Codex so just wire up the ExoBench MCP server right there and tell ExoBench to optimize whatever the agent finds. You'll get working indexes on the spot.

What this run doesn't show

The data is synthetic and uniform, with about 10% of each field missing, and the extra column ended up empty. Your data is skewed in its own ways.
Only reads were measured. GIN and the other indexes also cost time on every write, which this run didn't measure.
Rows here are small, about 280 bytes each. Bigger documents mean more bytes in every whole-table scan.
Milliseconds moved by up to 36% between runs with identical plans. Page counts moved by 6 pages at most.
It ran on Postgres 17.11, on ExoBench's hardware, with a warm cache, at up to 1M rows.

All fifteen runs at 1M rows

I ran every setup against every query through ExoBench, fifteen runs on the same seeded data. Every version of the count found the same 44,982 orders. The region tile, in the form each setup's index can use:

-- GIN
SELECT count(*) FROM orders WHERE details @> '{"region": "region-07"}';

-- No index, plain path index, typed path index
SELECT count(*) FROM orders WHERE details->>'region' = 'region-07';

-- Normal columns
SELECT count(*) FROM orders WHERE region = 'region-07';

Setup	Count > 950	Region lookup	Top 100
No index	34,598	34,598	34,706
GIN	34,598	14,003	34,712
Plain path index	34,598	14,012	34,712
Typed path index	25,595	14,012	101
Normal columns	45	20	100

Pages read per query. A page is 8,192 bytes. Bold means Postgres read the whole table. In milliseconds, the whole-table cells ran 46 to 97 ms, and the indexed cells 0.10 to 32 ms.

Top down: GIN helped only the lookup it was built for. The plain index helped nothing numeric. The typed index helped the sort a lot and the count a little. Normal columns read the fewest pages in all three, and never the whole table.

What one refresh costs

Those three queries are your admin dashboard's three tiles. With the GIN setup, one refresh reads 683 MB and takes about 190 ms back to back. With normal columns, it reads 1.4 MB in about 10 ms. Firing all three at once with Promise.all shortens the wait, but Postgres still reads the same 683 MB.

If 190 ms sounds tiny, remember that with that screen open on 20 desks, each refreshing every 5 seconds, Postgres serves 4 refreshes a second. Also it means concurrent whole-table scans that compete for the same CPUs. For the GIN version, that's about 2.7 GB a second to redraw three tiles. For normal columns, it's 5.4 MB.

Takeaways

Bottom line, if you can migrate promote the fields you're going to need to filter or sort on to normal columns. If you can't yet, give each one an expression index with the same cast as your query. For a sort, give the index the same ordering as the ORDER BY, such as DESC NULLS LAST.

If pg_stat_statements is enabled, this lists the five jsonb queries that take the most total time:

SELECT query, calls, mean_exec_time
FROM pg_stat_statements
WHERE query LIKE '%->>%' OR query LIKE '%@>%'
ORDER BY total_exec_time DESC
LIMIT 5;

Prisma and Drizzle both print the SQL they send if you ask: log: ['query'] in Prisma, logger: true in Drizzle.

Your dev database will keep saying it's fast.

The long version, with the plans (May 2026, Postgres 17.8): JSONB Paths or GIN or Columns?