Re: Function scan/Index scan to nested loop

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.