Index plan returns different results to sequential scan

Hi…

We’ve been using the postcode extension from PGXN for a number of years and it has not caused any problems until now.
The original developer is no longer around, but a couple of minor changes have kept it operating since version 9.

There is an operator (%) and underlying ‘C’ function (postcode_eq_partial) that provides a containment test
e.g. ‘NW10 5AQ’ % ‘NW10’ is true , ‘L17 3PB’ % ‘NW10’ is false etc.

If we run a query against a table with a postcode field but no index using the % operator we get the correct result.

If we run the same query against the table after adding a tree index on the postcode result we get few fewer results.

If we drop and reindex we get a different number of results.

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 underlying representation of the postcode is a 32 bit integer, so not especially esoteric.

Although the postcode package is probably not especially significant in the scheme of things , the behaviour of indexed versus non-indexed queries is worrisome.

I’ve had to make a couple of minor changes to the postcode package as the Postgres versions have changed, i.e. define TRUE and FALSE in the c header files and change the location of the include files when Postgres 16 arrived, but nothing else.

I’ve attached a sample data file to populate a table that exhibits the behaviour, and the tweaked version of the Postcode package.

Regards, John Burns

Version : PostgreSQL 16.2 (Ubuntu 16.2-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
—
john(at)impactdatametrics(dot)com