On 06/28/2005 01:40:56 AM, Tom Lane wrote:
"Karl O. Pinc" <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
