Message-ID: <fdb33101-a974-481c-ada3-0d7ab052c656@enterprisedb.com>
Date: Sat, 23 Mar 2024 12:24:42 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Index plan returns different results to sequential scan
To: Peter Geoghegan <pg@bowt.ie>, John Burns <john@impactdatametrics.com>
Cc: pgsql-bugs@lists.postgresql.org
References: <3B86F188-F4BF-4331-AD79-E5FF2D0711A3@impactdatametrics.com>
 <CAH2-WznBkvQDTSJJOB=EeDDs7ZENmpKSCwP5Kf+pNfeJSDoswQ@mail.gmail.com>
Content-Language: en-US
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: 
 <CAH2-WznBkvQDTSJJOB=EeDDs7ZENmpKSCwP5Kf+pNfeJSDoswQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/fdb33101-a974-481c-ada3-0d7ab052c656%40enterprisedb.com>
Precedence: bulk


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