Re: Index plan returns different results to sequential scan

On Thu, Mar 21, 2024 at 2:03 PM John Burns <john(at)impactdatametrics(dot)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?

The relevant rules for btree operator families are described here:

https://www.postgresql.org/docs/devel/btree-behavior.html

Offhand, I suspect that you don't see problems pre-12 B-Tree because
the B-Tree code happened to have been more forgiving of opfamilies
that were broken in this way. Earlier versions treated < and <= as the
same thing in certain contexts.

--
Peter Geoghegan