A common drawback of storing data in JSON fields is limited planner visibility: the optimizer doesn't automatically inspect documents, so selectivity estimates for JSON (or JSONB or BSON) predicates are often weak when useful statistics aren’t available. That’s expected—if you choose JSON to avoid defining the full application schema in the database upfront, the database can’t infer everything about those fields.
But this isn’t an all-or-nothing tradeoff. Even in MongoDB, you still declare part of the schema in practice—especially filter fields—through indexes. PostgreSQL, gives you two levers:
- expression indexes (which provide both access paths and statistics)
- extended statistics (which improve estimates even without adding an index).
I'm using an example from an old article by Dan Robinson on the Heap blog: When To Avoid JSONB In A PostgreSQL Schema. At the time, PostgreSQL 9.4 didn't support extended statistics, but expression indexes could have solved this (example on db<>fiddle), avoiding the expensive nested-loop join and producing good cardinality estimates.
I've run the following in a lab:
CREATE TABLE measurements (tick BIGSERIAL PRIMARY KEY, record JSONB NOT NULL)
;
INSERT INTO measurements (record)
SELECT jsonb_build_object(
'value_1', trunc(2 * random())::int,
'value_2', trunc(2 * random())::int,
'value_3', trunc(2 * random())::int,
'scientist_id', trunc(10000 * random() + 1)::bigint
)
FROM generate_series(1, 100000)
;
CREATE TABLE scientist_labs (scientist_id BIGINT PRIMARY KEY, lab_name TEXT NOT NULL)
;
INSERT INTO scientist_labs (scientist_id, lab_name)
SELECT i,(ARRAY['California Scientific','Industrial Labs','Western Science People'])[(i % 3) + 1]
FROM generate_series(1, 10000) AS i
;
VACUUM (ANALYZE) measurements
;
VACUUM (ANALYZE) scientist_labs
;
Get the execution plan for this query:
EXPLAIN (ANALYZE, BUFFERS)
SELECT s.lab_name, COUNT( * )
FROM (
SELECT (record ->> 'scientist_id')::BIGINT AS scientist_id
FROM measurements
WHERE (record ->> 'value_1')::INT = 0
AND (record ->> 'value_2')::INT = 0
AND (record ->> 'value_3')::INT = 0
) AS m
JOIN scientist_labs AS s
ON s.scientist_id = m.scientist_id
GROUP BY s.lab_name
ORDER BY s.lab_name
;
The estimated cardinality for "measurements" after the filter is rows=1, which is why PostgreSQL chooses a Nested Loop. However, the actual row count is rows=12518.00, so "scientist_labs" is accessed loops=12518 times:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=5827.32..5827.34 rows=1 width=28) (actual time=145.667..166.436 rows=3.00 loops=1)
Group Key: s.lab_name
Buffers: shared hit=39186
-> Sort (cost=5827.32..5827.33 rows=1 width=20) (actual time=139.215..150.787 rows=12518.00 loops=1)
Sort Key: s.lab_name
Sort Method: quicksort Memory: 645kB
Buffers: shared hit=39186
-> Nested Loop (cost=0.29..5827.31 rows=1 width=20) (actual time=0.156..126.483 rows=12518.00 loops=1)
Buffers: shared hit=39186
-> Seq Scan on measurements (cost=0.00..5819.00 rows=1 width=105) (actual time=0.029..60.878 rows=12518.00 loops=1)
Filter: ((((record ->> 'value_1'::text))::integer = 0) AND (((record ->> 'value_2'::text))::integer = 0) AND (((record ->> 'value_3'::text))::integer = 0))
Rows Removed by Filter: 87482
Buffers: shared hit=1819
-> Index Scan using scientist_labs_pkey on scientist_labs s (cost=0.29..8.31 rows=1 width=28) (actual time=0.002..0.002 rows=1.00 loops=12518)
Index Cond: (scientist_id = ((measurements.record ->> 'scientist_id'::text))::bigint)
Index Searches: 12518
Buffers: shared hit=37367
Planning Time: 0.433 ms
Execution Time: 166.557 ms
(19 rows)
Compare the Planned rows with the actual rows:

This is not optimal but easily fixed by indexing the fields used to filter:
CREATE INDEX measurements_value_1_idx ON measurements (
((record ->> 'value_1')::INT)
);
CREATE INDEX measurements_value_2_idx ON measurements (
((record ->> 'value_2')::INT)
);
CREATE INDEX measurements_value_3_idx ON measurements (
((record ->> 'value_3')::INT)
);
ANALYZE measurements;
We index an expression to retrieve its value by using the full path, and specify the data type the filter uses. This is like declaring part of the schema for the "schemaless" datatype—the parts correspond to the access pattern.
ANALYZE also gathers statistics about the index keys for the query planner, which uses them for the query:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=4309.88..4309.88 rows=3 width=28) (actual time=96.348..96.403 rows=3.00 loops=1)
Sort Key: s.lab_name
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=1977
-> HashAggregate (cost=4309.82..4309.85 rows=3 width=28) (actual time=96.310..96.362 rows=3.00 loops=1)
Group Key: s.lab_name
Batches: 1 Memory Usage: 32kB
Buffers: shared hit=1977
-> Hash Join (cost=1395.30..4247.13 rows=12538 width=20) (actual time=25.260..80.897 rows=12518.00 loops=1)
Hash Cond: (((measurements.record ->> 'scientist_id'::text))::bigint = s.scientist_id)
Buffers: shared hit=1977
-> Bitmap Heap Scan on measurements (cost=1099.30..3913.50 rows=12538 width=105) (actual time=2.671..25.013 rows=12518.00 loops=1)
Recheck Cond: ((((record ->> 'value_2'::text))::integer = 0) AND (((record ->> 'value_1'::text))::integer = 0))
Filter: (((record ->> 'value_3'::text))::integer = 0)
Rows Removed by Filter: 12593
Heap Blocks: exact=1819
Buffers: shared hit=1906
-> BitmapAnd (cost=1099.30..1099.30 rows=24880 width=0) (actual time=2.416..2.430 rows=0.00 loops=1)
Buffers: shared hit=87
-> Bitmap Index Scan on measurements_value_2_idx (cost=0.00..545.74 rows=49793 width=0) (actual time=1.210..1.213 rows=49896.00 loops=1)
Index Cond: (((record ->> 'value_2'::text))::integer = 0)
Index Searches: 1
Buffers: shared hit=43
-> Bitmap Index Scan on measurements_value_1_idx (cost=0.00..547.04 rows=49967 width=0) (actual time=1.144..1.147 rows=50332.00 loops=1)
Index Cond: (((record ->> 'value_1'::text))::integer = 0)
Index Searches: 1
Buffers: shared hit=44
-> Hash (cost=171.00..171.00 rows=10000 width=28) (actual time=22.575..22.584 rows=10000.00 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 727kB
Buffers: shared hit=71
-> Seq Scan on scientist_labs s (cost=0.00..171.00 rows=10000 width=28) (actual time=0.025..11.113 rows=10000.00 loops=1)
Buffers: shared hit=71
Planning Time: 0.278 ms
Execution Time: 96.487 ms
(34 rows)
The estimated cardinality of "measurements" (rows=12538) is close to the actual count (rows=12518), so the query planner chooses a Hash Join and reads "scientist_labs" only once.
The query planner estimates the predicate’s overall cardinality by multiplying the selectivity of each field. This works here because the fields are uncorrelated. Three separate indexes offer flexibility when queries don’t filter on all three fields, but require a bitmap combination. If the fields always appear together in the filter, a compound index would be better:
CREATE INDEX measurements_value_123_idx ON measurements (
((record ->> 'value_1')::INT)
,
((record ->> 'value_2')::INT)
,
((record ->> 'value_3')::INT)
);
ANALYZE measurements;
The query planner prefers this index, and it still estimates cardinality correctly: rows=12564 for rows=12518.00:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=2922.62..2922.63 rows=3 width=28) (actual time=71.266..71.279 rows=3.00 loops=1)
Sort Key: s.lab_name
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=1890 read=12
-> HashAggregate (cost=2922.57..2922.60 rows=3 width=28) (actual time=71.246..71.257 rows=3.00 loops=1)
Group Key: s.lab_name
Batches: 1 Memory Usage: 32kB
Buffers: shared hit=1890 read=12
-> Hash Join (cost=500.48..2859.75 rows=12564 width=20) (actual time=17.645..59.549 rows=12518.00 loops=1)
Hash Cond: (((measurements.record ->> 'scientist_id'::text))::bigint = s.scientist_id)
Buffers: shared hit=1890 read=12
-> Bitmap Heap Scan on measurements (cost=204.48..2526.04 rows=12564 width=105) (actual time=0.669..16.853 rows=12518.00 loops=1)
Recheck Cond: ((((record ->> 'value_1'::text))::integer = 0) AND (((record ->> 'value_2'::text))::integer = 0) AND (((record ->> 'value_3'::text))::integer = 0))
Heap Blocks: exact=1819
Buffers: shared hit=1819 read=12
-> Bitmap Index Scan on measurements_value_123_idx (cost=0.00..201.34 rows=12564 width=0) (actual time=0.465..0.466 rows=12518.00 loops=1)
Index Cond: ((((record ->> 'value_1'::text))::integer = 0) AND (((record ->> 'value_2'::text))::integer = 0) AND (((record ->> 'value_3'::text))::integer = 0))
Index Searches: 1
Buffers: shared read=12
-> Hash (cost=171.00..171.00 rows=10000 width=28) (actual time=16.956..16.959 rows=10000.00 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 727kB
Buffers: shared hit=71
-> Seq Scan on scientist_labs s (cost=0.00..171.00 rows=10000 width=28) (actual time=0.016..8.454 rows=10000.00 loops=1)
Buffers: shared hit=71
Planning:
Buffers: shared hit=26 read=1
Planning Time: 0.521 ms
Execution Time: 71.397 ms
(28 rows)
The same from the PostgreSQL extension visualization in Visual Studio Code:

Even when I disable index access, the cardinality estimate remains correct:
postgres=# set enable_bitmapscan to off;
SET
postgres=# EXPLAIN (ANALYZE, BUFFERS)
SELECT s.lab_name, COUNT( * )
FROM (
SELECT (record ->> 'scientist_id')::BIGINT AS scientist_id
FROM measurements
WHERE (record ->> 'value_1')::INT = 0
AND (record ->> 'value_2')::INT = 0
AND (record ->> 'value_3')::INT = 0
) AS m
JOIN scientist_labs AS s
ON s.scientist_id = m.scientist_id
GROUP BY s.lab_name
ORDER BY s.lab_name
;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=6215.58..6215.59 rows=3 width=28) (actual time=98.676..98.691 rows=3.00 loops=1)
Sort Key: s.lab_name
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=1890
-> HashAggregate (cost=6215.53..6215.56 rows=3 width=28) (actual time=98.652..98.665 rows=3.00 loops=1)
Group Key: s.lab_name
Batches: 1 Memory Usage: 32kB
Buffers: shared hit=1890
-> Hash Join (cost=296.00..6152.71 rows=12564 width=20) (actual time=22.378..85.932 rows=12518.00 loops=1)
Hash Cond: (((measurements.record ->> 'scientist_id'::text))::bigint = s.scientist_id)
Buffers: shared hit=1890
-> Seq Scan on measurements (cost=0.00..5819.00 rows=12564 width=105) (actual time=0.029..34.747 rows=12518.00 loops=1)
Filter: ((((record ->> 'value_1'::text))::integer = 0) AND (((record ->> 'value_2'::text))::integer = 0) AND (((record ->> 'value_3'::text))::integer = 0))
Rows Removed by Filter: 87482
Buffers: shared hit=1819
-> Hash (cost=171.00..171.00 rows=10000 width=28) (actual time=22.328..22.331 rows=10000.00 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 727kB
Buffers: shared hit=71
-> Seq Scan on scientist_labs s (cost=0.00..171.00 rows=10000 width=28) (actual time=0.008..11.150 rows=10000.00 loops=1)
Buffers: shared hit=71
Planning Time: 0.402 ms
Execution Time: 98.816 ms
(22 rows)
Statistics collected for the index key columns appear in pg_stats, with the index name in "tablename" and the data type in "attname". A number may be appended to the data type to make it unique:
postgres=# select tablename, attname, n_distinct, most_common_vals from pg_stats where tablename like 'measurements%'
;
tablename | attname | n_distinct | most_common_vals
----------------------------+---------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
measurements | tick | -1 |
measurements | record | -0.48375 | {"{\"value_1\": 0, \"value_2\": 0, \"value_3\": 0, \"scientist_id\": 9422}","{\"value_1\": 1, \"value_2\": 0, \"value_3\": 1, \"scientist_id\": 3615}","{\"value_1\": 1, \"value_2\": 0, \"value_3\": 1, \"scientist_id\": 5835}","{\"value_1\": 1, \"value_2\": 0, \"value_3\": 1, \"scientist_id\": 9281}","{\"value_1\": 1, \"value_2\": 1, \"value_3\": 0, \"scientist_id\": 2627}","{\"value_1\": 1, \"value_2\": 1, \"value_3\": 0, \"scientist_id\": 8940}"}
measurements_value_1_idx | int4 | 2 | {0,1}
measurements_value_2_idx | int4 | 2 | {0,1}
measurements_value_3_idx | int4 | 2 | {1,0}
measurements_value_123_idx | int4 | 2 | {0,1}
measurements_value_123_idx | int41 | 2 | {0,1}
measurements_value_123_idx | int42 | 2 | {1,0}
(8 rows)
postgres=# select tablename, exprs, most_common_vals from pg_stats_ext where tablename like 'measurements%'
;
tablename | exprs | most_common_vals
-----------+-------+------------------
(0 rows)
This shows that a compound index provides statistics for individual columns, not groups of columns. If the columns are correlated, the estimate may be inaccurate. In this case, we can gather additional statistics for the column group:
CREATE STATISTICS measurements_values_stats (mcv, dependencies)
ON ((record ->> 'value_1')::INT)
,
((record ->> 'value_2')::INT)
,
((record ->> 'value_3')::INT)
FROM measurements;
ANALYZE measurements
;
The extended statistics are visible in pg_stats_ext
postgres=# select tablename, exprs, most_common_vals from pg_stats_ext where tablename like 'measurements%'
;
tablename | exprs | most_common_vals
--------------+---------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------
measurements | {"((record ->> 'value_1'::text))::integer","((record ->> 'value_2'::text))::integer","((record ->> 'value_3'::text))::integer"} | {{0,1,1},{0,0,1},{0,1,0},{1,0,1},{0,0,0},{1,1,1},{1,0,0},{1,1,0}}
(1 row)
Even if I have no indexes here, the query planner can use the extended statistics to estimate selectivity accurately:
DROP INDEX measurements_value_1_idx;
DROP INDEX measurements_value_2_idx;
DROP INDEX measurements_value_3_idx;
DROP INDEX measurements_value_123_idx;
I confirm that only the extended statistics on the column group remain, then get the execution plan for the same query:
postgres=# select tablename, attname, n_distinct, most_common_vals from pg_stats where tablename like 'measurements%'
;
tablename | attname | n_distinct | most_common_vals
--------------+---------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------
measurements | tick | -1 |
measurements | record | -0.48568 | {"{\"value_1\": 1, \"value_2\": 0, \"value_3\": 0, \"scientist_id\": 3770}","{\"value_1\": 1, \"value_2\": 0, \"value_3\": 0, \"scientist_id\": 6752}"}
(2 rows)
postgres=# select tablename, exprs, most_common_vals from pg_stats_ext where tablename like 'measurements%';
tablename | exprs | most_common_vals
--------------+---------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------
measurements | {"((record ->> 'value_1'::text))::integer","((record ->> 'value_2'::text))::integer","((record ->> 'value_3'::text))::integer"} | {{0,1,1},{0,0,1},{0,1,0},{1,0,1},{0,0,0},{1,1,1},{1,0,0},{1,1,0}}
(1 row)
postgres=# EXPLAIN (ANALYZE, BUFFERS)
SELECT s.lab_name, COUNT( * )
FROM (
SELECT (record ->> 'scientist_id')::BIGINT AS scientist_id
FROM measurements
WHERE (record ->> 'value_1')::INT = 0
AND (record ->> 'value_2')::INT = 0
AND (record ->> 'value_3')::INT = 0
) AS m
JOIN scientist_labs AS s
ON s.scientist_id = m.scientist_id
GROUP BY s.lab_name
ORDER BY s.lab_name
;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=6214.67..6214.68 rows=3 width=28) (actual time=109.840..109.855 rows=3.00 loops=1)
Sort Key: s.lab_name
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=1890
-> HashAggregate (cost=6214.61..6214.64 rows=3 width=28) (actual time=109.819..109.833 rows=3.00 loops=1)
Group Key: s.lab_name
Batches: 1 Memory Usage: 32kB
Buffers: shared hit=1890
-> Hash Join (cost=296.00..6152.36 rows=12450 width=20) (actual time=21.074..95.602 rows=12518.00 loops=1)
Hash Cond: (((measurements.record ->> 'scientist_id'::text))::bigint = s.scientist_id)
Buffers: shared hit=1890
-> Seq Scan on measurements (cost=0.00..5819.00 rows=12450 width=105) (actual time=0.027..39.998 rows=12518.00 loops=1)
Filter: ((((record ->> 'value_1'::text))::integer = 0) AND (((record ->> 'value_2'::text))::integer = 0) AND (((record ->> 'value_3'::text))::integer = 0))
Rows Removed by Filter: 87482
Buffers: shared hit=1819
-> Hash (cost=171.00..171.00 rows=10000 width=28) (actual time=21.024..21.028 rows=10000.00 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 727kB
Buffers: shared hit=71
-> Seq Scan on scientist_labs s (cost=0.00..171.00 rows=10000 width=28) (actual time=0.006..10.399 rows=10000.00 loops=1)
Buffers: shared hit=71
Planning Time: 0.235 ms
Execution Time: 110.004 ms
(22 rows)
The estimated rows=12450 is close to the real value rows=12518.00.
JSON columns in a relational database don’t force an all-or-nothing choice between flexibility and performance. PostgreSQL can inspect JSONB contents: for top-level fields—or nested fields accessible without traversing an array—you can create B-tree expression indexes that provide both an access path and the statistics the planner needs to estimate cardinality accurately. When you need better estimates without index overhead, or when predicates involve correlated fields, extended statistics on expressions (PostgreSQL 14+) let you specify exactly which field combinations the planner should understand. In practice, efficient applications use both approaches: keep flexible schema details known only to the application, and declare the fields that drive access patterns to the database through indexes, statistics, or both.

Top comments (1)
Good point on extended statistics here. In practice I've found the expression index is often the better first move on JSONB filter fields because it gives you both the access path and the stats, but CREATE STATISTICS is handy when the predicate columns are correlated and you can't justify another index on a write-heavy table. One thing I'd add is to check with EXPLAIN ANALYZE that the planner actually uses the expression, since the query has to repeat the exact same expression for it to match.