Skip site navigation (1) Skip section navigation (2)

dynamic SQL - possible performance regression in 9.2

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: dynamic SQL - possible performance regression in 9.2
Date: 2012-12-27 06:07:06
Message-ID: CAFj8pRCKfoz6L82PovLXNK-1JL=jzjwaT8e2BD2PwNKm7i7KVg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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;
CREATE FUNCTION
postgres=# \timing
Timing is on.
postgres=# select test();
 test
------

(1 row)

Time: 7652.904 ms
postgres=# select test();
 test
------

(1 row)

Time: 7828.025 ms

-- 9.2
postgres=# create or replace function test() returns void as $$ begin
for i in 1..1000000 loop execute 'select 1'; end loop; end $$ language
plpgsql;
CREATE FUNCTION
Time: 59.272 ms
postgres=# select test();
 test
------

(1 row)

Time: 11153.646 ms
postgres=# select test();
 test
------

(1 row)

Time: 11081.899 ms

This test is synthetic, but it shows so somebody who use dynamic SQL
in triggers (for partitioning) can has slower operations.


Responses

pgsql-hackers by date

Next:From: Heikki LinnakangasDate: 2012-12-27 10:06:12
Subject: Re: pg_basebackup from cascading standby after timeline switch
Previous:From: Josh BerkusDate: 2012-12-27 05:33:01
Subject: Re: Proposal: Store "timestamptz" of database creation on "pg_database"

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group