Re: Unique indexes not unique?

Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> On Mon, 13 Jan 2003, [iso-8859-1] Jimmy Mkel 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 OR
indexscan 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 the
indexquals generated for each arm of the OR clause. Essentially
this is an index- driven conversion from CNF to DNF.
Implementation is a bit klugy, but better than not exploiting the
extra 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