From: Daniel Cristian Cruz <danielcristian@gmail.com>
To: Gregory Stark <stark@enterprisedb.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Nested loops overpriced
Date: Wed, 9 May 2007 11:51:58 -0300
Message-ID: <48d0cacb0705090751s30b7da17yed64b47ab29c61c7@mail.gmail.com> (raw)
In-Reply-To: <87sla6gj6v.fsf@oxford.xeocode.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: danielcristian@gmail.com, stark@enterprisedb.com
Subject: Re: Nested loops overpriced
In-Reply-To: <48d0cacb0705090751s30b7da17yed64b47ab29c61c7@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox