Minor inaccuracy in jsonb_path_ops documentation
От | Peter Geoghegan |
---|---|
Тема | Minor inaccuracy in jsonb_path_ops documentation |
Дата | |
Msg-id | CAM3SWZSGV44Agpw3EFrZAcKaF2+=80MWjP8dfspGsWadAH4+vA@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Minor inaccuracy in jsonb_path_ops documentation
|
Список | pgsql-hackers |
The jsonb documentation says of the jsonb_path_ops GIN opclass: """ A disadvantage of the jsonb_path_ops approach is that it produces no index entries for JSON structures not containing any values, such as {"a": {}}. If a search for documents containing such a structure is requested, it will require a full-index scan, which is quite slow. jsonb_path_ops is therefore ill-suited for applications that often perform such searches. """ The reference to a full index scan seems questionable. This text should indicate that a sequential scan can be expected. Even without any statistics, in the event of not being able to extract any hash values as GIN keys, the optimizer prefers a sequential scan. Example with query where one jsonb_path_ops GIN hash value is generated: postgres=# explain analyze select count(*) from test where j @> '{"tags":[{"term":"postgres"}]}'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------Aggregate (cost=4732.72..4732.73 rows=1 width=0) (actual time=0.513..0.513 rows=1 loops=1) -> Bitmap Heap Scan on test (cost=33.71..4729.59 rows=1253 width=0) (actual time=0.107..0.496 rows=100 loops=1) Recheck Cond: (j @> '{"tags": [{"term": "postgres"}]}'::jsonb) Heap Blocks: exact=100 -> Bitmap Index Scan on ttt (cost=0.00..33.40 rows=1253 width=0) (actual time=0.076..0.076 rows=100 loops=1) Index Cond: (j @> '{"tags": [{"term": "postgres"}]}'::jsonb)Planningtime: 0.083 msExecution time: 0.560 ms (8 rows) Example of empty query hazard with no such hash values: postgres=# explain select count(*) from test where j @> '{"tags":[]}'; QUERY PLAN ------------------------------------------------------------------Aggregate (cost=191519.46..191519.47 rows=1 width=0) -> Seq Scan on test (cost=0.00..191516.33 rows=1253 width=0) Filter: (j @> '{"tags": []}'::jsonb)Planning time:0.073 ms (4 rows) gincostestimate() does at least have the ability to anticipate that a full index scan will be required, and that actually makes the optimizer do the right thing here. Maybe the text quoted above is intended to indicate that if there was an index scan, it would have to be a full index scan, but that doesn't seem appropriate for user facing documentation like this. -- Peter Geoghegan
В списке pgsql-hackers по дате отправления: