Re: Need advice to avoid ORDER BY

On 2013-04-05 01:54, Merlin Moncure wrote:
> On Thu, Apr 4, 2013 at 5:15 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 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.
>
> right -- that's a more general solution -- here we are exploiting that
> A: the OP only needs access to "=0" rows and especially B: "=0" rows
> are a tiny fraction of the overall set (we know this because otherwise
> the query would have returned quickly anyways). So we get to squeak
> out with a tiny index pointing to only the candidate rows.
>
> Partial indexes are an underutilized trick -- the efficiency savings
> can be enormous. They are often useful when coding ad hoc queue
> operations in the database where the queued items are intermixed with
> items that have been resolved.
>
> merlin

Thank you for every one for suggestions. I'll try to make
changes tomorrow night to see what will be happened.

Cheers,
Condor