From: Karl O. Pinc <kop@meme.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Poor index choice -- multiple indexes of the same
Date: Tue, 28 Jun 2005 17:16:41 +0000
Message-ID: <1119979001l.20199l.7l@mofo> (raw)
In-Reply-To: <11497.1119940856@sss.pgh.pa.us>
References: <1119913766l.4428l.2l@mofo>
<11497.1119940856@sss.pgh.pa.us>
On 06/28/2005 01:40:56 AM, Tom Lane wrote:
> "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.
Yes, that works. I'd already tried "ORDER BY date DESC", before
I first wrote, and that did not work. (I started with no LIMIT
either, and tried adding specifications until I gave up. It's
very good that the new planner will figure out things by itself.)
"ORDER BY sname DESC" works as well. This is a
bit odd, as with the constant in the = comparison "ORDER BY date
DESC" is the same as "ORDER BY sname DESC, date DESC".
I guess that's why I gave up on my attempts to get the planner
to use the (sname, date) index before I got to your solution.
Thanks everybody for the help.
Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
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: kop@meme.com, tgl@sss.pgh.pa.us
Subject: Re: Poor index choice -- multiple indexes of the same
In-Reply-To: <1119979001l.20199l.7l@mofo>
* 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