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

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Karl O(dot) Pinc" <kop(at)meme(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Poor index choice -- multiple indexes of the same columns
Date: 2005-06-28 06:40:56
Message-ID: 11497.1119940856@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
"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.

			regards, tom lane

In response to

Responses

pgsql-performance by date

Next:From: Klint GoreDate: 2005-06-28 08:36:35
Subject: Re: How can I speed up this function?
Previous:From: John A MeinelDate: 2005-06-28 06:35:07
Subject: Re: Too slow querying a table of 15 million records

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