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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

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