Speed of EXCECUTE in PL/PGSQL

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

 

 


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux