BUG #19443: GIN jsonb_path_ops index not used when many rows contain nested null values (PostgreSQL 18.3)
The following bug has been logged on the website:
Bug reference: 19443
Logged by: Xianghang Zheng
Email address: zheng_xianghang@163.com
PostgreSQL version: 18.3
Operating system: Linux x86_64
Description:
I believe I've found a planner bug in PostgreSQL 18.3 where a GIN index
using
jsonb_path_ops is not selected for @? queries when the jsonb column contains
a large number of rows with nested null values.
Problem Description
-------------------
When a jsonb column has a high proportion of rows with {"a": null}, and a
small
subset with valid nested paths like {"a":{"b":{"c":1}}}, the query planner
chooses a sequential scan instead of using a jsonb_path_ops GIN index for
the
path query @? '$.a.b.c' LIMIT 10.
This leads to unexpected performance degradation.
Version Information
-------------------
PostgreSQL 18.3
Platform
--------
Linux x86_64
Complete Reproducible Test Case
-------------------------------
-- Create test table
DROP TABLE IF EXISTS test_jsonb;
CREATE TABLE test_jsonb (
id serial primary key,
data jsonb not null
);
-- Insert 100,000 rows: 90% null, 10% valid nested JSON
INSERT INTO test_jsonb (data)
SELECT
CASE WHEN random() < 0.1 THEN
'{"a":{"b":{"c":1}}}'::jsonb
ELSE
'{"a":null}'::jsonb
END
FROM generate_series(1,100000);
-- Create jsonb_path_ops index
CREATE INDEX idx_test_jsonb ON test_jsonb USING gin (data jsonb_path_ops);
-- Query that should use the index
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM test_jsonb
WHERE data @? '$.a.b.c'
LIMIT 10;
Actual Output
-------------
Limit (cost=0.00..19.37 rows=10 width=36) (actual time=0.067..0.117
rows=10 loops=1)
Buffers: shared hit=2
-> Seq Scan on test_jsonb (cost=0.00..1937.00 rows=1000 width=36)
(actual time=0.060..0.107 rows=10 loops=1)
Filter: (data @? '$."a"."b"."c"'::jsonpath)
Rows Removed by Filter: 110
Buffers: shared hit=2
Planning Time: 0.592 ms
Execution Time: 0.152 ms
The planner chooses a sequential scan even though a valid jsonb_path_ops
index exists.
Expected Behavior
-----------------
The planner should use the GIN index for the path query.
Additional Observations
-----------------------
1. The index works correctly when forced with enable_seqscan = off.
2. The default jsonb_ops opclass does not have this problem.
3. The issue appears to be caused by null scalar values not being indexed by
jsonb_path_ops,
which makes the planner avoid the index due to misestimation.
Impact
------
This bug affects production systems using jsonb_path_ops for path queries
on tables with many null-containing JSON structures, causing unexpected
full table scans.
Workaround
----------
1. Use the default jsonb_ops opclass
2. Use a partial index excluding nulls
3. Force index usage with enable_seqscan = off
I'm happy to provide more details or run further tests if needed.
PG Bug reporting form <noreply@postgresql.org> writes:
When a jsonb column has a high proportion of rows with {"a": null}, and a
small
subset with valid nested paths like {"a":{"b":{"c":1}}}, the query planner
chooses a sequential scan instead of using a jsonb_path_ops GIN index for
the
path query @? '$.a.b.c' LIMIT 10.
This leads to unexpected performance degradation.
If I force the plan selection with enable_seqscan, the query gets
slower, and not by a small amount:
regression=# EXPLAIN (ANALYZE)
SELECT * FROM test_jsonb
WHERE data @? '$.a.b.c'
LIMIT 10;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..1.91 rows=10 width=22) (actual time=0.009..0.024 rows=10 loops=1)
-> Seq Scan on test_jsonb (cost=0.00..1938.00 rows=10130 width=22) (actual time=0.008..0.022 rows=10 loops=1)
Filter: (data @? '$."a"."b"."c"'::jsonpath)
Rows Removed by Filter: 75
Planning Time: 0.060 ms
Execution Time: 0.036 ms
(6 rows)
regression=# set enable_seqscan TO 0;
SET
regression=# EXPLAIN (ANALYZE)
SELECT * FROM test_jsonb
WHERE data @? '$.a.b.c'
LIMIT 10;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=150.51..151.31 rows=10 width=22) (actual time=4.963..4.979 rows=10 loops=1)
-> Bitmap Heap Scan on test_jsonb (cost=150.51..965.13 rows=10130 width=22) (actual time=4.963..4.977 rows=10 loops=1)
Recheck Cond: (data @? '$."a"."b"."c"'::jsonpath)
Rows Removed by Index Recheck: 75
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_test_jsonb (cost=0.00..147.98 rows=10130 width=0) (actual time=4.900..4.900 rows=100000 loops=1)
Index Cond: (data @? '$."a"."b"."c"'::jsonpath)
Planning Time: 0.122 ms
Execution Time: 4.998 ms
(9 rows)
So it appears to me that the planner's decision was correct.
It looks like the index is unable to distinguish real matches
from false matches in this example, so it returns all 100000
rows and the bitmap heap scan has to filter most of them away.
So that's why it's slow. I'm not sure offhand if the planner's
cost model is accurately modeling this situation or if it
just got lucky ... but in any case, you've not demonstrated
that there's a planner bug.
regards, tom lane