Missing results after adding index

[Paul Ramsey]

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/94766bfe/attachment.htm>