Speed of EXCECUTE in PL/PGSQL

pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed

Speed of EXCECUTE in PL/PGSQL
3+ messages / 3 participants
[nested] [flat]

* Speed of EXCECUTE in PL/PGSQL
@ 2013-03-14 19:22  Artur Zając <azajac@ang.com.pl>
  0 siblings, 2 replies; 3+ messages in thread

From: Artur Zając @ 2013-03-14 19:22 UTC (permalink / raw)
  To: pgsql-performance

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Speed of EXCECUTE in PL/PGSQL
@ 2013-03-14 19:36  Merlin Moncure <mmoncure@gmail.com>
  parent: Artur Zając <azajac@ang.com.pl>
  1 sibling, 0 replies; 3+ messages in thread

From: Merlin Moncure @ 2013-03-14 19:36 UTC (permalink / raw)
  To: Artur Zając <azajac@ang.com.pl>; +Cc: pgsql-performance

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Speed of EXCECUTE in PL/PGSQL
@ 2013-03-14 19:39  Andrew Dunstan <andrew@dunslane.net>
  parent: Artur Zając <azajac@ang.com.pl>
  1 sibling, 0 replies; 3+ messages in thread

From: Andrew Dunstan @ 2013-03-14 19:39 UTC (permalink / raw)
  To: Artur Zając <azajac@ang.com.pl>; +Cc: pgsql-performance


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



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2013-03-14 19:39 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-03-14 19:22 Speed of EXCECUTE in PL/PGSQL Artur Zając <azajac@ang.com.pl>
2013-03-14 19:36 ` Merlin Moncure <mmoncure@gmail.com>
2013-03-14 19:39 ` Andrew Dunstan <andrew@dunslane.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox