Opened 8 months ago

Last modified 4 months ago

#5032 new defect

_postgis_index_extent gives bogus results on combined gist indexes

Reported by: Björn Harrtell Owned by: pramsey
Priority: medium Milestone: PostGIS 3.3.0
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 (6)

comment:1 by Björn Harrtell, 8 months ago

Description: modified (diff)

comment:2 by robe, 8 months ago

Milestone: PostGIS 3.2.0PostGIS 3.1.5

comment:3 by robe, 6 months ago

Milestone: PostGIS 3.1.5PostGIS 3.3.0

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

See #5120 too

comment:6 by strk, 4 months 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)

Note: See TracTickets for help on using tickets.