slow SP with temporary tables, any idea of solution?
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
Pavel Stehule <stehule@kix.fsv.cvut.cz> writes:
Can you help me other possibilities?
Create the temp table only once per connection (you can use ON COMMIT
DELETE ROWS instead of ON COMMIT DROP to clean it out). Then you won't
need to use EXECUTE.
regards, tom lane
Pavel Stehule <stehule@kix.fsv.cvut.cz> writes:
Can you help me other possibilities?
Create the temp table only once per connection (you can use ON COMMIT
DELETE ROWS instead of ON COMMIT DROP to clean it out). Then you won't
need to use EXECUTE.
I am not sure so it's possible. I use persistent connect via PHP. There
isn't trigger for new connect. But I found same problem as you. PL/pgSQL
can't
FOR r IN EXPLAIN SELECT ..
FOR r IN EXECUTE 'EXECUTE plan()'
I rewrite SP, and I have only one SELECT without two, 20% time less, but
it's not readable code. I don't know how much work or if its possible move
compilation time for PREPARE on every processing of this command. I think
so its more natural for cmd PREPARE. But in this part of PL/pgSQL are more
problems:
CREATE OR REPLACE FUNCTION foo() RETURNS SETOF varchar AS $$
DECLARE r RECORD;
BEGIN PREPARE se(date) AS SELECT * FROM queue WHERE activated = $1;
FOR r IN EXECUTE se(CURRENT_DATE) LOOP
RETUTRN NEXT r.activated;
END LOOP;
DEALLOCATE se;
RETURN;
END; $$ LANGUAGE plpgsql;
is this code correct? I think yes. But isn't true. I get message: function
se(date) does not exist CONTEXT: SQL statement "SELECT se(CURRENT_DATE)".
Is only theory: FOR rn IN EXECUTE 'EXECUTE se(CURRENT_DATE)' LOOP
Now I get error: cannot open non-SELECT query as cursor. Prepared commands
are good idea, but I cant use its now.
I have Pg 8.0.1
Regards
Pavel Stehule
Create the temp table only once per connection (you can use ON COMMIT
DELETE ROWS instead of ON COMMIT DROP to clean it out). Then you won't
need to use EXECUTE.
I am sorry, first time I didn't understand. Now I did some test and its
good adivice. Sometimes I have problem understand so I can use temp.
tables on an level of connection and its not neccesery push temp tables on
SP level. If I don't change structure of temp tables (and its unpropably
on produstion database) I haven't problems. I have still one question.
What is possible so I can
CREATE OR REPLACE FONCTION foo() ..
BEGIN CREATE TEMP TABLE xxx(...
INSERT INTO xxx VALUES (...
It's works, but in time of compilation SP temp table xxx doesn't exists.
It's mean so Id in cmd INSERT is little bit dynamic?
I rewrite my SP and I have one notice: In documentation is note, so
TRUNCATE TABLE is faster then DELETE FROM. It's not true for small temp
tables. On my computer TRUNCATE needs 100ms and DELETE 8ms. It's general
or any exception?
Thank You
Pavel Stehule