From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ian Westmacott <ianw@intellivid.com>
Cc: pgsql-performance@postgresql.org
Cc: pgsql-hackers@postgresql.org
Subject: Re: Planner doesn't look at LIMIT?
Date: Wed, 10 Aug 2005 18:55:24 -0400
Message-ID: <16040.1123714524@sss.pgh.pa.us> (raw)
In-Reply-To: <1123707833.17725.133.camel@spectre.intellivid.com>
References: <758d5e7f0507220210bfa4978@mail.gmail.com>
<29473.1122043197@sss.pgh.pa.us>
<6440.1122049220@sss.pgh.pa.us>
<1123707833.17725.133.camel@spectre.intellivid.com>
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.
If this wasn't chance, but was expected because there are many matching
rows and not only one, then there's a statistical problem.
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, ianw@intellivid.com, pgsql-hackers@postgresql.org
Subject: Re: Planner doesn't look at LIMIT?
In-Reply-To: <16040.1123714524@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