Subject: Re: multiple joins + Order by + LIMIT query performance
	issue
From: Shaun Thomas <sthomas@leapfrogonline.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: Antoine Baudoux <ab@taktik.be>, "pgsql-performance@postgresql.org"
	<pgsql-performance@postgresql.org>
In-Reply-To: <16494.1210096749@sss.pgh.pa.us>
References: <938EC908-FB9A-4BFA-950A-053BE3C87085@taktik.be>
	<1210092220.14833.33.camel@berners-lee>
	<16494.1210096749@sss.pgh.pa.us>
Content-Type: text/plain
Organization: Leapfrog Online
Date: Tue, 6 May 2008 13:14:29 -0500
Message-ID: <1210097669.14833.45.camel@berners-lee>
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit


On Tue, 2008-05-06 at 18:59 +0100, Tom Lane wrote:

> 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.

Oh, I know that.  I just noticed that when this happened to us, more
often than not, it was a reverse index scan that did it.  The thing that
annoyed me most was when it happened on an index that, even on a table
having 20M rows, the cardinality is < 10 on almost every value of that
index.  In our case, having a "LIMIT 1" was much worse than just getting
back 5 or 10 rows and throwing away everything after the first one.

> 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.

That's just it... it *can* be a big win.  But when it's a loss, you're
index-scanning a 20M+ row table for no reason.  We got around it,
obviously, but it was a definite surprise when a query that normally
runs in 0.5ms time randomly and inexplicably runs at 4-120s.  This is
disaster for a feed loader chewing through a few ten-thousand entries.

But that's just me grousing about not having query hints or being able
to tell Postgres to never, ever, ever index-scan certain tables. :)

-- 

Shaun Thomas
Database Administrator

Leapfrog Online 
807 Greenwood Street 
Evanston, IL 60201 
Tel. 847-440-8253
Fax. 847-570-5750
www.leapfrogonline.com