From: Daniel Cristian Cruz <danielcristian@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Nested loops overpriced
Date: Wed, 9 May 2007 10:38:56 -0300
Message-ID: <48d0cacb0705090638p54f7ccbcg90270b889c8900a1@mail.gmail.com> (raw)
In-Reply-To: <200705091358.25748.peter_e@gmx.net>
References: <200705081722.16731.peter_e@gmx.net>
<1987.1178639593@sss.pgh.pa.us>
<200705091358.25748.peter_e@gmx.net>
I'm having something weird too...
Look:
Nested Loop Left Join (cost=93.38..7276.26 rows=93 width=58) (actual
time=99.211..4804.525 rows=2108 loops=1)
-> Hash Join (cost=93.38..3748.18 rows=93 width=4) (actual
time=0.686..20.632 rows=45 loops=1)
Hash Cond: ((u.i)::text = (m.i)::text)
-> Seq Scan on u (cost=0.00..2838.80 rows=10289 width=4)
(actual time=0.010..7.813 rows=10291 loops=1)
-> Hash (cost=87.30..87.30 rows=30 width=7) (actual
time=0.445..0.445 rows=45 loops=1)
-> Index Scan using m_pkey on m (cost=0.00..87.30
rows=30 width=7) (actual time=0.046..0.371 rows=45 loops=1)
Index Cond: (t = 1613)
Filter: ((a)::text = 'Y'::text)
-> Index Scan using s_pkey on s (cost=0.00..37.33 rows=3
width=58) (actual time=19.864..106.198 rows=47 loops=45)
Index Cond: ((u.i = s.u) AND ((s.p)::text = '4'::text) AND
(s.t = 1613) AND ((s.c)::text = 'cmi.core.total_time'::text))
Total runtime: 4805.975 ms
And disabling all the joins Tom said:
Nested Loop Left Join (cost=0.00..16117.12 rows=93 width=58) (actual
time=2.706..168.556 rows=2799 loops=1)
-> 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)
Index Cond: ((m.t = 1615) AND ((u.i)::text = (m.i)::text))
Filter: ((a)::text = 'Y'::text)
-> Index Scan using s_pkey on s (cost=0.00..31.09 rows=2
width=58) (actual time=0.047..0.778 rows=56 loops=50)
Index Cond: ((u.i = s.u) AND ((s.p)::text = '4'::text) AND
(s.t = 1615) AND ((s.c)::text = 'cmi.core.total_time'::text))
Total runtime: 169.836 ms
I had PostgreSQL 8.2.3 on x86_64-redhat-linux-gnu, shared_buffers with
1640MB, effective_cache_size with 5400MB and 8GB of RAM, where all
shared_buffers blocks are used (pg_buffercache, relfilenode IS NOT
NULL).
Note that even when I set default_statistics_target to 500, and
calling "ANALYZE s;", I cannot see the number of estimated rows on the
index scan on s close to the actual rows.
Could it be related?
2007/5/9, Peter Eisentraut <peter_e@gmx.net>:
> Am Dienstag, 8. Mai 2007 17:53 schrieb Tom Lane:
> > Hmm, I'd have expected it to discount the repeated indexscans a lot more
> > than it seems to be doing for you. As an example in the regression
> > database, note what happens to the inner indexscan cost estimate when
> > the number of outer tuples grows:
>
> I can reproduce your results in the regression test database. 8.2.1 and 8.2.4
> behave the same.
>
> I checked the code around cost_index(), and the assumptions appear to be
> correct (at least this query doesn't produce wildly unusual data).
> Apparently, however, the caching effects are much more significant than the
> model takes into account.
>
> --
> Peter Eisentraut
> http://developer.postgresql.org/~petere/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>
--
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
Subject: Re: Nested loops overpriced
In-Reply-To: <48d0cacb0705090638p54f7ccbcg90270b889c8900a1@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