Re: dynamic SQL - possible performance regression in 9.2

From: Heikki Linnakangas <hlinnakangas(at)vmware(dot)com>
To: Peter Eisentraut <peter_e(at)gmx(dot)net>
Cc: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>, Dong Ye <yed(at)vmware(dot)com>
Subject: Re: dynamic SQL - possible performance regression in 9.2
Date: 2012-12-28 22:11:28
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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

- Heikki

Attachment Content-Type Size
reduce-copies-in-spi-execute-2.patch text/x-diff 10.1 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message James Cloos 2012-12-28 23:04:52 ILIKE vs indices
Previous Message Peter Eisentraut 2012-12-28 21:53:59 Re: dynamic SQL - possible performance regression in 9.2