From: Shaun Thomas <sthomas@leapfrogonline.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: 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:14:29 -0500
Message-ID: <1210097669.14833.45.camel@berners-lee> (raw)
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>
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
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, tgl@sss.pgh.pa.us, ab@taktik.be
Subject: Re: multiple joins + Order by + LIMIT query performance issue
In-Reply-To: <1210097669.14833.45.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