Re: Nested loops overpriced

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(at)gmx(dot)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