Skip site navigation (1) Skip section navigation (2)

Re: Function scan/Index scan to nested loop

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 (view raw or flat)
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.

In response to

Responses

pgsql-performance by date

Next:From: Craig RingerDate: 2010-05-11 07:14:08
Subject: Re: Function scan/Index scan to nested loop
Previous:From: Carlo StonebanksDate: 2010-05-11 05:32:28
Subject: Function scan/Index scan to nested loop

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group