Thread: Jsonb first level keys statistic

Jsonb first level keys statistic

From
Игорь Выскорко
Date:
Hi!
I ran into a problem which caused by wrong rows count planner expecting to get when using ?| operator against jsonb
field.

To illustrate the issue:

create table tbl_test (a jsonb);

insert into tbl_test(a)
select jsonb_build_object('val1', 1)
from generate_series(1, 100000);

analyse tbl_test;

explain analyze 
select count(*)
from tbl_test
where a ?| '{val1}'::text[];

                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1986.25..1986.26 rows=1 width=8) (actual time=55.264..55.264 rows=1 loops=1)
   ->  Seq Scan on tbl_test  (cost=0.00..1986.00 rows=100 width=0) (actual time=0.026..43.886 rows=100000 loops=1)
         Filter: (a ?| '{val1}'::text[])
 Planning time: 0.065 ms
 Execution time: 55.310 ms


rows=100 when real value is 100000. In current simple situation it's not an issue, but in real rather complicated
queriesit leads to wrong execution plan. 
 
How can I help planner to get accurate statistic about keys distribution in jsonb field? 

version I'm using: PostgreSQL 9.6.16 on x86_64-pc-linux-gnu 




Re: Jsonb first level keys statistic

From
Michael Lewis
Date:
Other than creating a functional index on the keys that you most want to access and include that function call in your query, I don't know that you can. Functional indexes get statistics while partial indexes do not. If a GIN index using path operator collected stats on the frequency of the keys, then you would be all set I think. Perhaps that is a feature that could be implemented.

Others will chime in I expect, but I tend to avoid using jsonb for any values that I need to use to significant filter a result set (ON and WHERE clauses).