From: | Peter Eisentraut <peter_e(at)gmx(dot)net> |
---|---|
To: | Heikki Linnakangas <hlinnakangas(at)vmware(dot)com> |
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-31 21:35:51 |
Message-ID: | 50E20537.80406@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 12/28/12 5:11 PM, Heikki Linnakangas wrote:
>
> 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.
I was recently profiling an application that uses a fair amount of
PL/pgSQL with dynamic queries and also noticed AllocSetAlloc high in the
profile. I was getting suspicious now and compared 9.1 and 9.2
performance: 9.2 is consistently about 3% slower. Your patch doesn't
seem to have a measurable effect, but it might be if I ran the test for
longer.
From | Date | Subject | |
---|---|---|---|
Next Message | Noah Misch | 2013-01-01 02:54:21 | Re: Visual Studio 2012 RC |
Previous Message | Alvaro Herrera | 2012-12-31 21:06:39 | Re: fix bgworkers in EXEC_BACKEND |