[PERFORM] Speed of EXCECUTE in PL/PGSQL
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
Re: [PERFORM] Speed of EXCECUTE in PL/PGSQL
On Thu, Mar 14, 2013 at 2:22 PM, Artur Zając aza...@ang.com.pl wrote: 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”? kinda hard to follow you here. but, it looks like you are adding LIMIT 0 which makes performance comparison unfair? merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Speed of EXCECUTE in PL/PGSQL
On 03/14/2013 03:22 PM, Artur Zając wrote: 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
