_postgis_index_extent gives bogus results on combined gist indexes

[Björn Harrtell]

Reproduction:

drop table if exists test;
with vals(k, geom) as (values (1, 'POINT(1 1)'::geometry), (2, 'POINT(1 2)'::geometry))
select * into table test from vals;
analyze test;
select st_estimatedextent('test', 'geom');
-- BOX(1 0.995000004768372,1 2.005000114440918)
create index test_geom_idx on test using gist (geom);
select st_estimatedextent('test', 'geom');
-- BOX(1 1,1 2)
drop index test_geom_idx;
create index test_k_geom_idx on test using gist (k, geom);
select st_estimatedextent('test', 'geom');
-- BOX(1.401298464324817E-45 1,2.802596928649634E-45 1)

This is on:

POSTGIS="3.2.0dev 3.2.0beta2-11-g27f44ecf6" [EXTENSION] PGSQL="120" GEOS="3.9.0-CAPI-1.16.2" PROJ="7.2.1" LIBXML="2.9.12" LIBJSON="0.15" (core procs from "3.2.0dev 3.2.0beta2-3-geda70258f" need upgrade)

But I think it's an old bug.

[strk]

I would completely drop support for indices in ST_EstimatedExtent, as I think an extent read from an index is not *estimated* but actual. Better have another signature to read the extent from an index.

[strk]

See #5120 too

[strk]

It looks like we already have a _postgis_index_extent() since version 2.5.0 ( #2256 ) That method is just not documented, and its signature name seems to be for internal use only.

Bjorn when trying your example on a PostgreSQL-13 I get a failure here:

create index test_k_geom_idx on test using gist (k, geom);
ERROR:  data type integer has no default operator class for access method "gist"

It can be fixed by CREATE EXTENSION btree_gist;

So I confirm the bug is still present, and I'm updating the description to mention it's about _postgis_index_extent() effectively. New repro:

CREATE EXTENSION IF NOT EXISTS btree_gist;
DROP TABLE IF EXISTS test;

WITH vals(k, geom) as (
  values
    (1, 'POINT(1 1)'::geometry),
    (2, 'POINT(1 2)'::geometry)
)
SELECT * INTO TABLE test FROM vals;

SELECT _postgis_index_extent('test', 'geom');
-- NULL

CREATE INDEX test_geom_idx ON test using gist (geom);
SELECT _postgis_index_extent('test', 'geom');
-- BOX(1 1,1 2)

DROP INDEX test_geom_idx;
CREATE INDEX test_k_geom_idx on test using gist (k, geom);
SELECT _postgis_index_extent('test', 'geom');
-- BOX(1.401298464324817e-45 1,2.802596928649634e-45 1)

[pramsey]

Firstly, index extents are still estimated, since an index with a lot of deletions can be quite over-determined relative to the actual data extent.

Secondly, the answer to the bug is either getting very clever about finding the geometry part of the index or just noticing it's a multi-key index and skipping the index check in that case. Given the rarity of the multi-key case, I think I opt for the latter.

[Paul Ramsey <pramsey@…>]

In eec975e/git:

Correctly read geometry extent off multi-key GIST indexes. References #5032

[pramsey]

Turned out to be not so hard to just read the right key out of the index.

[Paul Ramsey <pramsey@…>]

In eb871d03/git:

Correctly read geometry extent off multi-key GIST indexes. References #5032

[Paul Ramsey <pramsey@…>]

In 6908a1e/git:

Correctly read geometry extent off multi-key GIST indexes. References #5032

[Paul Ramsey <pramsey@…>]

In 295d6c7/git:

Correctly read geometry extent off multi-key GIST indexes. References #5032