Re: Scalar in a range (but textual not numeric)

Richard Huxton <dev(at)archonet(dot)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