Re: [HACKERS] dynamic SQL - possible performance regression in 9.2

2013-01-09 Thread Tom Lane
Jeff Janes jeff.ja...@gmail.com writes: On Wednesday, January 2, 2013, Tom Lane wrote: That scenario doesn't sound like it has anything to do with the one being discussed in this thread. But what do you mean by rule-based partitioning exactly? A rule per se wouldn't result in a cached plan

[HACKERS] dynamic SQL - possible performance regression in 9.2

2013-01-05 Thread Jeff Janes
On Wednesday, January 2, 2013, Tom Lane wrote: Jeff Janes jeff.ja...@gmail.com writes: 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

Re: [HACKERS] dynamic SQL - possible performance regression in 9.2

2013-01-04 Thread Dong Ye
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

Re: [HACKERS] dynamic SQL - possible performance regression in 9.2

2013-01-04 Thread Tom Lane
Dong Ye y...@vmware.com writes: I did three back-to-back runs using the same settings as in http://archives.postgresql.org/pgsql-performance/2012-11/msg7.php Except: - use no prepared statement - use 40 db connections - build source from postgresql.git on the server box using: REL9_1_7,

Re: [HACKERS] dynamic SQL - possible performance regression in 9.2

2013-01-04 Thread Pavel Stehule
2013/1/4 Tom Lane t...@sss.pgh.pa.us: Dong Ye y...@vmware.com writes: I did three back-to-back runs using the same settings as in http://archives.postgresql.org/pgsql-performance/2012-11/msg7.php Except: - use no prepared statement - use 40 db connections - build source from

Re: [HACKERS] dynamic SQL - possible performance regression in 9.2

2013-01-04 Thread Josh Berkus
Next question is what people think about back-patching into 9.2 so as to eliminate the performance regression vs 9.1. I believe this would be safe (although some care would have to be taken to put the added boolean fields into places where they'd not result in an ABI break). However it may

Re: [HACKERS] dynamic SQL - possible performance regression in 9.2

2013-01-04 Thread Heikki Linnakangas
On 04.01.2013 22:05, Josh Berkus wrote: Next question is what people think about back-patching into 9.2 so as to eliminate the performance regression vs 9.1. I believe this would be safe (although some care would have to be taken to put the added boolean fields into places where they'd not

Re: [HACKERS] dynamic SQL - possible performance regression in 9.2

2013-01-04 Thread Joshua D. Drake
On 01/04/2013 12:05 PM, Josh Berkus wrote: Next question is what people think about back-patching into 9.2 so as to eliminate the performance regression vs 9.1. I believe this would be safe (although some care would have to be taken to put the added boolean fields into places where they'd

Re: [HACKERS] dynamic SQL - possible performance regression in 9.2

2013-01-04 Thread Tom Lane
Josh Berkus j...@agliodbs.com writes: Next question is what people think about back-patching into 9.2 so as to eliminate the performance regression vs 9.1. 8% is a pretty serious regression, for those of us with applications which do a lot of dynamic SQL. As a reminder, many people do

Re: [HACKERS] dynamic SQL - possible performance regression in 9.2

2013-01-04 Thread Joshua D. Drake
On 01/04/2013 01:17 PM, Tom Lane wrote: Well, of course, people with that type of problem should probably rethink their use of dynamic SQL when they move to 9.2 anyway, because that's the case where the new plancache code could actually help them if they'd only let it. Not to further the

Re: [HACKERS] dynamic SQL - possible performance regression in 9.2

2013-01-04 Thread Josh Berkus
Well, of course, people with that type of problem should probably rethink their use of dynamic SQL when they move to 9.2 anyway, because that's the case where the new plancache code could actually help them if they'd only let it. Oh, no question. But it'll take time for users with 1000's of

Re: [HACKERS] dynamic SQL - possible performance regression in 9.2

2013-01-03 Thread Peter Eisentraut
On 1/1/13 6:48 PM, Tom Lane wrote: 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

Re: [HACKERS] dynamic SQL - possible performance regression in 9.2

2013-01-03 Thread Tom Lane
Peter Eisentraut pete...@gmx.net writes: On 1/1/13 6:48 PM, Tom Lane wrote: Here's a draft patch for that. This didn't make a difference in my test case. I might have to do some bisecting to find where the problem was introduced. Could we see the test case? Or at least oprofile results for

Re: [HACKERS] dynamic SQL - possible performance regression in 9.2

2013-01-02 Thread Jeff Janes
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()

Re: [HACKERS] dynamic SQL - possible performance regression in 9.2

2013-01-02 Thread Tom Lane
Jeff Janes jeff.ja...@gmail.com writes: 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

Re: [HACKERS] dynamic SQL - possible performance regression in 9.2

2013-01-01 Thread Tom Lane
Peter Eisentraut pete...@gmx.net writes: On 12/28/12 5:11 PM, Heikki Linnakangas wrote: 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

Re: [HACKERS] dynamic SQL - possible performance regression in 9.2

2013-01-01 Thread Tom Lane
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

Re: [HACKERS] dynamic SQL - possible performance regression in 9.2

2012-12-31 Thread Peter Eisentraut
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/msg7.php. It looks like that is also caused by the

Re: [HACKERS] dynamic SQL - possible performance regression in 9.2

2012-12-28 Thread Peter Eisentraut
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..100 loop execute 'select 1'; end loop; end $$ language

Re: [HACKERS] dynamic SQL - possible performance regression in 9.2

2012-12-28 Thread Heikki Linnakangas
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..100 loop execute

Re: [HACKERS] dynamic SQL - possible performance regression in 9.2

2012-12-28 Thread Pavel Stehule
Hello 2012/12/28 Heikki Linnakangas hlinnakan...@vmware.com: 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

[HACKERS] dynamic SQL - possible performance regression in 9.2

2012-12-26 Thread Pavel Stehule
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..100 loop execute 'select 1'; end loop; end $$ language plpgsql; CREATE FUNCTION postgres=# \timing Timing is