Re: Index plan returns different results to sequential scan

I’m sorry, but I can’t really say when it broke — we suspect when we went from version 14 to 16 — we use this functionality a lot, and we would have noticed long ago if it was wrong.

As additional information, I created a new operator that used another function — wrapping the function postcode_cmp_partial(...) = 0 — and it exhibited the same behaviour.
Not surprised since the postcode_eq_partial is a trivially simple function.

— John Burns

> On 22 Mar 2024, at 00:58, Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
>
>
>
> On 3/21/24 21:08, John Burns wrote:
>>
>>
>>> On 21 Mar 2024, at 20:27, Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
>>>
>>>
>>> Sorry about that … the patched version of the package, plus a sample data set is in the attached zip file,
>>>
>>> -John
>>>
>
> Thanks, I can reproduce the issue. I don't know why is it happening, but
> the behavior I see is that the (postcode % 'NW10') query somehow misses
> rows with postcodes 'NW10 0AA' - 'NW10 3NL' when executed using index.
>
> I don't see anything obviously wrong in the extension / index pages.
>
> You suggested it used to work OK and then broke. Do you perhaps know at
> which version it broke? Or did you use 9.x until now, upgrades to 16 and
> realized it's broken?
>
>
> regards
>
> --
> Tomas Vondra
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company