pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Peter Galbavy <peter.galbavy@knowtion.net>
To: pgsql-sql@postgresql.org
Subject: Non-use of index ?
Date: Wed, 27 Mar 2002 11:32:02 -0000
Message-ID: <013001c1d583$04296ba0$2028a8c0@carpenter> (raw)

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





view thread (2+ messages)  latest in thread

Message-ID: <013001c1d583$04296ba0$2028a8c0@carpenter>
Permalink:  ../013001c1d583$04296ba0$2028a8c0@carpenter/
Also on:    postgresql.org/message-id/013001c1d583$04296ba0$2028a8c0@carpenter

 · 

reply

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: peter.galbavy@knowtion.net
  Subject: Re: Non-use of index ?
  In-Reply-To: <013001c1d583$04296ba0$2028a8c0@carpenter>

* 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