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@xxxxxxx>:
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
