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
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 |