From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Peter Geoghegan <pg@bowt.ie>
To: John Burns <john@impactdatametrics.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Index plan returns different results to sequential scan
Date: Sat, 23 Mar 2024 12:24:42 +0100
Message-ID: <fdb33101-a974-481c-ada3-0d7ab052c656@enterprisedb.com> (raw)
In-Reply-To: <CAH2-WznBkvQDTSJJOB=EeDDs7ZENmpKSCwP5Kf+pNfeJSDoswQ@mail.gmail.com>
References: <3B86F188-F4BF-4331-AD79-E5FF2D0711A3@impactdatametrics.com>
<CAH2-WznBkvQDTSJJOB=EeDDs7ZENmpKSCwP5Kf+pNfeJSDoswQ@mail.gmail.com>
On 3/23/24 03:24, Peter Geoghegan wrote:
> On Thu, Mar 21, 2024 at 2:03 PM John Burns <john@impactdatametrics.com> wrote:
>> The query is SELECT * FROM XXX where postcode % ’NW10’
>> To create a sample table — create table XXX ( udprn bigint, postcode postcode )
>> To Index it CREATE INDEX on XXX(postcode)
>
> The opfamily's % operator uses the B-Tree equality strategy. This
> means that it works the same way as = works in most other opfamilies.
>
> I don't see how equality can work reliably here. A query with a
> predicate "WHERE my_indexed_postcode_column % ‘NW10’" seems to work by
> masking the value stored in the index, throwing away some amount of
> suffix bytes in the process. But the values from the index are still
> stored in their original order -- the temporarily masked suffix bytes
> aren't masked in the index, of course (they're only masked
> temporarily, by the cross-type equality operator %).
>
> Wouldn't you need something closer to "WHERE
> my_indexed_postcode_column >= ‘NW10’ and my_indexed_postcode_column <
> ‘NW11’" for this to work reliably?
>
Yeah, I was not sure how come this could be processed using equality
operator, but I got distracted by bisecting this to a particular commit.
I didn't realize the commit might have changed how much we rely on the
opfamily to do things correctly.
I do think the '%' operator is pretty close to what we do for LIKE with
prefix patterns for text:
explain select * from t where a like 'aa%';
QUERY PLAN
-----------------------------------------------------------------------
Index Only Scan using t_a_idx on t (cost=0.29..4.31 rows=1 width=33)
Index Cond: ((a ~>=~ 'aa'::text) AND (a ~<~ 'ab'::text))
Filter: (a ~~ 'aa%'::text)
(3 rows)
So I guess postcode would need to do something similar - treat the '%'
operator as separate from opclass equality, and define a new support
procedure akin to text_support, translating the '%' into the range quals
that can match the index.
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, pg@bowt.ie, john@impactdatametrics.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Index plan returns different results to sequential scan
In-Reply-To: <fdb33101-a974-481c-ada3-0d7ab052c656@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