Hello 2012/12/28 Heikki Linnakangas <hlinnakan...@vmware.com>: > On 28.12.2012 23:53, Peter Eisentraut wrote: >> >> On 12/27/12 1:07 AM, Pavel Stehule wrote: >>> >>> Hello >>> >>> I rechecked performance of dynamic SQL and it is significantly slower >>> in 9.2 than 9.1 >>> >>> -- 9.1 >>> postgres=# create or replace function test() returns void as $$ begin >>> for i in 1..1000000 loop execute 'select 1'; end loop; end $$ language >>> plpgsql; >> >> >> I think this is the same as the case discussed at >> <CAD4+=qWnGU0qi+iq=eph6egpuunscysgdtgkazizevrggjo...@mail.gmail.com>. > > > Yeah, probably so. > > As it happens, I just spent a lot of time today narrowing down yet another > report of a regression in 9.2, when running DBT-2: > http://archives.postgresql.org/pgsql-performance/2012-11/msg00007.php. It > looks like that is also caused by the plancache changes. DBT-2 implements > the transactions using C functions, which use SPI_execute() to run all the > queries. > > It looks like the regression is caused by extra copying of the parse tree > and plan trees. Node-copy-related functions like AllocSetAlloc and _copy* > are high in the profile, They are also high in the 9.1 profile, but even > more so in 9.2. > > I hacked together a quick&dirty patch to reduce the copying of single-shot > plans, and was able to buy back much of the regression I was seeing on > DBT-2. Patch attached. But of course, DBT-2 really should be preparing the > queries once with SPI_prepare, and reusing them thereafter. >
performance regression is about 30-50%. You copy_reduce_patch increase speed about 8% Regards Pavel > - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers