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