Re: Unique indexes not unique?

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On Mon, 13 Jan 2003, [iso-8859-1] Jimmy M�kel� wrote:
>> And another completely unrelated question... I have got a table with a composite
>> index on A andBb and an index on A
>> which I query with something like this:
>> 
>> SELECT * FROM "table"
>> WHERE (a = 1 OR a = 2 OR a = 3) AND b > 1232132 AND b < 123123123213123
>> 
>> Postgres then chooses to use the index for A three times, which is really slow
>> on my table...

> On my dev (7.4devel) box I see it using the composite index three times,
> but you haven't given explain output for the two queries or any statistics
> information so that doesn't say much.

[ checks CVS logs... ]  I believe 7.2 should behave the same; the
relevant change predated 7.2:

2001-06-05 13:13  tgl
* src/: backend/optimizer/path/allpaths.c,backend/optimizer/path/indxpath.c,
include/optimizer/paths.h,backend/optimizer/path/orindxpath.c:Improve planning of ORindexscan plans: for quals like
WHERE(a = 1 or a = 2) and b =42 and an index on (a,b), include the clause b = 42 in theindexquals generated for each
armof the OR clause.  Essentiallythis is an index- driven conversion from CNF to DNF. Implementation is a bit klugy,
butbetter than not exploiting theextra quals at all ...
 

There may be a datatype coercion issue: in the example as quoted,
'123123123213123' is a bigint constant.  If b is int then that
comparison wouldn't be considered indexable (and if it's bigint, then
the other comparison against b wouldn't be indexable without adding
a cast).
        regards, tom lane