From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | Carlo Stonebanks <stonec(dot)register(at)sympatico(dot)ca> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Function scan/Index scan to nested loop |
Date: | 2010-05-11 07:07:26 |
Message-ID: | AANLkTineh1Ibv34hj_Q4T1Gd5mp-MLPCqmEPNzn1QdLS@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Mon, May 10, 2010 at 11:32 PM, Carlo Stonebanks
<stonec(dot)register(at)sympatico(dot)ca> wrote:
> Hello all,
>
> A query ran twice in succession performs VERY poorly the first time as it
> iterates through the nested loop. The second time, it rips. Please see SQL,
> SLOW PLAN and FAST PLAN below.
This is almost always due to caching. First time the data aren't in
the cache, second time they are.
> I don't know why these nested loops are taking so long to execute.
> " -> Nested Loop (cost=0.00..42866.98 rows=77 width=18) (actual
> time=126.354..26301.027 rows=9613 loops=1)"
> " -> Nested Loop (cost=0.00..42150.37 rows=122 width=18) (actual
> time=117.369..15349.533 rows=13247 loops=1)"
Your row estimates are WAY off. A nested loop might now be the best choice.
Also note that some platforms add a lot of time to some parts of an
explain analyze due to slow time function response. Compare the run
time of the first run with and without explain analyze.
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2010-05-11 07:14:08 | Re: Function scan/Index scan to nested loop |
Previous Message | Carlo Stonebanks | 2010-05-11 05:32:28 | Function scan/Index scan to nested loop |