Opened 14 months ago
Closed 14 months ago
Last modified 14 months ago
#5120 closed defect (fixed)
Not null return from ST_EstimatedExtent after TRUNCATE on table with a spatial index defined
|Reported by:||strk||Owned by:||pramsey|
When a GiST index is present on a geometry column it makes no sense for ST_EstimatedExtent to fallback to stats-based view, if the index based bbox is returned as null, because that means the table is empty.
To reproduce the problem:
- Create a spatial table, with an index
- Add some rows, ANALYZE (creates stats)
- Truncate the table
- Run ST_EstimatedExtent for the spatial column
Note there's NO WAY to clear the stats on a truncated table, to my knowledge. The only way to "refresh" the stats would be to re-add some rows to it. Instead, in the scenario above, users are left to an ST_EstimatedExtent return which is plain bogus, when all the information would be available to the function to return an informed NULL (no rows in the table, according to index).
See also #5032 for a possibly misinformed value from index query too.
I've tested this in 3.3.0dev (master branch) but it's very likely an old issue.
Change History (8)
comment:1 by , 14 months ago
comment:2 by , 14 months ago
comment:3 by , 14 months ago
I've pushed a fix for this case. I'm tempted to backport it but I wonder if others have a different view on the matter. Paul ?
Basically the question is: is there ANY reason why a NULL gbox fetched from an index found by table_get_spatial_index should not be trusted by ST_EstimatedExtent ?
I don't see any reason for that. If index knows no BOX the table *must* be empty (while it *could* be empty when the index returns an actual box, due to pending vacuum …)
comment:4 by , 14 months ago
|Summary:||ST_EstimatedExtent returns stats based extent even if index is present → Not null return from ST_EstimatedExtent after TRUNCATE on table with a spatial index defined|
comment:5 by , 14 months ago
comment:6 by , 14 months ago
comment:7 by , 14 months ago
|Status:||new → closed|
comment:8 by , 14 months ago
|Milestone:||PostGIS 3.3.0 → PostGIS 3.0.6|
Interesting enough, commented-out tests in regress/core/estimatedextent.sql show that even the index-provided read can be dirty, and only becomes accurate on VACUUM FULL. Still, it at least possible to make it accurate (by runnign VACUUM) while this is impossible to do with pg_statistic, looks like.