Gerhard Wiesinger <lists@xxxxxxxxxxxxx> writes: > Back to the original problem: Finally ;-) > 8.3 query plans: http://www.wiesinger.com/tmp/pg_perf_83_new.txt > 8.4 quey plans: http://www.wiesinger.com/tmp/pg_perf_84.txt Hmm. The 8.3 plan is indeed assuming that the number of rows will stay constant as we bubble up through the join levels, but AFAICS this is simply wrong: -> Nested Loop Left Join (cost=0.00..38028.89 rows=67 width=8) -> Nested Loop Left Join (cost=0.00..25399.46 rows=67 width=8) -> Nested Loop Left Join (cost=0.00..12770.04 rows=67 width=8) -> Index Scan using i_log_unique on log l (cost=0.00..140.61 rows=67 width=8) Index Cond: (datetime >= (now() - '00:01:00'::interval)) -> Index Scan using unique_key_and_id on log_details d7 (cost=0.00..187.39 rows=89 width=8) Index Cond: ((l.id = d7.fk_id) AND (d7.fk_keyid = $6)) -> Index Scan using unique_key_and_id on log_details d6 (cost=0.00..187.39 rows=89 width=8) Index Cond: ((l.id = d6.fk_id) AND (d6.fk_keyid = $5)) -> Index Scan using unique_key_and_id on log_details d5 (cost=0.00..187.39 rows=89 width=8) Index Cond: ((l.id = d5.fk_id) AND (d5.fk_keyid = $4)) If the log_details indexscans are expected to produce 89 rows per execution, then surely the join size should go up 89x at each level, because the join steps themselves don't eliminate anything. In 8.4 the arithmetic is at least self-consistent: -> Nested Loop Left Join (cost=0.00..505256.95 rows=57630 width=8) -> Nested Loop Left Join (cost=0.00..294671.96 rows=6059 width=8) -> Nested Loop Left Join (cost=0.00..272532.55 rows=637 width=8) -> Index Scan using log_pkey on log l (cost=0.00..270203.92 rows=67 width=8) Filter: (datetime >= (now() - '00:01:00'::interval)) -> Index Scan using unique_key_and_id on log_details d7 (cost=0.00..34.63 rows=10 width=8) Index Cond: ((l.id = d7.fk_id) AND (d7.fk_keyid = $6)) -> Index Scan using unique_key_and_id on log_details d6 (cost=0.00..34.63 rows=10 width=8) Index Cond: ((l.id = d6.fk_id) AND (d6.fk_keyid = $5)) -> Index Scan using unique_key_and_id on log_details d5 (cost=0.00..34.63 rows=10 width=8) Index Cond: ((l.id = d5.fk_id) AND (d5.fk_keyid = $4)) The rowcount estimates are apparently a shade less than 10, but they get rounded off in the display. I believe the reason for this change is that 8.4's join estimation code was rewritten so that it wasn't completely bogus for outer joins. 8.3 might have been getting the right answer, but it was for the wrong reasons. So the real question to be answered here is why doesn't it think that each of the unique_key_and_id indexscans produce just a single row, as you indicated was the case. The 8.4 estimate is already a factor of almost 10 closer to reality than 8.3's, but you need another factor of 10. You might find that increasing the statistics target for the log_details table helps. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
