Re: Need advice to avoid ORDER BY

Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> problem is that you are looking for needles (valids = 0) in the
> haystack. the problem wasn't really the order, but the fact that you
> had to scan an arbitrary amount of rows before finding a candidate
> record. so the partial index manages this problem by creating index
> entries *only for records that match a criteria*, and the planner
> recognizes this and prefers that index when the criteria is also
> present in the query. In other words, index only the needles.

The other way to fix it is a two-column index on (valids, id), which
will be more useful if sometimes you need the minimum/maximum id
for some nonzero value of valids.

The real point here is that you want the index to contain consecutive
entries for the rows with the particular valids value you want, *in
order by id*. Then the planner knows the first/last such index entry
contains the answer. When you index only valids, it has to collect all
the matching rows and sort them by id.

regards, tom lane