Missing results after adding index

[Kjell Kvinge]

Thank you :)

tir. 25. feb. 2025 kl. 01:29 skrev Paul Ramsey <pramsey at cleverelephant.ca>:

> 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
> >:
>
>> 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>
>> 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/20250225/3aed9f40/attachment.htm>