Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id A847B476929 for ; Mon, 13 Jan 2003 10:33:51 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 67BF8476CD9 for ; Mon, 13 Jan 2003 10:31:18 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h0DFVB5u025150; Mon, 13 Jan 2003 10:31:11 -0500 (EST) To: Stephan Szabo Cc: =?iso-8859-1?Q?Jimmy_M=E4kel=E4?= , "'pgsql-sql@postgresql.org'" Subject: Re: Unique indexes not unique? In-reply-to: <20030113040014.Q53527-100000@megazone23.bigpanda.com> References: <20030113040014.Q53527-100000@megazone23.bigpanda.com> Comments: In-reply-to Stephan Szabo message dated "Mon, 13 Jan 2003 04:08:04 -0800" Date: Mon, 13 Jan 2003 10:31:10 -0500 Message-ID: <25149.1042471870@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/169 X-Sequence-Number: 11034 Stephan Szabo 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