From: Shaun Thomas <sthomas@leapfrogonline.com>
To: Antoine Baudoux <ab@taktik.be>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: multiple joins + Order by + LIMIT query performance issue
Date: Tue, 6 May 2008 13:24:33 -0500
Message-ID: <1210098273.14833.55.camel@berners-lee> (raw)
In-Reply-To: <ED75A52A-2CBC-4EA3-AE1A-E344B08A9682@taktik.be>
References: <938EC908-FB9A-4BFA-950A-053BE3C87085@taktik.be>
<1210092220.14833.33.camel@berners-lee>
<ED75A52A-2CBC-4EA3-AE1A-E344B08A9682@taktik.be>
On Tue, 2008-05-06 at 18:24 +0100, Antoine Baudoux wrote:
> Isnt the planner fooled by the index on the sorting column?
> If I remove the index the query runs OK.
In your case, for whatever reason, the stats say doing the index scan on
the sorted column will give you the results faster. That isn't always
the case, and sometimes you can give the same query different where
clauses and that same slow-index-scan will randomly be fast. It's all
based on the index distribution and the particular values being fetched.
This goes back to what Tom said. If you know a "miss" can result in
terrible performance, it's best to just recode the query to avoid the
situation.
> This is crazy, so simply by adding a LIMIT to a query, the planning is
> changed in a very bad way. Does the planner use the LIMIT as a sort of
> hint?
Yes. That's actually what tells it the index scan can be a "big win."
If it scans the index backwards on values returned from some of your
joins, it may just have to find 25 rows and then it can immediately stop
scanning and just give you the results. In normal cases, this is a
massive performance boost when you have an order clause and are
expecting a ton of results, (say you're getting the first 25 rows of
10000 or something). But if it would be faster to generate the results
and *then* sort, but Postgres thinks otherwise, you're pretty much
screwed.
But that's the long answer. You have like 3 ways to get around this
now, so pick one. ;)
--
Shaun Thomas
Database Administrator
Leapfrog Online
807 Greenwood Street
Evanston, IL 60201
Tel. 847-440-8253
Fax. 847-570-5750
www.leapfrogonline.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sthomas@leapfrogonline.com, ab@taktik.be
Subject: Re: multiple joins + Order by + LIMIT query performance issue
In-Reply-To: <1210098273.14833.55.camel@berners-lee>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox