Re: dynamic SQL - possible performance regression in 9.2

On Friday, December 28, 2012, Heikki Linnakangas wrote:

> 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<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.

The plancache change slowed down a dynamic sql partitioning trigger about
26%, and your patch redeems about 1/2 of that cost.

Using a RULE-based partitioning instead with row by row insertion, the
plancache changes slowed it down by 300%, and this patch doesn't change
that. But that seems to be down to the insertion getting planned
repeatedly, because it decides the custom plan is cheaper than the generic
plan. Whatever savings the custom plan may have are clearly less than the
cost of doing the planning repeatedly.

Cheers,

Jeff