Re: where+orderby+limit not (always) using appropriate index? - Mailing list pgsql-performance

=?ISO-8859-2?Q?Sz=FBcs_G=E1bor?= <surrano@gmail.com> writes:
> Create a table with (at least) two fields, say i and o.
> Create three indexes on (i), (o), (i,o)
> Insert enough rows to test.
> Try to replace min/max aggregates with indexable queries such as:

> SELECT o FROM t WHERE i = 1 ORDER BY o LIMIT 1;

> Problem #1: This tends to use one of the single-column indexes (depending on
> the frequency of the indexed element), not the two-column index. Also, I'm
> not perfectly sure but maybe the planner is right. Why?

To get the planner to use the double-column index, you have to use an
ORDER BY that matches the index, eg

    SELECT o FROM t WHERE i = 1 ORDER BY i,o LIMIT 1;

            regards, tom lane