DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=sk7swjv6EovPOmyUEiBGDt00D5YK3u504miLDISqhMLQ+VdHRi5iuMFkuvG3bkKOtKPs22RBhmJjMa7OUNRXLNrZciI1WJvTH1agGBvG2hd7wlMx+x/1FyExfpLb7k5FT5YeShmdImz6i3rEmw+LKN6darDSAOTaXWdL35PwRM0=
Message-ID: <48d0cacb0705090751s30b7da17yed64b47ab29c61c7@mail.gmail.com>
Date: Wed, 9 May 2007 11:51:58 -0300
From: "Daniel Cristian Cruz" <danielcristian@gmail.com>
To: "Gregory Stark" <stark@enterprisedb.com>
Subject: Re: Nested loops overpriced
Cc: pgsql-performance@postgresql.org
In-Reply-To: <87sla6gj6v.fsf@oxford.xeocode.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <200705081722.16731.peter_e@gmx.net>
	<1987.1178639593@sss.pgh.pa.us> <200705091358.25748.peter_e@gmx.net>
	<48d0cacb0705090638p54f7ccbcg90270b889c8900a1@mail.gmail.com>
	<87sla6gj6v.fsf@oxford.xeocode.com>

2007/5/9, Gregory Stark <stark@enterprisedb.com>:
>
> "Daniel Cristian Cruz" <danielcristian@gmail.com> writes:
>
> >   ->  Nested Loop  (cost=0.00..13187.94 rows=93 width=4) (actual time=2.622..125.739 rows=50 loops=1)
> >         ->  Seq Scan on u  (cost=0.00..2838.80 rows=10289 width=4) (actual time=0.012..9.863 rows=10291 loops=1)
> >         ->  Index Scan using m_pkey on m  (cost=0.00..0.80 rows=1 width=7) (actual time=0.009..0.009 rows=0 loops=10291)
>
> That's not discounting the nested loop for cache effect at all!
>
> What is your effective_cache_size for this?

effective_cache_size is 5400MB.

I forgot to mention a modifications on cost:
cpu_tuple_cost = 0.2
Which forced a usage of indexes.

I set it to 0.01 and the plan has a index scan on m before the hash on
u, being 15% slower:

         Hash Cond: ((u.i)::text = (m.i)::text)
         ->  Seq Scan on u  (cost=0.00..2838.80 rows=10289 width=4)
(actual time=0.007..6.138 rows=10292 loops=1)
         ->  Hash  (cost=87.30..87.30 rows=30 width=7) (actual
time=0.185..0.185 rows=50 loops=1)
               ->  Index Scan using m_pkey on m  (cost=0.00..87.30
rows=30 width=7) (actual time=0.021..0.144 rows=50 loops=1)
                     Index Cond: (t = 1615)
                     Filter: ((a)::text = 'Y'::text)

I'm still confused since I didn't understood what "That's not
discounting the nested loop for cache effect at all!" could mean...

Thanks for the help.
-- 
Daniel Cristian Cruz