Re: dynamic SQL - possible performance regression in 9.2

I did three back-to-back runs using the same settings as in
http://archives.postgresql.org/pgsql-performance/2012-11/msg00007.php
Except:
- use no prepared statement
- use 40 db connections
- build source from postgresql.git on the server box using: REL9_1_7,
REL9_2_2, REL9_2_2 + this patch

NOTPM results:
REL9_1_7: 46512.66
REL9_2_2: 42828.66
REL9_2_2 + this patch: 46973.70

Thanks,
Dong

PS, the top 20 lines of oprofile of these runs attached.


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, January 01, 2013 6:48 PM
To: Peter Eisentraut
Cc: Heikki Linnakangas; Pavel Stehule; PostgreSQL Hackers; Dong Ye
Subject: Re: [HACKERS] dynamic SQL - possible performance regression in
9.2

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

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