From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Scalar in a range (but textual not numeric)
Date: Wed, 25 Feb 2004 17:32:28 -0500
Message-ID: <7179.1077748348@sss.pgh.pa.us> (raw)
In-Reply-To: <200402252208.55502.dev@archonet.com>
References: <200402251918.20289.dev@archonet.com>
<6803.1077744728@sss.pgh.pa.us>
<200402252208.55502.dev@archonet.com>
Richard Huxton <dev@archonet.com> writes:
> On Wednesday 25 February 2004 21:32, Tom Lane wrote:
>>> SELECT * FROM blocks WHERE 'ABCDE' BETWEEN min AND max;
>>
>> Even if it did realize that, it couldn't do much, because this query
>> isn't indexable as it stands.
> Well, it is in the sense that an index can be used. Here I'd defined pkey as
> (min,max,id) and set enable_seqscan=off
> -> Index Scan using prnblock_range_pkey on prnblock_range
> (cost=0.00..1403.99 rows=892 width=33) (actual time=23.88..24.07 rows=1
> loops=1)
> Index Cond: (('09050091234'::character varying >= pr_min) AND
> ('09050091234'::character varying <= pr_max))
You're mistaking "I can put all the relevant columns into an index" for
"this index is useful". There's a reason why the planner will not use
that plan without a gun to its head, and it is that you have only a
one-sided restriction on the first index column. At runtime, the index
machinery will have to scan starting at pr_min = '09050091234' and
continuing clear to the end of the index. It will avoid visiting the
heap for the index entries past the desired range, but since it does not
understand that there's any relation between the pr_min and pr_max
columns, it won't realize that it doesn't need to continue the index
scan past the first failure of '09050091234' <= pr_max. For all it
knows, there could be lots of entries with larger pr_min and smaller
pr_max.
Now that I think about it, you can exploit your knowledge that the
min/max subranges don't overlap without building a new index type.
What you have to do is put the knowledge into the query. Instead of
WHERE 'ABCDE' >= pr_min AND 'ABCDE' <= pr_max
try writing
WHERE 'ABCDE' >= pr_min AND 'ABCDE' <= pr_max
AND pr_min < (SELECT pr_min FROM table
WHERE pr_min > 'ABCDE'
ORDER BY pr_min LIMIT 1)
The idea here is to add an upper bound on pr_min to the index scan
conditions, so that the scan can stop short of the end of the index.
The sub-SELECT will efficiently use the index to pick out a safe
stopping point.
regards, tom lane
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: tgl@sss.pgh.pa.us, dev@archonet.com
Subject: Re: Scalar in a range (but textual not numeric)
In-Reply-To: <7179.1077748348@sss.pgh.pa.us>
* 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