On Mon, May 10, 2010 at 11:32 PM, Carlo Stonebanks
<stonec.register@sympatico.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.