Non-use of index ?

From: "Peter Galbavy" <peter(dot)galbavy(at)knowtion(dot)net>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Non-use of index ?
Date: 2002-03-27 11:32:02
Message-ID: 013001c1d583$04296ba0$2028a8c0@carpenter
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Torsten Willrich 2002-03-27 12:23:29 Problem how to combine to two tables
Previous Message Achilleus Mantzios 2002-03-27 07:23:32 Re: Arrays Design Question