Re: Nested loops overpriced

2007/5/9, Gregory Stark <stark(at)enterprisedb(dot)com>:
>
> "Daniel Cristian Cruz" <danielcristian(at)gmail(dot)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