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:4 by , 5 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|