pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Galbavy <peter.galbavy@knowtion.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Non-use of index ?
Date: Wed, 27 Mar 2002 10:51:30 -0500
Message-ID: <18989.1017244290@sss.pgh.pa.us> (raw)
In-Reply-To: <013001c1d583$04296ba0$2028a8c0@carpenter>
References: <013001c1d583$04296ba0$2028a8c0@carpenter>

"Peter Galbavy" <peter.galbavy@knowtion.net> 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



view thread (2+ messages)

Message-ID: <18989.1017244290@sss.pgh.pa.us>
Permalink:  ../18989.1017244290@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/18989.1017244290@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, peter.galbavy@knowtion.net
  Subject: Re: Non-use of index ?
  In-Reply-To: <18989.1017244290@sss.pgh.pa.us>

* 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