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