Skip site navigation (1) Skip section navigation (2)

Re: Poor index choice -- multiple indexes of the same

From: "Karl O(dot) Pinc" <kop(at)meme(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Poor index choice -- multiple indexes of the same
Date: 2005-06-28 17:16:41
Message-ID: 1119979001l.20199l.7l@mofo (view raw or flat)
Thread:
Lists: pgsql-performance
On 06/28/2005 01:40:56 AM, Tom Lane wrote:
> "Karl O. Pinc" <kop(at)meme(dot)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(at)meme(dot)com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein


In response to

pgsql-performance by date

Next:From: Michael StoneDate: 2005-06-28 17:27:01
Subject: Re: read block size
Previous:From: PFCDate: 2005-06-28 17:10:05
Subject: Re: Too slow querying a table of 15 million records

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group