To: "Karl O. Pinc" <kop@meme.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Poor index choice -- multiple indexes of the same columns 
In-reply-to: <1119913766l.4428l.2l@mofo> 
References: <1119913766l.4428l.2l@mofo>
Comments: In-reply-to "Karl O. Pinc" <kop@meme.com>
	message dated "Mon, 27 Jun 2005 23:09:26 -0000"
Date: Tue, 28 Jun 2005 02:40:56 -0400
Message-ID: <11497.1119940856@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Karl O. Pinc" <kop@meme.com> writes:
> I have a query

> select 1
>   from census
>   where date < '1975-9-21' and sname = 'RAD' and status != 'A'
>   limit 1;

> Explain analyze says it always uses the index made by:

>    CREATE INDEX census_date_sname ON census (date, sname);

> this is even after I made the index:

>    CREATE INDEX census_sname_date ON census (sname, date);

I don't believe that any existing release can tell the difference
between these two indexes as far as costs go.  I just recently
added some code to btcostestimate that would cause it to prefer
the index on (sname, date) but of course that's not released yet.

However: isn't the above query pretty seriously underspecified?
With a LIMIT and no ORDER BY, you are asking for a random one
of the rows matching the condition.  I realize that with
"select 1" you may not care much, but adding a suitable ORDER BY
would help push the planner towards using the right index.  In
this case "ORDER BY sname DESC, date DESC" would probably do the
trick.

			regards, tom lane