From: | Ian Westmacott <ianw(at)intellivid(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Planner doesn't look at LIMIT? |
Date: | 2005-08-11 13:26:37 |
Message-ID: | 1123766797.21162.93.camel@spectre.intellivid.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-performance |
On Wed, 2005-08-10 at 18:55, Tom Lane wrote:
> Ian Westmacott <ianw(at)intellivid(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Matt Miller | 2005-08-11 14:21:19 | Re: [GENERAL] Testing of MVCC |
Previous Message | Soeren Laursen | 2005-08-11 08:23:51 | Re: Use of inv_getsize in functions |
From | Date | Subject | |
---|---|---|---|
Next Message | Luis Cornide Arce | 2005-08-11 13:59:31 | Re: Why is not using the index |
Previous Message | Stéphane COEZ | 2005-08-11 13:19:06 | Performance pb vs SQLServer. |