pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedwhere clause + function, execution order
6+ messages / 4 participants
[nested] [flat]
* where clause + function, execution order
@ 2011-11-11 15:38 Sorin Dudui <sd@wigeogis.com>
2011-11-11 15:54 ` Re: where clause + function, execution order Julius Tuskenis <julius@nsoft.lt>
2011-11-11 16:00 ` Re: where clause + function, execution order Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 6+ messages in thread
From: Sorin Dudui @ 2011-11-11 15:38 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: where clause + function, execution order
2011-11-11 15:38 where clause + function, execution order Sorin Dudui <sd@wigeogis.com>
@ 2011-11-11 15:54 ` Julius Tuskenis <julius@nsoft.lt>
2011-11-11 15:59 ` Re: where clause + function, execution order Richard Huxton <dev@archonet.com>
1 sibling, 1 reply; 6+ messages in thread
From: Julius Tuskenis @ 2011-11-11 15:54 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: where clause + function, execution order
2011-11-11 15:38 where clause + function, execution order Sorin Dudui <sd@wigeogis.com>
2011-11-11 15:54 ` Re: where clause + function, execution order Julius Tuskenis <julius@nsoft.lt>
@ 2011-11-11 15:59 ` Richard Huxton <dev@archonet.com>
2011-11-11 16:28 ` Re: where clause + function, execution order Sorin Dudui <sd@wigeogis.com>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Huxton @ 2011-11-11 15:59 UTC (permalink / raw)
To: Julius Tuskenis <julius@nsoft.lt>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: where clause + function, execution order
2011-11-11 15:38 where clause + function, execution order Sorin Dudui <sd@wigeogis.com>
2011-11-11 15:54 ` Re: where clause + function, execution order Julius Tuskenis <julius@nsoft.lt>
2011-11-11 15:59 ` Re: where clause + function, execution order Richard Huxton <dev@archonet.com>
@ 2011-11-11 16:28 ` Sorin Dudui <sd@wigeogis.com>
2011-11-11 16:55 ` Re: where clause + function, execution order Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 6+ messages in thread
From: Sorin Dudui @ 2011-11-11 16:28 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; Julius Tuskenis <julius@nsoft.lt>; +Cc: pgsql-performance
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.263 rows=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.408 rows=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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: where clause + function, execution order
2011-11-11 15:38 where clause + function, execution order Sorin Dudui <sd@wigeogis.com>
2011-11-11 15:54 ` Re: where clause + function, execution order Julius Tuskenis <julius@nsoft.lt>
2011-11-11 15:59 ` Re: where clause + function, execution order Richard Huxton <dev@archonet.com>
2011-11-11 16:28 ` Re: where clause + function, execution order Sorin Dudui <sd@wigeogis.com>
@ 2011-11-11 16:55 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 6+ messages in thread
From: Richard Huxton @ 2011-11-11 16:55 UTC (permalink / raw)
To: Sorin Dudui <sd@wigeogis.com>; +Cc: Julius Tuskenis <julius@nsoft.lt>; pgsql-performance
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.263 rows=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.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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: where clause + function, execution order
2011-11-11 15:38 where clause + function, execution order Sorin Dudui <sd@wigeogis.com>
@ 2011-11-11 16:00 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2011-11-11 16:00 UTC (permalink / raw)
To: Sorin Dudui <sd@wigeogis.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2011-11-11 16:55 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-11-11 15:38 where clause + function, execution order Sorin Dudui <sd@wigeogis.com>
2011-11-11 15:54 ` Julius Tuskenis <julius@nsoft.lt>
2011-11-11 15:59 ` Richard Huxton <dev@archonet.com>
2011-11-11 16:28 ` Sorin Dudui <sd@wigeogis.com>
2011-11-11 16:55 ` Richard Huxton <dev@archonet.com>
2011-11-11 16:00 ` Tom Lane <tgl@sss.pgh.pa.us>
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