Opened 5 months ago

Closed 5 months ago

Last modified 5 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
Priority: medium Milestone: PostGIS 3.0.6
Component: postgis Version: master
Keywords: Cc:

Description

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:

  1. Create a spatial table, with an index
  2. Add some rows, ANALYZE (creates stats)
  3. Truncate the table
  4. 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 strk, 5 months ago

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.

comment:2 by Sandro Santilli <strk@…>, 5 months ago

In df8f5ba/git:

Have ST_EstimatedExtent take a NULL box from index as definitive

References #5120 in master branch (3.3.0dev)
Includes regression test

comment:3 by strk, 5 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 strk, 5 months ago

Summary: ST_EstimatedExtent returns stats based extent even if index is presentNot null return from ST_EstimatedExtent after TRUNCATE on table with a spatial index defined

comment:5 by Sandro Santilli <strk@…>, 5 months ago

In 15fab40/git:

Have ST_EstimatedExtent take a NULL box from index as definitive

References #5120 in 3.2 branch (3.2.2dev)
Includes regression test

comment:6 by Sandro Santilli <strk@…>, 5 months ago

In dbdf868/git:

Have ST_EstimatedExtent take a NULL box from index as definitive

References #5120 in 3.1 branch (3.1.6dev)
Includes regression test

comment:7 by Sandro Santilli <strk@…>, 5 months ago

Resolution: fixed
Status: newclosed

In e1b72b5/git:

Have ST_EstimatedExtent take a NULL box from index as definitive

Closes #5120 in 3.0 branch (3.0.6dev)
Includes regression test

comment:8 by strk, 5 months ago

Milestone: PostGIS 3.3.0PostGIS 3.0.6
Note: See TracTickets for help on using tickets.