Re: where clause + function, execution order

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(at)postgresql(dot)org [mailto:pgsql-performance-owner(at)postgresql(dot)org] Im Auftrag von Richard Huxton
Gesendet: Freitag, 11. November 2011 17:00
An: Julius Tuskenis
Cc: pgsql-performance(at)postgresql(dot)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(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance