Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id D0D62475CFA for ; Wed, 27 Mar 2002 10:51:36 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g2RFpUS18992; Wed, 27 Mar 2002 10:51:30 -0500 (EST) To: "Peter Galbavy" Cc: pgsql-sql@postgresql.org Subject: Re: Non-use of index ? In-reply-to: <013001c1d583$04296ba0$2028a8c0@carpenter> References: <013001c1d583$04296ba0$2028a8c0@carpenter> Comments: In-reply-to "Peter Galbavy" message dated "Wed, 27 Mar 2002 11:32:02 +0000" Date: Wed, 27 Mar 2002 10:51:30 -0500 Message-ID: <18989.1017244290@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200203/361 X-Sequence-Number: 7235 "Peter Galbavy" writes: > We have two seemingly identical SQL statements: > SELECT user_name, status, count(status) AS total FROM messages > WHERE client_id = '89' AND user_name in ('U66220', 'U66221') > GROUP BY user_name, status; > and > SELECT user_name, status, count(status) AS total FROM messages > WHERE (client_id = '89' AND user_name = 'U66221') > OR (client_id = '89' AND user_name = 'U66220') > GROUP BY user_name, status; > The (relevant) index on that table is: > CREATE INDEX messages_200203_ix2 ON messages_200203 (client_id, user_name); > The first statement is *slow* by an order of magnitude in comparison to the > second. Presumably "client_id = '89'" alone is not very selective? > Now I can intellectually understand why the index is only used for the > second statement, but shouldn't the optimiser be able to use the index to > match (client_id, user_name) against the index through each combination and > produce the second query by itself ? The Postgres planner does not explore that particular direction of transformation of user qualifications --- it generally thinks that conjunctive-normal-form quals are fine as-is, and it won't change them to disjunctive form unless that makes the quals more compact. Apparently Oracle uses a different set of heuristics to decide what transformations of the quals are worth generating and comparing to indexes. There is a hack in 7.2 that would have discovered this optimization if the index had been on (user_name, client_id) ... but as-is, the possibility of using an OR index search is never considered. regards, tom lane