From: Tom Lane <tgl@sss.pgh.pa.us>
To: Karl O. Pinc <kop@meme.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Poor index choice -- multiple indexes of the same columns
Date: Tue, 28 Jun 2005 02:40:56 -0400
Message-ID: <11497.1119940856@sss.pgh.pa.us> (raw)
In-Reply-To: <1119913766l.4428l.2l@mofo>
References: <1119913766l.4428l.2l@mofo>
"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
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-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, kop@meme.com
Subject: Re: Poor index choice -- multiple indexes of the same columns
In-Reply-To: <11497.1119940856@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