Thread: Speed of EXCECUTE in PL/PGSQL

On Thu, Mar 14, 2013 at 2:22 PM, Artur Zając <azajac@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

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