Re: Replacing Cursors with Temporary Tables - Mailing list pgsql-performance

More benchmarking results are in with a comparison between cursors, arrays, and temporary tables for storing, using, and accessing data outside the stored procedure:

CREATE OR REPLACE FUNCTION Test_Init() RETURNS INTEGER AS
$BODY$
DECLARE
   temp INTEGER;
BEGIN
   DROP TABLE IF EXISTS test_table1 CASCADE;
   CREATE TABLE test_table1 (
      id SERIAL NOT NULL PRIMARY KEY,
      junk_field1 INTEGER,
      junk_field2 INTEGER,
      junk_field3 INTEGER
   ) WITH (OIDS=FALSE);
   DROP INDEX IF EXISTS test_table1_junk_field1_idx CASCADE;
   DROP INDEX IF EXISTS test_table1_junk_field2_idx CASCADE;
   DROP INDEX IF EXISTS test_table1_junk_field3_idx CASCADE; 
   FOR i IN 1..10000 LOOP
      INSERT INTO test_table1 (junk_field1, junk_field2, junk_field3) VALUES
        (i%10, i%20, i%30);
   END LOOP;
   CREATE INDEX test_table1_junk_field1_idx ON test_table1 USING btree (junk_field1);
   CREATE INDEX test_table1_junk_field2_idx ON test_table1 USING btree (junk_field2);
   CREATE INDEX test_table1_junk_field3_idx ON test_table1 USING btree (junk_field3);
   DROP TABLE IF EXISTS test_table2 CASCADE;
   CREATE TABLE test_table2 (
      id SERIAL NOT NULL PRIMARY KEY,
      junk_field1 INTEGER,
      junk_field2 INTEGER,
      junk_field3 INTEGER
   ) WITH (OIDS=FALSE);
   DROP INDEX IF EXISTS test_table2_junk_field1_idx CASCADE;
   DROP INDEX IF EXISTS test_table2_junk_field2_idx CASCADE;
   DROP INDEX IF EXISTS test_table2_junk_field3_idx CASCADE; 
   FOR i IN 1..10000 LOOP
      INSERT INTO test_table2 (junk_field1, junk_field2, junk_field3) VALUES
          (i%15, i%25, i%35);
   END LOOP;
   CREATE INDEX test_table2_junk_field1_idx ON test_table2 USING btree (junk_field1);
   CREATE INDEX test_table2_junk_field2_idx ON test_table2 USING btree (junk_field2);
   CREATE INDEX test_table2_junk_field3_idx ON test_table2 USING btree (junk_field3);
   RETURN 1;
END;
$BODY$
LANGUAGE plpgsql;

SELECT * FROM Test_Init();

DROP TYPE IF EXISTS test_row_type CASCADE;
CREATE TYPE test_row_type AS (
   junk_field1 INTEGER,
   junk_field2 INTEGER,
   junk_field3 INTEGER
);

CREATE OR REPLACE FUNCTION Test1() RETURNS INTEGER AS
$BODY$
DECLARE
   temp_row test_row_type;
   cursresults test_row_type[];
   curs SCROLL CURSOR IS
      SELECT * FROM test_table1 WHERE junk_field1=8;
BEGIN
    FOR temp IN curs LOOP
       temp_row := temp;
       cursresults := array_append(cursresults, temp_row);
    END LOOP;
    OPEN curs;
    RETURN 1;
END;
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION Test2() RETURNS INTEGER AS
$BODY$
DECLARE
cursresults test_row_type[];
   cur SCROLL CURSOR IS
     SELECT * FROM unnest(cursresults);
BEGIN
   cursresults := array(SELECT (junk_field1, junk_field2, junk_field3)::test_row_type AS rec FROM test_table1 WHERE junk_field1=8);
   OPEN cur;
   RETURN 1;
END;
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION Test3() RETURNS INTEGER AS
$BODY$
DECLARE
BEGIN
   CREATE TEMPORARY TABLE results WITH (OIDS=FALSE) ON COMMIT DROP AS (
      SELECT junk_field1, junk_field2, junk_field3 FROM test_table1 WHERE junk_field1=8
   );
   RETURN 1;
END;
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION Test4() RETURNS INTEGER AS
$BODY$
DECLARE
   cur SCROLL CURSOR IS
      SELECT * FROM results;
BEGIN
   CREATE TEMPORARY TABLE results WITH (OIDS=FALSE) ON COMMIT DROP AS (
      SELECT junk_field1, junk_field2, junk_field3 FROM test_table1 WHERE junk_field1=8
   );
   OPEN cur;
   RETURN 1;
END;
$BODY$
LANGUAGE plpgsql;

EXPLAIN ANALYZE SELECT * FROM Test1();
"Function Scan on test1 (cost=0.00..0.26 rows=1 width=4) (actual time=17.701..17.701 rows=1 loops=1)"
"Total runtime: 17.714 ms" -- Ouch

EXPLAIN ANALYZE SELECT * FROM Test2();

"Function Scan on test2 (cost=0.00..0.26 rows=1 width=4) (actual time=1.137..1.137 rows=1 loops=1)"
"Total runtime: 1.153 ms" -- Wow

EXPLAIN ANALYZE SELECT * FROM Test3();
"Function Scan on test3 (cost=0.00..0.26 rows=1 width=4) (actual time=2.033..2.034 rows=1 loops=1)"
"Total runtime: 2.050 ms"

EXPLAIN ANALYZE SELECT * FROM Test4();
"Function Scan on test4 (cost=0.00..0.26 rows=1 width=4) (actual time=2.001..2.001 rows=1 loops=1)"
"Total runtime: 2.012 ms"

In each case, the results are available outside the stored procedure by either fetching from the cursor or selecting from the temporary table. Clearly, the temporary table takes a performance hit compared using arrays. Building an array with array append is horrendously inefficient. Unnesting an array is surprisingly efficient. As can be seen from Test3 and Test4, cursors have no detectable overhead for opening the cursor (at least in this example with 1000 result rows). It is unclear whether there is any difference at all from Test3 and Test4 for retrieving the data as I have no easy way right now to measure that accurately. However, since arrays+cursors are more efficient than anything having to do with temp tables, that is the way I will go. With the number of rows I am dealing with (which should always be less than 1,000 in the final returned result set), unnesting an array is much faster than building a temp table and selecting from it. 

If anyone thinks I may have missed some important item in this testing, please let me know.