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

"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