Opened 2 years ago

Closed 20 months ago

Last modified 20 months ago

#5032 closed defect (fixed)

_postgis_index_extent gives bogus results on combined gist indexes

Reported by: Björn Harrtell Owned by: pramsey
Priority: medium Milestone: PostGIS 3.0.7
Component: postgis Version: master
Keywords: Cc:

Description (last modified by 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.

Change History (14)

comment:1 by Björn Harrtell, 2 years ago

Description: modified (diff)

comment:2 by robe, 2 years ago

Milestone: PostGIS 3.2.0PostGIS 3.1.5

comment:3 by robe, 2 years ago

Milestone: PostGIS 3.1.5PostGIS 3.3.0

comment:4 by strk, 2 years ago

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.

comment:5 by strk, 2 years ago

See #5120 too

comment:6 by strk, 2 years ago

Summary: ST_EstimatedExtent gives bogus results on combined gist indexes_postgis_index_extent gives bogus results on combined gist indexes

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)

comment:7 by pramsey, 20 months ago

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.

comment:8 by Paul Ramsey <pramsey@…>, 20 months ago

In eec975e/git:

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

comment:9 by pramsey, 20 months ago

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

comment:10 by Paul Ramsey <pramsey@…>, 20 months ago

In eb871d03/git:

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

comment:11 by Paul Ramsey <pramsey@…>, 20 months ago

In 6908a1e/git:

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

comment:12 by Paul Ramsey <pramsey@…>, 20 months ago

In 295d6c7/git:

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

comment:13 by pramsey, 20 months ago

Resolution: fixed
Status: newclosed

comment:14 by robe, 20 months ago

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