DEV Community

Cover image for JSONB performance: declare access patterns to PostgreSQL query planner
Franck Pachot
Franck Pachot

Posted on Edited on

JSONB performance: declare access patterns to PostgreSQL query planner

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
;
Enter fullscreen mode Exit fullscreen mode

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
;
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)

Enter fullscreen mode Exit fullscreen mode

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
;
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)

Collapse
 
lycore profile image
Lycore Development •

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.