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

"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