From: Richard Huxton <dev@archonet.com>
To: Sorin Dudui <sd@wigeogis.com>
Cc: Julius Tuskenis <julius@nsoft.lt>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: where clause + function, execution order
Date: Fri, 11 Nov 2011 16:55:44 +0000
Message-ID: <4EBD5390.5060804@archonet.com> (raw)
In-Reply-To: <13DE38ECCB7CE74890E7150A7CCB610D3642DD75@exc1.wigeo.lan>
References: <13DE38ECCB7CE74890E7150A7CCB610D3642DD47@exc1.wigeo.lan>
<4EBD4518.5070508@nsoft.lt>
<4EBD4675.2060604@archonet.com>
<13DE38ECCB7CE74890E7150A7CCB610D3642DD75@exc1.wigeo.lan>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dev@archonet.com, sd@wigeogis.com, julius@nsoft.lt
Subject: Re: where clause + function, execution order
In-Reply-To: <4EBD5390.5060804@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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