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

Re: [PERFORM] 8.2rc1 (much) slower than 8.2dev?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Arjen van der Meijden <acmmailing(at)tweakers(dot)net>
Cc: pgsql-patches(at)postgreSQL(dot)org
Subject: Re: [PERFORM] 8.2rc1 (much) slower than 8.2dev?
Date: 2006-12-14 21:10:03
Message-ID: 25060.1166130603@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-patchespgsql-performance
Arjen van der Meijden <acmmailing(at)tweakers(dot)net> writes:
>> The file is attached. (bz)Grepping for 'Was executed on 8.2 final slower 
>> than 8.2 dev' with -A1 will show you the timecomparisons, which you 
>> could than look up using your favorite editor.

I dug through this file, and it seems that all the cases where 8.2 final
is choosing a really markedly worse plan are instances of the same
query, for which 8.2 chose a nestloop plan with an inner indexscan on
a clustered index.  8.2 final is failing to choose that because in the
nestloop case it's not giving any cost credit for the index's
correlation.  Obviously a clustered index should have very high
correlation.  In the test case, half a dozen or so heap tuples need to
be fetched per index scan, and because of the correlation it's likely
they are all on the same heap page ... but the costing is assuming that
they are scattered randomly.  I had punted on this point back in June
because it seemed too complicated to handle in combination with the
cross-scan caching, but obviously we need to do something.  After
thinking a bit more, I propose the attached patch --- to be applied on
top of the other ones I sent you --- which seems to fix the problem
here.  Please give it a try.

			regards, tom lane

In response to

pgsql-performance by date

Next:From: Ron MayerDate: 2006-12-14 21:21:11
Subject: Re: File Systems Compared
Previous:From: Steven FlattDate: 2006-12-14 20:40:24
Subject: Re: Insertion to temp table deteriorating over time

pgsql-patches by date

Next:From: Peter EisentrautDate: 2006-12-14 22:02:05
Subject: Updated XML patch
Previous:From: Simon RiggsDate: 2006-12-14 12:04:10
Subject: pg_standby

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