From: Tom Lane <tgl@sss.pgh.pa.us>
To: Chris Shoemaker <chris.shoemaker@cox.net>
Cc: Shane Ambler <pgsql@Sheeky.Biz>
Cc: pgsql-performance@postgresql.org
Cc: ejones@engineyard.com
Subject: Re: Adding "LIMIT 1" kills performance.
Date: Thu, 29 May 2008 13:59:11 -0400
Message-ID: <1866.1212083951@sss.pgh.pa.us> (raw)
In-Reply-To: <20080529174712.GB18895@pe.Belkin>
References: <20080529154734.GA18273@pe.Belkin>
<483EDF9A.20100@Sheeky.Biz>
<20080529174712.GB18895@pe.Belkin>
Chris Shoemaker <chris.shoemaker@cox.net> writes:
> Still stumped,
The short answer here is that the planner is guessing that scanning the
index in ID order will come across the desired row (ie, the first one
matching the join condition) in less time than it will take to select
all the joinable rows, sort them by ID, and take the first one. It's
wrong in this case, but the plan is not unreasonable on its face.
The problem boils down to a misestimate of how many join rows there are.
You might get better results by increasing the statistics targets.
There are plenty of similar cases in the list archives.
regards, tom lane
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: tgl@sss.pgh.pa.us, chris.shoemaker@cox.net, pgsql@Sheeky.Biz, ejones@engineyard.com
Subject: Re: Adding "LIMIT 1" kills performance.
In-Reply-To: <1866.1212083951@sss.pgh.pa.us>
* 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