Stored Procedure Performance

["Simon Dale" <sdale@xxxxxx>]

Hi,

 

I’m trying to evaluate PostgreSQL as a database that will have to store a high volume of data and access that data frequently. One of the features on our wish list is to be able to use stored procedures to access the data and I was wondering if it is usual for stored procedures to perform slower on PostgreSQL than raw SQL?

 

A simple example of this can be shown with the following commands:

 

First I created a test table:

 

CREATE TABLE test (

id int8,

name varchar(128),

description varchar(500),

constraint “pk_test” primary key (id)

);

 

Then the function I want to test:

 

CREATE OR REPLACE FUNCTION readTest() RETURNS SETOF test AS

$$

DECLARE

            row test%ROWTYPE;

BEGIN

            FOR row IN SELECT * FROM test LOOP

                        RETURN NEXT row;

            END LOOP;

 

            RETURN;

END;

$$ LANGUAGE plpgsql;

 

Firstly, I ran EXPLAIN on the raw SQL to see how long that takes to access the database the results are as follows:

 

EXPLAIN ANALYZE SELECT * FROM test;

Seq Scan on test  (cost=0.00..10.90 rows=90 width=798) (actual time=0.003..0.003 rows=0 loops=1)

Total runtime: 0.074 ms

(2 rows)

 

Secondly, I ran EXPLAIN on the function created above and the results are as follows:

 

EXPLAIN ANALYZE SELECT * FROM readTest();

Function Scan on readtest  (cost=0.00..12.50 rows=1000 width=798) (actual time=0.870..0.870 rows=0 loops=1)

Total runtime: 0.910 ms

(2 rows)

 

I know that the function is planned the first time it is executed so I ran the same command again to remove that processing from the timings and the results are as follows:

 

EXPLAIN ANALYZE SELECT * FROM readTest();

Function Scan on readtest  (cost=0.00..12.50 rows=1000 width=798) (actual time=0.166..0.166 rows=0 loops=1)

Total runtime: 0.217 ms

(2 rows)

 

Event with the planning removed, the function still performs significantly slower than the raw SQL. Is that normal or am I doing something wrong with the creation or calling of the function?

 

Thanks for your help,

 

Simon

 

Visit our Website at www.rm.com

This message is confidential. You should not copy it or disclose its contents to anyone. You may use and apply the information for the intended purpose only. Internet communications are not secure; therefore, RM does not accept legal responsibility for the contents of this message. Any views or opinions presented are those of the author only and not of RM. If this email has come to you in error, please delete it, along with any attachments. Please note that RM may intercept incoming and outgoing email communications.

Freedom of Information Act 2000
This email and any attachments may contain confidential information belonging to RM. Where the email and any attachments do contain information of a confidential nature, including without limitation information relating to trade secrets, special terms or prices these shall be deemed for the purpose of the Freedom of Information Act 2000 as information provided in confidence by RM and the disclosure of which would be prejudicial to RM's commercial interests.

This email has been scanned for viruses by Trend ScanMail.