Re: Speed of EXCECUTE in PL/PGSQL

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