Re: Need advice to avoid ORDER BY

On Thu, Apr 4, 2013 at 4:49 PM, Condor <condor(at)stz-bg(dot)com> wrote:
>> SELECT jobid FROM mytable WHERE valids = 0 ORDER BY id ASC LIMIT 1;
>>
>> should return in zero time since btree indexes can optimize order by
>> expressions and the partial index will bypass having to wade through
>> the rows you don't want.
>>
>> merlin
>
>
>
> Hm,
> I only can say: Thank You!
> Your solution is work, but Im now a little confused. I has a index
> CREATE INDEX ON mytable (valids) USING BTREE (valids) and the
> query to find valids = 0 tooks 137 ms.

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.

merlin