Re: where clause + function, execution order

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