Stephan Szabo <[EMAIL PROTECTED]> 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 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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
