From: John Burns <john@impactdatametrics.com>
To: pgsql-bugs@lists.postgresql.org
Subject: Index plan returns different results to sequential scan
Date: Thu, 21 Mar 2024 18:25:00 +0100
Message-ID: <3B86F188-F4BF-4331-AD79-E5FF2D0711A3@impactdatametrics.com> (raw)
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@impactdatametrics.com

Attachments:
Archive.zip (application/zip, 492.2K) download
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: john@impactdatametrics.com
Subject: Re: Index plan returns different results to sequential scan
In-Reply-To: <3B86F188-F4BF-4331-AD79-E5FF2D0711A3@impactdatametrics.com>
References: <3B86F188-F4BF-4331-AD79-E5FF2D0711A3@impactdatametrics.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