Re: [PERFORM] SQL Function Performance

[Mark Liberman]

Title: RE: [PERFORM] SQL Function Performance






> in my case; both direct query and sql function gererate same execution plan. Also, execution plan belongs to the sql function better than direct sql > query plan. But, direct sql result comes less than 1 second. sql function result comes about in 50 seconds.

How are you getting at the plan inside your function?  If you just do an EXPLAIN on the function call you get a FUNCTION SCAN line in your plan, which tells you nothing.  I remember I had to work through some process for catching the output of the Explain plan in a cursor and returning that to actually see the plan.  I saw in a previous response he suggested using a PREPARE and EXECUTE against that.  I'm not sure that's the same as what's going on in the function (although I could be wrong).

Just humor me and try creating the sql query in the fuction in a text variable and then Executing it. 

Prior to that, however, you might try just recreating the function.  The plan may be re-evaluated at that point.

- Mark

Re: [PERFORM] SQL Function Performance

[Mark Liberman]

Title: RE: [PERFORM] SQL Function Performance






I've run into this issue. It basically comes down to the plan that is being used inside the function is not the same as the plan used when you issue the query manually outside of the function.  Although I'm no expert on when plans are prepared and re-evaluated for functions, I know that they are not re-evaluated each time to execute the function.

So, what I did in such cases was to build up the sql query in a text variable inside my function, and then use the EXECUTE command inside the function.  When you use the EXECUTE command, the plan is prepared each time.  I know there is some minimal overhead of preparing the plan each time, but it seems like it's minor compared to the saving's you'll get.

- Mark

Re: [PERFORM] SQL Function Performance

[Adnan DURSUN]

 
 
---Original Message---
 

From: Michael Fuhr
Date: 02/14/06 23:05:55
To: Adnan DURSUN
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] SQL 
Function Performance
 
>On Tue, Feb 14, 2006 at 11:33:57AM +0200, Adnan DURSUN wrote:
>> 
->  Nested Loop  (cost=5.90..267.19 rows=3 width=101) 
(actual time=76.240..30974.777 rows=63193 loops=1)
>>   
->  Nested Loop  (cost=5.90..123.48 rows=26 width=73) 
(actual time=32.082..4357.786 rows=14296 loops=1)
 
>Absent a better solution, you could write a PL/pgSQL function and
>build the query as a text string, then EXECUTE it.  That 
would give
>you a new plan each time, one that can take better advantage of
>statistics, at the cost of having to plan the query each time you
>call the function (but you probably don't care about that cost
>as long as the overall results are better).  Here's an 
example:
 
    Yes, i did it. i wrote a PL/pgSQL function. Now results 
come at 100 ms.. :-)
I dont like that method but i have to do it for perfomance
 
Many thanks to everyone who helps...
 

Adnan DURSUN
ASRIN Bilisim Ltd.
Ankara /TURKEY
---(end of 
broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] SQL Function Performance

[Michael Fuhr]

On Tue, Feb 14, 2006 at 11:33:57AM +0200, Adnan DURSUN wrote:
> ->  Nested Loop  (cost=5.90..267.19 rows=3 width=101) (actual 
> time=76.240..30974.777 rows=63193 loops=1)
>   ->  Nested Loop  (cost=5.90..123.48 rows=26 width=73) (actual 
> time=32.082..4357.786 rows=14296 loops=1)

A prepared query is planned before the parameters' values are known,
so the planner can't take full advantage of column statistics to
estimate row counts.  The planner must therefore decide on a plan
that should be reasonable in most cases; apparently this isn't one
of those cases, as the disparity between estimated and actual rows
shows.  Maybe Tom (one of the core developers) can comment on whether
anything can be done to improve the plan in this case.

Absent a better solution, you could write a PL/pgSQL function and
build the query as a text string, then EXECUTE it.  That would give
you a new plan each time, one that can take better advantage of
statistics, at the cost of having to plan the query each time you
call the function (but you probably don't care about that cost
as long as the overall results are better).  Here's an example:

CREATE FUNCTION fooquery(qval text) RETURNS SETOF foo AS $$
DECLARE
rowfoo%ROWTYPE;
query  text;
BEGIN
query := 'SELECT * FROM foo WHERE val = ' || quote_literal(qval);

FOR row IN EXECUTE query LOOP
RETURN NEXT row;
END LOOP;

RETURN;
END;
$$ LANGUAGE plpgsql STABLE STRICT;

-- 
Michael Fuhr

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] SQL Function Performance

[Adnan DURSUN]

 

From: Michael Fuhr
Date: 02/14/06 03:32:28
To: Tom Lane
Cc: Adnan DURSUN; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] SQL 
Function Performance
 
On Mon, Feb 13, 2006 at 07:57:07PM -0500, Tom Lane wrote:
>> "Adnan DURSUN" <[EMAIL PROTECTED]> writes:
>> >>>> EXPLAIN ANALYZE EXECUTE stmt (...);
>>
>> >Here is the EXPLAIN ANALYZE output for 
prepared statement :
>>
>> This is exactly the same as the other plan --- you did not 
parameterize
>> the query.  To see what's going on, you need to insert 
PREPARE
>> parameters in the places where the function uses plpgsql 
variables.
 
>Actually it was an SQL function, but that also does 
PREPARE/EXECUTE,
>right?
 
>Adnan, what Tom is saying is that I requested this (simplified):
 
>PREPARE stmt (integer) AS SELECT * FROM foo WHERE id = $1;
>EXPLAIN ANALYZE EXECUTE stmt (12345);
 
Ok. I am sending right execution plan.  I made mistake apologize 
me..
 

   QUERY PLAN"HashAggregate  (cost=276.73..276.76 rows=1 
width=58) (actual time=192648.385..192648.385 rows=0 loops=1)""  
->  Nested Loop  (cost=5.90..276.71 rows=1 width=58) (actual 
time=192648.377..192648.377 rows=0 
loops=1)""    Join Filter: 
(((""inner"".hat_no)::text = (""outer"".hat_no)::text) AND (""inner"".sefer_kod 
= ""outer"".sefer_kod) AND (""inner"".plan_tarihi = ""outer"".plan_tarihi) AND 
(""inner"".bin_yer_kod = ""outer"".bin_yer_kod) AND (""inner"".koltuk_no = 
""outer"".koltuk_no) AND (((""inner"".islem_tarihi = $2) AND (($5)::text = 
'I'::text)) OR ((""outer"".kalkis_tarihi = $2) AND (($5)::text = 'K'::text))) 
AND (((""outer"".lokal_kod = $3) AND (($4)::text = 'K'::text)) OR 
((""inner"".ypt_lcl_kod = $3) AND (($4)::text = 
'I'::text""    ->  Nested 
Loop  (cost=5.90..267.19 rows=3 width=101) (actual time=76.240..30974.777 
rows=63193 
loops=1)""  
->  Nested Loop  (cost=5.90..123.48 rows=26 width=73) (actual 
time=32.082..4357.786 rows=14296 
loops=1)""    
->  Nested Loop  (cost=3.62..15.29 rows=1 width=48) (actual 
time=1.279..46.882 rows=41 
loops=1)""  
Join Filter: ((""inner"".kod)::text = 
(""outer"".durumu)::text)""  
->  Nested Loop  (cost=3.62..13.01 rows=1 width=53) (actual 
time=1.209..40.010 rows=41 
loops=1)""    
->  Nested Loop  (cost=3.62..8.49 rows=1 width=47) (actual 
time=1.150..38.928 rows=41 
loops=1)""  
Join Filter: ((""inner"".""no"")::text = 
(""outer"".hat_no)::text)""  
->  Nested Loop  (cost=2.25..6.79 rows=1 width=28) (actual 
time=0.710..24.708 rows=41 
loops=1)""    
Join Filter: (""inner"".sefer_tip_kod = 
""outer"".kod)""    
->  Seq Scan on t_sefer_tip t  (cost=0.00..1.03 rows=1 width=9) 
(actual time=0.117..0.126 rows=1 
loops=1)""  
Filter: (((iptal)::text = 'H'::text) AND (($1)::text = 
(firma_no)::text))""    
->  Hash Join  (cost=2.25..5.74 rows=2 width=32) (actual 
time=0.567..24.349 rows=41 
loops=1)""  
Hash Cond: ((""outer"".ek_dev)::text = 
(""inner"".kod)::text)""  
->  Seq Scan on t_seferler s  (cost=0.00..3.21 rows=34 width=37) 
(actual time=0.077..23.466 rows=41 
loops=1)""    
Filter: (((iptal)::text = 'H'::text) AND ((iptal)::text = 'H'::text) AND 
((firma_no)::text = 
($1)::text))""  
->  Hash  (cost=2.25..2.25 rows=2 width=5) (actual 
time=0.451..0.451 rows=2 
loops=1)""    
->  Seq Scan on t_domains d1  (cost=0.00..2.25 rows=2 width=5) 
(actual time=0.346..0.429 rows=2 
loops=1)""  
Filter: ((name)::text = 
'EKDEV'::text)""  
->  Merge Join  (co

Re: [PERFORM] SQL Function Performance

[Michael Fuhr]

On Mon, Feb 13, 2006 at 07:57:07PM -0500, Tom Lane wrote:
> "Adnan DURSUN" <[EMAIL PROTECTED]> writes:
>  EXPLAIN ANALYZE EXECUTE stmt (...);
> 
> >Here is the EXPLAIN ANALYZE output for prepared statement :
> 
> This is exactly the same as the other plan --- you did not parameterize
> the query.  To see what's going on, you need to insert PREPARE
> parameters in the places where the function uses plpgsql variables.

Actually it was an SQL function, but that also does PREPARE/EXECUTE,
right?

Adnan, what Tom is saying is that I requested this (simplified):

PREPARE stmt (integer) AS SELECT * FROM foo WHERE id = $1;
EXPLAIN ANALYZE EXECUTE stmt (12345);

but instead you appear to have done this:

PREPARE stmt AS SELECT * FROM foo WHERE id = 12345;
EXPLAIN ANALYZE EXECUTE stmt;

We can tell because if you had done it the first way (parameterized)
then the EXPLAIN ANALYZE output would have shown the parameters as
$1, $2, $3, etc., which it didn't.

-- 
Michael Fuhr

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [PERFORM] SQL Function Performance

[Tom Lane]

"Adnan DURSUN" <[EMAIL PROTECTED]> writes:
 EXPLAIN ANALYZE EXECUTE stmt (...);

>Here is the EXPLAIN ANALYZE output for prepared statement :

This is exactly the same as the other plan --- you did not parameterize
the query.  To see what's going on, you need to insert PREPARE
parameters in the places where the function uses plpgsql variables.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] SQL Function Performance

[Adnan DURSUN]

---Original Message---
 

From: Mark Liberman
Date: 02/14/06 01:46:16
To: Adnan DURSUN; pgsql-performance@postgresql.org
Subject: RE: [PERFORM] SQL 
Function Performance
 
>> in my case; both direct query and sql function gererate 
same execution plan. Also, execution plan belongs to the sql function better 
than direct sql >> query plan. But, direct sql result comes less than 
1 second. sql function result comes about in 50 seconds.>How are you 
getting at the plan inside your function?  If you just do an EXPLAIN on the 
function call you get a FUNCTION SCAN line in your plan, which tells you 
>nothing.  I remember I had to work through some process for catching 
the output of the Explain plan in a cursor and returning that to actually see 
the plan.  I saw in a >previous response he suggested using a PREPARE 
and EXECUTE against that.  I'm not sure that's the same as what's going on 
in the function (although I could be >wrong).   Yes, i have 
got sql function prepared execution plan using PREPARE and EXECUTE that he 
suggested to me. 
>Just humor me and try creating the sql query in the fuction in a text 
variable and then Executing it.    But i believe that, 
that behavior of PostgreSQL is not good. It should handle this case. PostgreSQL 
has this "sql function" functionality and it 
should   give good serve...Of course, i will do your 
suggesion if i dont solve it.>Prior to that, however, you might try 
just recreating the function.  The plan may be re-evaluated at that 
point.    Ok. i did it many times. But nothing was 
changed..- Mark
 
Adnan DURSUN
ASRIN Bilişim Ltd.Şti

 

Re: [PERFORM] SQL Function Performance

[Adnan DURSUN]

>From: Mark 
Liberman

>Date: 02/13/06 
22:09:48
>To: Adnan DURSUN; pgsql-performance@postgresql.org
>Subject: RE: [PERFORM] 
SQL Function Performance
 
>I've run into this issue. It basically comes down to the 
plan that is being used inside the function is not the same as the plan used 
when you issue the query manually >outside of the function.  Although 
I'm no expert on when plans are prepared and re-evaluated for functions, I know 
that they are not re-evaluated each time to execute the 
>function.
 in my case; both direct query and sql function gererate 
same execution plan. Also, execution plan belongs to the sql function better 
than direct sql query plan. But, direct sql result comes less than 1 second. sql 
function result comes about in 50 seconds.

Adnan DURSUN
ASRIN Bilişim Ltd.Şti

Re: [PERFORM] SQL Function Performance

[Adnan DURSUN]

 

  
  
  From: Michael Fuhr
  Date: 02/13/06 
  07:46:05
  To: Adnan DURSUN
  Cc: pgsql-performance@postgresql.org
  Subject: Re: [PERFORM] SQL 
  Function Performance
   
  On Sun, Feb 12, 2006 at 10:25:28PM +0200, Adnan DURSUN wrote:
  >> My database has an SQL function. The result comes in 30-40 
  seconds
  >> when i use the SQL function. On the other hand; The result 
  comes
  >> 300-400 milliseconds when i run the SQL statement. Any idea 
  ??
   
  >Have you analyzed the tables?  If that's not the problem 
  then could
  >you post the EXPLAIN ANALYZE output for the direct query and for 
  a
  >prepared query?  For the prepared query do this:
   
  >EXPLAIN ANALYZE EXECUTE stmt (...);
   
   Here is the EXPLAIN ANALYZE output for prepared statement :
   
  QUERY PLAN"HashAggregate  (cost=29.37..29.40 rows=1 width=58) 
  (actual time=10.600..10.600 rows=0 loops=1)""  ->  Nested 
  Loop  (cost=9.55..29.36 rows=1 width=58) (actual time=10.594..10.594 
  rows=0 loops=1)""    Join Filter: 
  (((""inner"".hat_no)::text = (""outer"".hat_no)::text) AND 
  (""inner"".sefer_kod = ""outer"".sefer_kod) AND (""inner"".plan_tarihi = 
  ""outer"".plan_tarihi) AND (""inner"".bin_yer_kod = ""outer"".bin_yer_kod) AND 
  (""inner"".koltuk_no = 
  ""outer"".koltuk_no))""    
  ->  Nested Loop  (cost=9.55..26.15 rows=1 width=93) (actual 
  time=10.588..10.588 rows=0 
  loops=1)""  
  ->  Nested Loop  (cost=9.55..20.60 rows=1 width=65) (actual 
  time=7.422..10.499 rows=1 
  loops=1)""    
  ->  Nested Loop  (cost=9.55..14.62 rows=1 width=48) (actual 
  time=5.455..7.247 rows=41 
  loops=1)""  
  Join Filter: (""outer"".sefer_tip_kod = 
  ""inner"".kod)""  
  ->  Hash Join  (cost=9.55..13.58 rows=1 width=52) (actual 
  time=5.432..6.131 rows=41 
  loops=1)""    
  Hash Cond: (""outer"".kod = 
  ""inner"".varis_yer_kod)""    
  ->  Seq Scan on t_yer y2  (cost=0.00..3.44 rows=115 width=14) 
  (actual time=0.018..0.375 rows=115 
  loops=1)""  
  Filter: ((iptal)::text = 
  'H'::text)""    
  ->  Hash  (cost=9.55..9.55 rows=1 width=46) (actual 
  time=5.352..5.352 rows=41 
  loops=1)""  
  ->  Merge Join  (cost=9.45..9.55 rows=1 width=46) (actual 
  time=4.713..5.182 rows=41 
  loops=1)""    
  Merge Cond: (""outer"".kod = 
  ""inner"".kalkis_yer_kod)""    
  ->  Index Scan using t_yer_pkey on t_yer y1  (cost=0.00..9.62 
  rows=115 width=14) (actual time=0.021..0.176 rows=40 
  loops=1)""  
  Filter: ((iptal)::text = 
  'H'::text)""    
  ->  Sort  (cost=9.45..9.45 rows=1 width=40) (actual 
  time=4.678..4.747 rows=41 
  loops=1)""  
  Sort Key: 
  h.kalkis_yer_kod""  
  ->  Nested Loop  (cost=4.51..9.44 rows=1 width=40) (actual 
  time=0.412..4.389 rows=41 
  loops=1)""    
  Join Filter: ((""inner"".""no"")::text = 
  (""outer"".hat_no)::text)""    
  ->  Hash Join  (cost=4.51..8.09 rows=1 width=27) (actual 
  time=0.386..1.137 rows=41 
  loops=1)""  
  Hash Cond: ((""outer"".durumu)::text = 
  (""inner"".kod)::text)""  
  ->  Hash Join  (cost=2.25..5.80 rows=3 width=32) (actual 
  time=0.193..0.751 rows=41 
  loops=1)""    
  Hash Cond: ((""outer"".ek_dev)::text = 
  (""inner"".kod)::text)""    
  ->  Seq Scan on t_seferler s  (cost=0.00..3.21 rows=41 width=37) 
  (actual time=0.009..0.258 rows=41 
  loops=1)"" 

Re: [PERFORM] SQL Function Performance

[Adnan DURSUN]

 

  


From: Michael Fuhr
Date: 02/13/06 
07:46:05
To: Adnan DURSUN
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] 
SQL Function Performance
 
On Sun, Feb 12, 2006 at 10:25:28PM +0200, Adnan DURSUN wrote:
>> My database has an SQL function. The result comes in 30-40 
seconds
>> when i use the SQL function. On the other hand; The result 
comes
>> 300-400 milliseconds when i run the SQL statement. Any idea 
??
 
>Have you analyzed the tables?  If that's not the problem 
then could
>you post the EXPLAIN ANALYZE output for the direct query and for 
a
>prepared query?  For the prepared query do this:
 
EXPLAIN ANALYZE for direct query :
 
QUERY PLAN"HashAggregate  (cost=29.37..29.40 rows=1 width=58) 
(actual time=12.114..12.114 rows=0 loops=1)""  ->  Nested 
Loop  (cost=9.55..29.36 rows=1 width=58) (actual time=12.107..12.107 
rows=0 loops=1)""    Join Filter: 
(((""inner"".hat_no)::text = (""outer"".hat_no)::text) AND 
(""inner"".sefer_kod = ""outer"".sefer_kod) AND (""inner"".plan_tarihi = 
""outer"".plan_tarihi) AND (""inner"".bin_yer_kod = ""outer"".bin_yer_kod) 
AND (""inner"".koltuk_no = 
""outer"".koltuk_no))""    
->  Nested Loop  (cost=9.55..26.15 rows=1 width=93) (actual 
time=12.102..12.102 rows=0 
loops=1)""  
->  Nested Loop  (cost=9.55..20.60 rows=1 width=65) (actual 
time=8.984..12.012 rows=1 
loops=1)""    
->  Nested Loop  (cost=9.55..14.62 rows=1 width=48) (actual 
time=6.155..7.919 rows=41 
loops=1)""  
Join Filter: (""outer"".sefer_tip_kod = 
""inner"".kod)""  
->  Hash Join  (cost=9.55..13.58 rows=1 width=52) (actual 
time=6.129..6.846 rows=41 
loops=1)""    
Hash Cond: (""outer"".kod = 
""inner"".varis_yer_kod)""    
->  Seq Scan on t_yer y2  (cost=0.00..3.44 rows=115 width=14) 
(actual time=0.018..0.374 rows=115 
loops=1)""  
Filter: ((iptal)::text = 
'H'::text)""    
->  Hash  (cost=9.55..9.55 rows=1 width=46) (actual 
time=6.058..6.058 rows=41 
loops=1)""  
->  Merge Join  (cost=9.45..9.55 rows=1 width=46) (actual 
time=4.734..5.894 rows=41 
loops=1)""    
Merge Cond: (""outer"".kod = 
""inner"".kalkis_yer_kod)""    
->  Index Scan using t_yer_pkey on t_yer y1  (cost=0.00..9.62 
rows=115 width=14) (actual time=0.021..0.183 rows=40 
loops=1)""  
Filter: ((iptal)::text = 
'H'::text)""    
->  Sort  (cost=9.45..9.45 rows=1 width=40) (actual 
time=4.699..4.768 rows=41 
loops=1)""  
Sort Key: 
h.kalkis_yer_kod""  
->  Nested Loop  (cost=4.51..9.44 rows=1 width=40) (actual 
time=0.410..4.427 rows=41 
loops=1)""    
Join Filter: ((""inner"".""no"")::text = 
(""outer"".hat_no)::text)""    
->  Hash Join  (cost=4.51..8.09 rows=1 width=27) (actual 
time=0.384..1.036 rows=41 
loops=1)""  
Hash Cond: ((""outer"".durumu)::text = 
(""inner"".kod)::text)""  
->  Hash Join  (cost=2.25..5.80 rows=3 width=32) (actual 
time=0.193..0.652 rows=41 
loops=1)""    
Hash Cond: ((""outer"".ek_dev)::text = 
(""inner"".kod)::text)""    
->  Seq Scan on t_seferler s  (cost=0.00..3.21 rows=41 
width=37) (actual time=0

Re: [PERFORM] SQL Function Performance

[Michael Fuhr]

On Sun, Feb 12, 2006 at 10:25:28PM +0200, Adnan DURSUN wrote:
> My database has an SQL function. The result comes in 30-40 seconds
> when i use the SQL function. On the other hand; The result comes
> 300-400 milliseconds when i run the SQL statement. Any idea ??

Have you analyzed the tables?  If that's not the problem then could
you post the EXPLAIN ANALYZE output for the direct query and for a
prepared query?  For the prepared query do this:

PREPARE stmt (varchar, date, int4, varchar, varchar) AS SELECT ... ;

where "..." is the same SQL as in the function body, including the
numbered parameters ($1, $2, etc.).  To execute the query do this:

EXPLAIN ANALYZE EXECUTE stmt (...);

Where "..." is the same parameter list you'd pass to the function
(the same values you used in the direct query).

If you need to re-prepare the query then run "DEALLOCATE stmt"
before doing so.

-- 
Michael Fuhr

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] SQL Function Performance

[andrew]

If you have only recently analyzed the tables in the query, close your psql 
session (if that's what you were using) and then restart it. I've gotten burned 
by asking a query using the function, which I believe is when PG creates the 
plan for the function, and then making significant changes to the tables behind 
it (new index, bulk insert, etc.). By starting a new session, the function will 
be re-planned according to up to date statistics or using newly created indices.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[PERFORM] SQL Function Performance

[Adnan DURSUN]

  
  

  


  
 
  

              Hi 
all,
   
              My 
  database has an SQL function. The result comes in 30-40 seconds 
  when i use the SQL function. On the other hand; The result comes
          300-400 milliseconds when 
  i run the SQL statement. Any idea ?? My database is Postgresql 
  8.1.2..
          
              Function is 
  below :
   
  CREATE OR REPLACE FUNCTION fn_online_seferler_satis("varchar", date, 
  int4, "varchar", "varchar")  RETURNS SETOF record 
  AS$BODY$SELECT  (S.KALKIS_YERI||' '||S.VARIS_YERI||' 
  '||S.SAAT)::varchar AS SEFER_BILGI, sum((i.bilet_ucreti + 
  coalesce(i.police_ucreti,0)) - coalesce(i.int_artik_ucret,0)) as 
  top_satis, count(1)::int4 as top_koltuk   FROM 
  T_KOLTUK_ISLEM I, T_KOLTUK_SON_DURUM SD, T_LOKAL_PLAN 
  LP, W_SEFERLER S  WHERE I.FIRMA_NO = 
  SD.FIRMA_NO    AND I.HAT_NO = 
  SD.HAT_NO    AND I.SEFER_KOD = 
  SD.SEFER_KOD    AND I.PLAN_TARIHI = 
  SD.PLAN_TARIHI    AND I.BIN_YER_KOD = 
  SD.BIN_YER_KOD    AND I.KOLTUK_NO = 
  SD.KOLTUK_NO    AND I.KOD = 
  SD.ISLEM_KOD    AND SD.ISLEM = 
  'S'    AND LP.FIRMA_NO = 
  I.FIRMA_NO    AND LP.HAT_NO = 
  I.HAT_NO    AND LP.SEFER_KOD = 
  I.SEFER_KOD    AND LP.PLAN_TARIHI = 
  I.PLAN_TARIHI    AND LP.YER_KOD = 
  I.BIN_YER_KOD    AND I.FIRMA_NO = 
  $1    AND S.FIRMA_NO = LP.FIRMA_NO 
      AND S.HAT_NO = LP.HAT_NO    AND 
  S.KOD = LP.SEFER_KOD    AND S.IPTAL = 
  'H'    AND ((I.ISLEM_TARIHI =  $2 AND $5 = 'I') OR 
  (LP.KALKIS_TARIHI = $2 AND $5 = 'K'))    AND 
  (((LP.LOKAL_KOD = $3 AND $4 = 'K')) OR  ((I.ypt_lcl_kod = $3 AND $4 = 
  'I'))) GROUP BY S.KALKIS_YERI,S.VARIS_YERI,S.SAAT;
  $BODY$  LANGUAGE 'sql' VOLATILE;
   
  Adnan DURSUN
  ASRIN Bilişim Ltd.Şti
  Turkey
   
  

  


  
  
  

Re: [PERFORM] SQL Function performance

[Jim C. Nasby]

On Thu, Sep 29, 2005 at 10:54:58PM +0300, [EMAIL PROTECTED] wrote:
> Hi All,
>  
> I have a SQL function like :
>  
> CREATE OR REPLACE FUNCTION
> fn_get_yetkili_inisyer_listesi(int4, int4)
>   RETURNS SETOF kod_adi_liste_type AS
> $BODY$
>  SELECT Y.KOD,Y.ADI
>FROM T_YER Y
>   WHERE EXISTS (SELECT 1
> FROM T_GUZER G
>   WHERE (G.BIN_YER_KOD = $1 OR COALESCE($1,0)=0)
>  AND FN_FIRMA_ISVISIBLE(G.FIRMA_NO,$2) = 1
>  AND G.IN_YER_KOD = Y.KOD)
> AND Y.IPTAL = 'H';
> $BODY$
>   LANGUAGE 'sql' VOLATILE;
> 
> When i use like "SELECT * FROM
> fn_get_yetkili_inisyer_listesi(1, 3474)" and 
> planner result is  "Function Scan on
> fn_get_yetkili_inisyer_listesi  (cost=0.00..12.50 rows=1000
> width=36) (1 row) " and it runs very slow.
>  
> But when i use like 
> 
> "SELECT Y.KOD,Y.ADI
>  FROM T_YER Y
>WHERE EXISTS (SELECT 1
> FROM T_GUZER G
>   WHERE (G.BIN_YER_KOD
> = 1 OR COALESCE(1,0)=0)
>  AND FN_FIRMA_ISVISIBLE(G.FIRMA_NO,3474) = 1
>  AND G.IN_YER_KOD = Y.KOD)
>  AND Y.IPTAL = 'H';" 
> 
> planner result :
> 
> " 
>  QUERY PLAN
>  
> 
> -
>  Seq Scan on t_yer y  (cost=0.00..3307.79 rows=58 width=14)
>Filter: (((iptal)::text = 'H'::text) AND (subplan))
>SubPlan
>  ->  Index Scan using
> t_guzer_ucret_giris_performans_idx on t_guzer g  (cost
> =0.00..28.73 rows=1 width=0)
>Index Cond: ((bin_yer_kod = 1) AND (in_yer_kod =
> $0))
>Filter: (fn_firma_isvisible(firma_no, 3474) = 1)
> (6 rows)
> "
>   and it runs very fast.
> 
> Any idea ?

Need EXPLAIN ANALYZE.

I suspect this is due to a cached query plan. PostgreSQL will cache a
query plan for the SELECT the first time you run the function and that
plan will be re-used. Depending on what data you call the function with,
you could get a very different plan.

Also, you might do better with a JOIN instead of using EXISTS.  You can
also make this function STABLE instead of VOLATILE. Likewise, if
FN_FIRMA_ISVISIBLE can't change any data, you can also make it STABLE
which would likely improve the performance of the query. But neither of
these ideas would account for the difference between function
performance and raw query performance.

On a side note, if OR $1 IS NULL works that will be more readable (and
probably faster) than the OR COALESCE($1,0)=0.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[PERFORM] SQL Function performance

[adnandursun]

Hi All,
 
I have a SQL function like :
 
CREATE OR REPLACE FUNCTION
fn_get_yetkili_inisyer_listesi(int4, int4)
  RETURNS SETOF kod_adi_liste_type AS
$BODY$
 SELECT Y.KOD,Y.ADI
   FROM T_YER Y
  WHERE EXISTS (SELECT 1
FROM T_GUZER G
  WHERE (G.BIN_YER_KOD = $1 OR COALESCE($1,0)=0)
 AND FN_FIRMA_ISVISIBLE(G.FIRMA_NO,$2) = 1
 AND G.IN_YER_KOD = Y.KOD)
AND Y.IPTAL = 'H';
$BODY$
  LANGUAGE 'sql' VOLATILE;

When i use like "SELECT * FROM
fn_get_yetkili_inisyer_listesi(1, 3474)" and 
planner result is  "Function Scan on
fn_get_yetkili_inisyer_listesi  (cost=0.00..12.50 rows=1000
width=36) (1 row) " and it runs very slow.
 
But when i use like 

"SELECT Y.KOD,Y.ADI
 FROM T_YER Y
   WHERE EXISTS (SELECT 1
FROM T_GUZER G
  WHERE (G.BIN_YER_KOD
= 1 OR COALESCE(1,0)=0)
 AND FN_FIRMA_ISVISIBLE(G.FIRMA_NO,3474) = 1
 AND G.IN_YER_KOD = Y.KOD)
 AND Y.IPTAL = 'H';" 

planner result :

" 
 QUERY PLAN
 

-
 Seq Scan on t_yer y  (cost=0.00..3307.79 rows=58 width=14)
   Filter: (((iptal)::text = 'H'::text) AND (subplan))
   SubPlan
 ->  Index Scan using
t_guzer_ucret_giris_performans_idx on t_guzer g  (cost
=0.00..28.73 rows=1 width=0)
   Index Cond: ((bin_yer_kod = 1) AND (in_yer_kod =
$0))
   Filter: (fn_firma_isvisible(firma_no, 3474) = 1)
(6 rows)
"
  and it runs very fast.

Any idea ?

Adnan DURSUN
ASRIN Bilişim Hiz.Ltd.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly