Hi all
I have a Postgres/PostGIS database with two separate database tables each of which has the following column structure:
- identifier (text)
- geometry (geometryz,27700)
- properties (jsonb)
I have created a GIN index (jsonb_path_ops) over the properties column on both tables.
As I understand it, Postgres is unable to calculate statistics on the contents of JSONB columns and so should use hard-coded estimates of selectivity when planning queries. However, despite both tables having identical structures, similar row counts, identical indexes, and both having been recently ANALYZEd, I am finding that the query planner is producing different row count estimates between the tables.
To expand: both tables have a 'description' field within their JSONB columns. I am executing the following query:
EXPLAIN
SELECT identifier, geometry, properties FROM table
WHERE properties @@ '$.description == "test"'
ORDER BY identifier;
I am expecting that the selectivity value used for such queries should be 0.010 which is the default result of the matchingsel selectivity function which the @@ operator uses when operating on jsonb.
For both tables, the planner opts for a Bitmap Index Scan -> Sort -> Gather Merge. However, the estimated number of rows returned from the Index Scan node differs substantially.
- Table A (1,611,752 rows): 159 estimated rows (this is roughly 0.01% and makes sense);
- Table B (1,656,110 rows): 16566 estimated rows (roughly 1% - why??)
This difference is causing the planner to come up with some strange plans for queries on Table B which it does not do for Table A. I know that both estimates are just that and that neither is 'better', but I would really prefer it to be consistent and use a selectivity of 0.01 rather than 0.1 as it should be doing.
I know this is getting long now, so apologies. But one thing I did notice and wondered if it could be related is that the pg_stats table for Table A seems to have values for histogram_bounds and correlation, but nulls for these values for Table B.
Any ideas?
Thanks
Joel