Message-ID: <b4ee59ca-a99a-474f-97cc-b26d4ce51e43@enterprisedb.com>
Date: Fri, 22 Mar 2024 02:21:49 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Index plan returns different results to sequential scan
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: John Burns <john@impactdatametrics.com>
Cc: pgsql-bugs@lists.postgresql.org
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>
Content-Language: en-US
In-Reply-To: <27496161-e7ec-47bd-b559-3571fde4dcb4@enterprisedb.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/b4ee59ca-a99a-474f-97cc-b26d4ce51e43%40enterprisedb.com>
Precedence: bulk

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=dd299df8189bd00fbe54b72c64f43b6af2ffeccd

==========================================================================
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