Re: Nested loops overpriced - Mailing list pgsql-performance
From | Daniel Cristian Cruz |
---|---|
Subject | Re: Nested loops overpriced |
Date | |
Msg-id | 48d0cacb0705090638p54f7ccbcg90270b889c8900a1@mail.gmail.com Whole thread Raw |
In response to | Re: Nested loops overpriced (Peter Eisentraut <peter_e@gmx.net>) |
Responses |
Re: Nested loops overpriced
|
List | pgsql-performance |
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
pgsql-performance by date: