Missing results after adding index

[Paul Ramsey]

https://trac.osgeo.org/postgis/ticket/5855#ticket

> On Feb 24, 2025, at 3:55 PM, Paul Ramsey <pramsey at cleverelephant.ca> wrote:
> 
> Yes, that is certainly the place. 
> 
>> On Feb 24, 2025, at 3:51 PM, Kjell Kvinge <kjell at easyconnect.no> wrote:
>> 
>> Thank you for your reply.
>> 
>> I'm really in deep waters here, but I have tried to figure out when this behaviour changed.
>> 
>> It seems to be around this patch https://github.com/postgis/postgis/commit/f5d6c10b7de4cc6cac4e809d495aa4ad25ca4743
>> 
>> Does that make sense?
>> 
>> Thank you.
>> 
>> 
>> 
>> 
>> 
>> man. 24. feb. 2025 kl. 19:54 skrev Paul Ramsey <pramsey at cleverelephant.ca <mailto:pramsey at cleverelephant.ca>>:
>>> Clearly the index test associated with that query is not consistent with the full calculation, so yeah, seems like a bug. 
>>> 
>>> > On Feb 24, 2025, at 10:41 AM, Kjell Kvinge <kjell at easyconnect.no <mailto:kjell at easyconnect.no>> wrote:
>>> > 
>>> > Hi.
>>> > 
>>> > I notice different behaviour in postgis 3.5 compared to previous versions.
>>> > 
>>> > When I add an index to a table containing extents, the result from query disappears 
>>> > 
>>> > 
>>> > Example to isolate this issue:
>>> > 
>>> > # starting docker image postgis/postgis:17-3.5
>>> > 
>>> > create temp table test (street text, extent geometry(Polygon,32633));
>>> > insert into test values ('Knøsesmauet','0103000020797F0000010000000500000010B2468761BDDFC06390523AA1B0594110B2468761BDDFC030554D9BC3B0594107992AF50799DFC030554D9BC3B0594107992AF50799DFC06390523AA1B0594110B2468761BDDFC06390523AA1B05941');
>>> > set enable_seqscan=false;
>>> > SELECT street
>>> > FROM test
>>> > WHERE ST_DFullyWithin(
>>> >     ST_SetSRID(ST_GeomFromText('POINT(-32356 6734606)'), 32633),
>>> >     extent,
>>> >     1700
>>> > );
>>> > 
>>> > -- we get the street as expected.
>>> > 
>>> > -- we add an index
>>> > create index ON test using gist (extent );
>>> > 
>>> > now, running the same query returns zero rows.
>>> > 
>>> > # starting docker image postgis/postgis:17-3.4
>>> > Now, both queries returns expected street.
>>> > 
>>> > 
>>> > Is this a bug I have stumbled upon? or am I missing something here?
>>> > 
>>> > Thank you.
>>> > 
>>> > br.
>>> > Kjell Kvinge.
>>> > 
>>> 
> 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20250224/c39562e5/attachment.htm>