Not null return from ST_EstimatedExtent after TRUNCATE on table with a spatial index defined

[strk]

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.

[strk]

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.

[Sandro Santilli <strk@…>]

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

[strk]

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 …)

[Sandro Santilli <strk@…>]

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

[Sandro Santilli <strk@…>]

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

[Sandro Santilli <strk@…>]

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