From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: Jimmy Mäkelä <jimmy.makela@agent25.se>
Cc: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>
Subject: Re: Unique indexes not unique?
Date: Mon, 13 Jan 2003 10:31:10 -0500
Message-ID: <25149.1042471870@sss.pgh.pa.us> (raw)
In-Reply-To: <20030113040014.Q53527-100000@megazone23.bigpanda.com>
References: <20030113040014.Q53527-100000@megazone23.bigpanda.com>
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 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
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, sszabo@megazone23.bigpanda.com, jimmy.makela@agent25.se
Subject: Re: Unique indexes not unique?
In-Reply-To: <25149.1042471870@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