Re: dynamic SQL - possible performance regression in 9.2

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Peter Eisentraut <peter_e(at)gmx(dot)net>
Cc: Heikki Linnakangas <hlinnakangas(at)vmware(dot)com>, 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: 2013-01-01 23:48:03
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

I wrote:
> I'm inclined to think that Heikki's patch doesn't go far enough, if we
> want to optimize behavior in this case. What we really want to happen
> is that parsing, planning, and execution all happen in the caller's
> memory context, with no copying of parse or plan trees at all - and we
> could do without overhead such as dependency extraction and invalidation
> checking, too. This would make SPI_execute a lot more comparable to the
> behavior of exec_simple_query().

Here's a draft patch for that. My initial hack at it had a
disadvantage, which was that because no invalidation checking happened,
a SPI_execute query string containing a DDL command (such as ALTER TABLE)
followed by a command affected by the DDL would fail to reparse/replan
the second command properly. (I suspect that Heikki's version had a
related defect, but haven't looked closely.) Now that's not a huge deal
IMO, because in many common cases parse analysis of the second command
would fail anyway. For instance, this has never worked in any PG

do $$ begin execute 'create table foo(f1 int); insert into foo values(1);'; end $$;

However it troubled me that there might be some regression there, and
after a bit of reflection I decided the right fix would be to rearrange
the code in spi.c so that parse analysis of later parsetrees follows
execution of earlier ones. This makes the behavior of SPI_execute()
even more like that of exec_simple_query(), and shouldn't cost anything
noticeable given the other changes here.

I'm not entirely sure about performance of this fix, though. I got
numbers varying between roughly-on-par with 9.1 and 10% slower than 9.1
for Pavel's example, depending on seemingly not-performance-related
rearrangements of the code in spi.c. I think this must be chance
effects of cache line alignment, but it would be good to hear what other
people get, both on Pavel's example and the other ones alluded to.
In any case this seems better than unmodified HEAD, which was 40% slower
than 9.1 for me.

regards, tom lane

Attachment Content-Type Size
oneshot-cached-plans.patch text/x-patch 33.0 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2013-01-02 00:15:44 Re: default SSL compression (was: libpq compression)
Previous Message Noah Misch 2013-01-01 19:00:55 Re: default SSL compression (was: libpq compression)