From: Richard Huxton <dev@archonet.com>
To: pgsql-sql@postgresql.org
Subject: Re: Scalar in a range (but textual not numeric)
Date: Wed, 25 Feb 2004 20:36:40 +0000
Message-ID: <200402252036.40747.dev@archonet.com> (raw)
In-Reply-To: <200402251918.20289.dev@archonet.com>
References: <200402251918.20289.dev@archonet.com>
On Wednesday 25 February 2004 19:18, Richard Huxton wrote:
> Large table representing non-overlapping blocks:
>
> blocks(id int4, min varchar, max varchar)
>
> SELECT * FROM blocks WHERE 'ABCDE' BETWEEN min AND max;
>
> The estimator gets the wrong plan because it doesn't realise there's (at
> most) only one block that can match.
Well, replying to myself (just one of my many bad habits) the best I've come
up with so far is to add another column with a trimmed string and do a direct
comparison against that too:
SELECT * FROM blocks WHERE substring('ABCDE',1,3)=block_segment AND 'ABCDE'
BETWEEN min AND max
This gives the planner something to work with, and on 7.4 it even renders it
down to 'ABC' first too (nice :-)
That's not quite the same though, because it means I need to split
ABCAA..ABDBB into ABCAA..ABCZZ and ABDAA..ABDZZ but it's close enough unless
someone is feeling clever this evening.
--
Richard Huxton
Archonet Ltd
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com
Subject: Re: Scalar in a range (but textual not numeric)
In-Reply-To: <200402252036.40747.dev@archonet.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