Re: Need advice to avoid ORDER BY

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Condor <condor(at)stz-bg(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Need advice to avoid ORDER BY
Date: 2013-04-04 22:54:42
Message-ID: CAHyXU0yzU9S+oSmYXxzBPDg4+yy9-H5gm8721bvH7g0E9m=QzQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Noel 2013-04-04 23:17:37 Re: PostgreSQL: CPU utilization creeping to 100%
Previous Message Tom Lane 2013-04-04 22:15:14 Re: Need advice to avoid ORDER BY