Re: Nested loops overpriced

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