Received: from mailstore-1.mail.knowledge.com (frida.london.knowtion.net [213.170.2.69]) by postgresql.org (Postfix) with ESMTP id E0DD347591D for ; Wed, 27 Mar 2002 07:00:59 -0500 (EST) Received: from mbloxltd-2.dsl.easynet.co.uk ([217.204.221.98] helo=carpenter) by mailstore-1.mail.knowledge.com with asmtp (Exim 3.33 #1) id 16qBes-0002nz-00 for pgsql-sql@postgresql.org; Wed, 27 Mar 2002 11:32:03 +0000 Message-ID: <013001c1d583$04296ba0$2028a8c0@carpenter> From: "Peter Galbavy" To: Subject: Non-use of index ? Date: Wed, 27 Mar 2002 11:32:02 -0000 Organization: Knowtion Ltd. MIME-Version: 1.0 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Archive-Number: 200203/355 X-Sequence-Number: 7229 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. Using EXPLAIN says that only the second statement is using the index... 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 ? Using 'SET ENABLE_SEQSCAN TO OFF' makes no difference. As a side note, the original SQL from an Oracle database that is being ported to PostgreSQL did not suffer this 'problem'. Peter