Home > mailing lists

Thread: where clause + function, execution order

where clause + function, execution order

From

Sorin Dudui

Date:

11 November 2011, 14:38:57

Hi,

I have the following function:

===============================

CREATE OR REPLACE FUNCTION xxx(text)

RETURNS SETOF vvvvv AS

$BODY$

select a.x, a.y,

CASE

WHEN strpos($1,b.x) > 0

THEN b.x

ELSE NULL

END AS mp_hm

from a LEFT JOIN b ON a.id=b.id

$BODY$

LANGUAGE sql STABLE

COST 1000

ROWS 10000;

===============================

which I call as:

select * from xxx(‘test0|test1‘) where a.x = ‘value’

I am wondering when the where clause (a.x = ‘value’) is executed. After the select statement in the function finishes? Or is it appended at the select statement in the function?

Thank you,

Sorin

Re: where clause + function, execution order

From

Julius Tuskenis

Date:

11 November 2011, 14:54:14

Hello,

On 2011.11.11 17:38, Sorin Dudui wrote:

Hi,

I have the following function:

===============================
CREATE OR REPLACE FUNCTION xxx(text)
RETURNS SETOF vvvvv AS
$BODY$
select a.x, a.y,
    CASE
    WHEN strpos($1,b.x) > 0
   THEN b.x
    ELSE NULL
    END AS mp_hm
from a LEFT JOIN b ON a.id=b.id

                $BODY$
LANGUAGE sql STABLE
COST 1000
ROWS 10000;
===============================

which I call as:

select * from xxx(‘test0|test1‘) where a.x = ‘value’

You should get an error as there is no "a" in this statement...

I am wondering when the where clause (a.x = ‘value’) is executed. After the select statement in the function finishes? Or is it appended at the select statement in the function?

Function execute plan is prepared when creating it, so the "where" clause should check the function result not altering its execution..

--
Julius Tuskenis
Head of the programming department
UAB nSoft
mob. +37068233050

Re: where clause + function, execution order

From

Richard Huxton

Date:

11 November 2011, 15:00:06

On 11/11/11 15:54, Julius Tuskenis wrote:
> On 2011.11.11 17:38, Sorin Dudui wrote:
>> I have the following function:
>>
>> CREATE OR REPLACE FUNCTION xxx(text)
[snip]
>> LANGUAGE sql STABLE

> Function execute plan is prepared when creating it, so the "where"
> clause should check the function result not altering its execution..

Not true for SQL functions. They can be inlined, but I'm not sure if
this one will be.

What does EXPLAIN ANALYSE show for this query?


--
   Richard Huxton
   Archonet Ltd

Re: where clause + function, execution order

From

Tom Lane

Date:

11 November 2011, 15:00:24

Sorin Dudui <sd@wigeogis.com> writes:
> I am wondering when the where clause (a.x = 'value') is executed. After the select statement in the function
finishes?Or is it appended at the select statement in the function? 

EXPLAIN is your friend ...

In this case the function looks inline-able, so reasonably recent
versions of PG should do what you want.

            regards, tom lane

Re: where clause + function, execution order

From

Sorin Dudui

Date:

11 November 2011, 15:28:13

Hi,

this is the EXPLAIN ANALYSE output:


"Merge Left Join  (cost=0.00..2820.34 rows=23138 width=777) (actual time=0.049..317.935 rows=26809 loops=1)"
"  Merge Cond: ((a.admin10)::text = (b.link_id)::text)"
"  ->  Index Scan using admin_lookup_admin10 on admin_lookup a  (cost=0.00..845.04 rows=5224 width=742) (actual
time=0.015..40.263rows=8100 loops=1)"
 
"        Filter: (((admin40)::text <> '-1'::text) AND (((admin40)::text = 'ITA10'::text) OR ((admin40)::text =
'ITA15'::text)OR ((admin40)::text = 'ITA19'::text) OR ((admin40)::text = 'ITA04'::text) OR ((admin40)::text =
'ITA09'::text)OR ((admin40)::text = 'ITA03'::text) OR ((admin40)::text = 'ITA08'::text) OR ((admin40)::text =
'ITA17'::text)OR ((admin40)::text = 'ITA02'::text) OR ((admin40)::text = 'ITA18'::text) OR ((admin40)::text =
'ITA01'::text)OR ((admin40)::text = 'ITA20'::text) OR ((admin40)::text = 'ITA13'::text) OR ((admin40)::text =
'ITA11'::text)OR ((admin40)::text = 'ITA14'::text) OR ((admin40)::text = 'ITA16'::text) OR ((admin40)::text =
'ITA07'::text)OR ((admin40)::text = 'ITA06'::text) OR ((admin40)::text = 'ITA12'::text) OR ((admin40)::text =
'ITA05'::text)))"
"  ->  Index Scan using reg_data_a08id_copy on registrations_data b  (cost=0.00..1496.89 rows=24174 width=45) (actual
time=0.008..70.408rows=24174 loops=1)"
 
"Total runtime: 372.765 ms"


Regards,
Sorin

-----Ursprüngliche Nachricht-----
Von: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] Im Auftrag von Richard
Huxton
Gesendet: Freitag, 11. November 2011 17:00
An: Julius Tuskenis
Cc: pgsql-performance@postgresql.org
Betreff: Re: [PERFORM] where clause + function, execution order

On 11/11/11 15:54, Julius Tuskenis wrote:
> On 2011.11.11 17:38, Sorin Dudui wrote:
>> I have the following function:
>>
>> CREATE OR REPLACE FUNCTION xxx(text)
[snip]
>> LANGUAGE sql STABLE

> Function execute plan is prepared when creating it, so the "where"
> clause should check the function result not altering its execution..

Not true for SQL functions. They can be inlined, but I'm not sure if this one will be.

What does EXPLAIN ANALYSE show for this query?


-- 
   Richard Huxton
   Archonet Ltd

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

Re: where clause + function, execution order

From

Richard Huxton

Date:

11 November 2011, 15:55:54

On 11/11/11 16:28, Sorin Dudui wrote:
> Hi,
>
> this is the EXPLAIN ANALYSE output:
>
>
> "Merge Left Join  (cost=0.00..2820.34 rows=23138 width=777) (actual time=0.049..317.935 rows=26809 loops=1)"
> "  Merge Cond: ((a.admin10)::text = (b.link_id)::text)"
> "  ->   Index Scan using admin_lookup_admin10 on admin_lookup a  (cost=0.00..845.04 rows=5224 width=742) (actual
time=0.015..40.263rows=8100 loops=1)" 
> "        Filter: (((admin40)::text<>  '-1'::text) AND (((admin40)::text = 'ITA10'::text) OR ((admin40)::text =
'ITA15'::text)OR ((admin40)::text = 'ITA19'::text) OR ((admin40)::text = 'ITA04'::text) OR ((admin40)::text =
'ITA09'::text)OR ((admin40)::text = 'ITA03'::text) OR ((admin40)::text = 'ITA08'::text) OR ((admin40)::text =
'ITA17'::text)OR ((admin40)::text = 'ITA02'::text) OR ((admin40)::text = 'ITA18'::text) OR ((admin40)::text =
'ITA01'::text)OR ((admin40)::text = 'ITA20'::text) OR ((admin40)::text = 'ITA13'::text) OR ((admin40)::text =
'ITA11'::text)OR ((admin40)::text = 'ITA14'::text) OR ((admin40)::text = 'ITA16'::text) OR ((admin40)::text =
'ITA07'::text)OR ((admin40)::text = 'ITA06'::text) OR ((admin40)::text = 'ITA12'::text) OR ((admin40)::text =
'ITA05'::text)))"
> "  ->   Index Scan using reg_data_a08id_copy on registrations_data b  (cost=0.00..1496.89 rows=24174 width=45)
(actualtime=0.008..70.408 rows=24174 loops=1)" 
> "Total runtime: 372.765 ms"

That certainly looks like it's been inlined. You are testing for
"ITA10", "ITA15" etc outside the function-call, no? It's pushing those
tests down, using index "admin_lookup_admin10" to test for them then
joining afterwards.

--
   Richard Huxton
   Archonet Ltd