From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: John Burns <john@impactdatametrics.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Index plan returns different results to sequential scan
Date: Fri, 22 Mar 2024 02:21:49 +0100
Message-ID: <b4ee59ca-a99a-474f-97cc-b26d4ce51e43@enterprisedb.com> (raw)
In-Reply-To: <27496161-e7ec-47bd-b559-3571fde4dcb4@enterprisedb.com>
References: <3B86F188-F4BF-4331-AD79-E5FF2D0711A3@impactdatametrics.com>
<72600f8a-4231-45b7-a116-8ceec5ccf66e@enterprisedb.com>
<881DF028-5222-486C-A13B-451239C1316F@impactdatametrics.com>
<27496161-e7ec-47bd-b559-3571fde4dcb4@enterprisedb.com>
On 3/22/24 00:58, Tomas Vondra wrote:
>
>
> On 3/21/24 21:08, John Burns wrote:
>>
>>
>>> On 21 Mar 2024, at 20:27, Tomas Vondra <tomas.vondra@enterprisedb.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?
>
After some bisecting, this seems to be broken since this PG12 commit:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=dd299df8189bd00fbe54b72c64f43b6af2ffe...
==========================================================================
commit dd299df8189bd00fbe54b72c64f43b6af2ffeccd
tree 931ef720687d61cf5e75464fa0b1c1d75fb3f9d3 tree
parent e5adcb789d80ba565ccacb1ed4341a7c29085238 commit | diff
Make heap TID a tiebreaker nbtree index column.
Make nbtree treat all index tuples as having a heap TID attribute.
Index searches can distinguish duplicates by heap TID, since heap TID is
always guaranteed to be unique. This general approach has numerous
benefits for performance, and is prerequisite to teaching VACUUM to
perform "retail index tuple deletion".
...
==========================================================================
I don't know what the problem is, or whether it's a big in PG or in the
postcode extension (I agree the extension is fairly straightforward).
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tomas.vondra@enterprisedb.com, john@impactdatametrics.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Index plan returns different results to sequential scan
In-Reply-To: <b4ee59ca-a99a-474f-97cc-b26d4ce51e43@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox