Skip site navigation (1) Skip section navigation (2)

Re: Major performance problem after upgrade from 8.3 to 8.4

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Gerhard Wiesinger <lists(at)wiesinger(dot)com>
Cc: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Andreas Kretschmer <akretschmer(at)spamfence(dot)net>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Major performance problem after upgrade from 8.3 to 8.4
Date: 2010-09-03 21:10:50
Message-ID: 29799.1283548250@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Gerhard Wiesinger <lists(at)wiesinger(dot)com> 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

In response to

Responses

pgsql-performance by date

Next:From: Gerhard WiesingerDate: 2010-09-04 06:58:30
Subject: Re: Major performance problem after upgrade from 8.3 to 8.4
Previous:From: Gerhard WiesingerDate: 2010-09-03 19:34:12
Subject: Re: Major performance problem after upgrade from 8.3 to 8.4

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group