Re: dynamic SQL - possible performance regression in 9.2

Hello

2012/12/28 Heikki Linnakangas <hlinnakangas(at)vmware(dot)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=EPh6EGPuUnSCYsGDTgKazizEvrGgjo0Sg(at)mail(dot)gmail(dot)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