pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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

view thread (10+ messages)  latest in thread

Message-ID: <3B86F188-F4BF-4331-AD79-E5FF2D0711A3@impactdatametrics.com>
Permalink:  ../3B86F188-F4BF-4331-AD79-E5FF2D0711A3@impactdatametrics.com/
Also on:    postgresql.org/message-id/3B86F188-F4BF-4331-AD79-E5FF2D0711A3@impactdatametrics.com

 · 

reply

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