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 TimeSource:https://dev.to/franckpachot/jsonb-performance-declare-access-patterns-to-postgresql-query-planner-2cip

Related posts