slow SP with temporary tables, any idea of solution?

From: Pavel Stehule <stehule(at)kix(dot)fsv(dot)cvut(dot)cz>
To: pgsql-hackers(at)postgresql(dot)org
Subject: slow SP with temporary tables, any idea of solution?
Date: 2005-02-10 06:33:53
Message-ID: Pine.LNX.4.44.0502100700530.9430-100000@kix.fsv.cvut.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello

I have very slow SP this type:
BEGIN
CREATE TEMP TABLE xxx ON COMMIT DROP();
WHILE n > 0 LOOP -- n >> 0
FOR _r IN EXECUTE 'SELECT ...' LOOP

RETURN NEXT _r;
EXECUTE 'UPDATE xxx SET item = 1 WHERE id = '||_r.id;
n := n - 1;
END LOOP;
END LOOP;
END;

The main problem is too much EXECUTE commands. My first idea wos
substitute it PREPARED plans. But PREPARED plans are compiled when
procedure is first time lunched, and I can't easy use it form temp tables.
I can change PREPARE upd UPDATE item SET .. to EXECUTE 'PREPARE upd
UPDATE..' and I really saved some time. But this method isn't possible for
cmd FOR

I have two possibility solution (before ending successfull solution for SP
and temporary tables). First, the time for really preparing command will
be really time of executing PREPARE command. Second, PREPARE can accept
string parametr like EXECUTE command. But all is inpossible now.

Can you help me other possibilities?
Thank you
Pavel Stehule

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Martin Pitt 2005-02-10 09:41:32 Re: libpq API incompatibility between 7.4 and 8.0
Previous Message Michael Fuhr 2005-02-10 06:16:19 Re: [SQL] Function .. AS..?