"Karl O. Pinc" <kop@meme.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
