Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UGDjE-0002I7-3O for pgsql-performance@arkaria.postgresql.org; Thu, 14 Mar 2013 19:22:16 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UGDjD-0005gh-Hu for pgsql-performance@arkaria.postgresql.org; Thu, 14 Mar 2013 19:22:15 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UGDjB-0005fa-Uc for pgsql-performance@postgresql.org; Thu, 14 Mar 2013 19:22:14 +0000 Received: from wentylator.ang.com.pl ([89.174.215.202]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UGDj7-0003ED-Dc for pgsql-performance@postgresql.org; Thu, 14 Mar 2013 19:22:13 +0000 Received: from Artur7 (cfidsl [83.18.105.226]) by wentylator.ang.com.pl (Postfix) with ESMTP id 8184B2DD2A3 for ; Thu, 14 Mar 2013 20:22:11 +0100 (CET) From: =?iso-8859-2?Q?Artur_Zaj=B1c?= To: References: In-Reply-To: Subject: Speed of EXCECUTE in PL/PGSQL Date: Thu, 14 Mar 2013 20:22:07 +0100 Message-ID: <035b01ce20e9$38ec92a0$aac5b7e0$@ang.com.pl> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_035C_01CE20F1.9AB2A850" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQH2I6zEQWdTI9jIdeq0zg9nJbLB15hViwXg Content-Language: pl X-wentylator-MailScanner-Information: Please contact the ISP for more information X-wentylator-MailScanner: Found to be clean X-wentylator-MailScanner-SpamCheck: not spam, SpamAssassin (not cached, score=0, required 6, autolearn=not spam) X-wentylator-MailScanner-From: azajac@ang.com.pl X-Spam-Status: No X-Pg-Spam-Score: -4.3 (----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multipart message in MIME format. ------=_NextPart_000_035C_01CE20F1.9AB2A850 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_035C_01CE20F1.9AB2A850 Content-Type: text/html; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable

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=3D'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=3Dc.idtowmag);

 

When I run this query explain = analyze is:

 

Subquery Scan on a  (cost=3D0.00..0.27 rows=3D1 = width=3D8) (actual time=3D24.041..24.042 rows=3D1 = loops=3D1)

  ->  Result  (cost=3D0.00..0.01 rows=3D1 = width=3D0) (actual time=3D0.001..0.002 rows=3D1 = loops=3D1)

"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=3Dc.idtowmag)” to = query

 

Explain analyze of query is:

"Subquery Scan on a  = (cost=3D0.00..0.27 rows=3D1 width=3D8) (actual time=3D1.868..1.869 = rows=3D1 loops=3D1)"

"  ->  = Result  (cost=3D0.00..0.01 rows=3D1 width=3D0) (actual = time=3D0.002..0.002 rows=3D1 loops=3D1)"

"Total runtime: 1.894 = ms"

 

Explain analyze of “SELECT 1 from tb_klient LIMIT = 0” is:

 

"Limit  (cost=3D0.00..0.13 rows=3D1 width=3D0) = (actual time=3D0.001..0.001 rows=3D0 = loops=3D1)"

"  ->  Seq Scan on tb_klient  = (cost=3D0.00..854.23 rows=3D6823 width=3D0) (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

 

 

------=_NextPart_000_035C_01CE20F1.9AB2A850--