From: Ian Westmacott <ianw@intellivid.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Planner doesn't look at LIMIT?
Date: Thu, 11 Aug 2005 09:26:37 -0400
Message-ID: <1123766797.21162.93.camel@spectre.intellivid.com> (raw)
In-Reply-To: <16040.1123714524@sss.pgh.pa.us>
References: <758d5e7f0507220210bfa4978@mail.gmail.com>
<29473.1122043197@sss.pgh.pa.us>
<6440.1122049220@sss.pgh.pa.us>
<1123707833.17725.133.camel@spectre.intellivid.com>
<16040.1123714524@sss.pgh.pa.us>
On Wed, 2005-08-10 at 18:55, Tom Lane wrote:
> Ian Westmacott <ianw@intellivid.com> writes:
> > In a nutshell, I have a LIMIT query where the planner
> > seems to favor a merge join over a nested loop.
>
> The planner is already estimating only one row out of the join, and so
> the LIMIT doesn't affect its cost estimates at all.
>
> It appears to me that the reason the nestloop plan is fast is just
> chance: a suitable matching row is found very early in the scan of
> tableB, so that the indexscan on it can stop after 29 rows, instead
> of having to go through all 55000 rows in the given range of bim.
> If it'd have had to go through, say, half of the rows to find a match,
> the sort/merge plan would show up a lot better.
Oh, I see. Thanks, that clears up some misconceptions I
had about the explain output.
> If this wasn't chance, but was expected because there are many matching
> rows and not only one, then there's a statistical problem.
Well, there are in fact almost 300 of them in this case.
So I guess what I need to do is give the planner more
information to correctly predict that.
Thanks,
--Ian
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: ianw@intellivid.com, tgl@sss.pgh.pa.us
Subject: Re: Planner doesn't look at LIMIT?
In-Reply-To: <1123766797.21162.93.camel@spectre.intellivid.com>
* 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