Speed of EXCECUTE in PL/PGSQL

[Artur Zając]

Hi,



I have PostgreSQL 9.0.12 on Windows.



I have some simple function:



CREATE OR REPLACE FUNCTION sfunction() RETURNS BOOL AS

$BODY$

DECLARE

q TEXT;

r RECORD;

BEGIN

q='SELECT 1 from tb_klient LIMIT 0';



FOR r IN EXECUTE q

LOOP

END LOOP;

RETURN NULL;



RETURN NULL;

END;

$BODY$

LANGUAGE 'plpgsql';





And some simple Query:





explain analyze SELECT sfunction() AS value

FROM (

SELECT 5604913 AS id ,5666 AS idtowmag

) AS c

LEFT OUTER JOIN tg_tm AS tm ON (tm.ttm_idtowmag=c.idtowmag);



When I run this query explain analyze is:



Subquery Scan on a (cost=0.00..0.27 rows=1 width=8) (actual
time=24.041..24.042 rows=1 loops=1)

-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002
rows=1 loops=1)

"Total runtime: 24.068 ms"



But when I change:

1. Table tb_klient to some other table (but not any other - queries
with some tables are still slow) or

2. "FOR r IN EXECUTE q"
change to
"FOR r IN SELECT 1 from tb_klient LIMIT 0" or

3. add "LEFT OUTER JOIN tb_klient AS kl ON
(kl.k_idklienta=c.idtowmag)" to query



Explain analyze of query is:

"Subquery Scan on a (cost=0.00..0.27 rows=1 width=8) (actual
time=1.868..1.869 rows=1 loops=1)"

" -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002
rows=1 loops=1)"

"Total runtime: 1.894 ms"



Explain analyze of "SELECT 1 from tb_klient LIMIT 0" is:



"Limit (cost=0.00..0.13 rows=1 width=0) (actual time=0.001..0.001 rows=0
loops=1)"

" -> Seq Scan on tb_klient (cost=0.00..854.23 rows=6823 width=0) (never
executed)"

"Total runtime: 0.025 ms"



tb_klient has 8200 rows and 77 cols.



Why speed of executing (or planning) some very simple query from string in
pl/pgsql is dependent from whole query or why "FOR r IN EXECUTE q" is
significally slower from "FOR r IN query"?





-------------------------------------------

Artur Zajac

[Merlin Moncure]

Post by Artur Zając
Hi,
I have PostgreSQL 9.0.12 on Windows.
CREATE OR REPLACE FUNCTION sfunction() RETURNS BOOL AS
$BODY$
DECLARE
q TEXT;
r RECORD;
BEGIN
q='SELECT 1 from tb_klient LIMIT 0';
FOR r IN EXECUTE q
LOOP
END LOOP;
RETURN NULL;
RETURN NULL;
END;
$BODY$
LANGUAGE 'plpgsql';
explain analyze SELECT sfunction() AS value
FROM (
SELECT 5604913 AS id ,5666 AS idtowmag
) AS c
LEFT OUTER JOIN tg_tm AS tm ON (tm.ttm_idtowmag=c.idtowmag);
Subquery Scan on a (cost=0.00..0.27 rows=1 width=8) (actual
time=24.041..24.042 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002
rows=1 loops=1)
"Total runtime: 24.068 ms"
1. Table tb_klient to some other table (but not any other – queries
with some tables are still slow) or
2. “FOR r IN EXECUTE q”
change to
“FOR r IN SELECT 1 from tb_klient LIMIT 0” or
3. add “LEFT OUTER JOIN tb_klient AS kl ON
(kl.k_idklienta=c.idtowmag)” to query
"Subquery Scan on a (cost=0.00..0.27 rows=1 width=8) (actual
time=1.868..1.869 rows=1 loops=1)"
" -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002
rows=1 loops=1)"
"Total runtime: 1.894 ms"
"Limit (cost=0.00..0.13 rows=1 width=0) (actual time=0.001..0.001 rows=0
loops=1)"
" -> Seq Scan on tb_klient (cost=0.00..854.23 rows=6823 width=0) (never
executed)"
"Total runtime: 0.025 ms"
tb_klient has 8200 rows and 77 cols.
Why speed of executing (or planning) some very simple query from string in
pl/pgsql is dependent from whole query or why “FOR r IN EXECUTE q” is
significally slower from “FOR r IN query”?

kinda hard to follow you here. but, it looks like you are adding LIMIT
0 which makes performance comparison unfair?

merlin

[Andrew Dunstan]

Post by Artur Zając
Why speed of executing (or planning) some very simple query from
string in pl/pgsql is dependent from whole query or why “FOR r IN
EXECUTE q” is significally slower from “FOR r IN query”?

The whole point of EXECUTE is that it's reparsed and planned each time.
You should expect it to be quite a bit slower, and avoid using EXECUTE
wherever possible.

cheers

andrew