On Thu, Mar 14, 2013 at 2:22 PM, Artur Zając <azajac@xxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
