Re: multiple joins + Order by + LIMIT query performance issue

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Shaun Thomas <sthomas(at)leapfrogonline(dot)com>
Cc: Antoine Baudoux <ab(at)taktik(dot)be>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: multiple joins + Order by + LIMIT query performance issue
Date: 2008-05-06 17:59:09
Message-ID: 16494.1210096749@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Shaun Thomas <sthomas(at)leapfrogonline(dot)com> writes:
> I'm not sure what causes this, but the problem with indexes is that
> they're not necessarily in the order you want unless you also cluster
> them, so a backwards index scan is almost always the wrong answer.

Whether the scan is forwards or backwards has nothing to do with it.
The planner is using the index ordering to avoid having to do a
full-table scan and sort. It's essentially betting that it will find
25 (or whatever your LIMIT is) rows that satisfy the other query
conditions soon enough in the index scan to make this faster than the
full-scan approach. If there are a lot fewer matching rows than it
expects, or if the target rows aren't uniformly scattered in the index
ordering, then this way can be a loss; but when it's a win it can be
a big win, too, so "it's a bug take it out" is an unhelpful opinion.

If a misestimate of this kind is bugging you enough that you're willing
to change the query, I think you can fix it like this:

select ... from foo order by x limit n;
=>
select ... from (select ... from foo order by x) ss limit n;

The subselect will be planned without awareness of the LIMIT, so you
should get a plan using a sort rather than one that bets on the LIMIT
being reached quickly.

regards, tom lane

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Heikki Linnakangas 2008-05-06 18:04:17 Re: multiple joins + Order by + LIMIT query performance issue
Previous Message Justin 2008-05-06 17:41:55 Re: need to speed up query