DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=rqhBjPhGM+uM3UDgAT7z0ojxpZ22RX5XcF6qLrob9QUvIARInLNSAns3gsiyAJIANNcUS/JrpG7GoZaCty5bw2ys0wbHHMPdSZhjP5SBV0P1IONZTWw9hJFQxgvRSIZKC2lBFEfUMdSkpZQFJN6nq2IJkIiJBmjVGwdJvK7JAYI=
Message-ID: <1d4e0c10801230105x696dad3bod0c1d7b80da96f8e@mail.gmail.com>
Date: Wed, 23 Jan 2008 10:05:53 +0100
From: "Guillaume Smet" <guillaume.smet@gmail.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Subject: Re: Workaround for cross column stats dependency
Cc: pgsql-performance <pgsql-performance@postgresql.org>
In-Reply-To: <1d4e0c10801221802y38dfdc60pde9092ff5ea295a4@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <1d4e0c10801221657s6b345fcdqd1c0b3f20e519316@mail.gmail.com>
	<10869.1201052593@sss.pgh.pa.us>
	<1d4e0c10801221802y38dfdc60pde9092ff5ea295a4@mail.gmail.com>

On Jan 23, 2008 3:02 AM, Guillaume Smet <guillaume.smet@gmail.com> wrote:
> I'll post my results tomorrow morning.

It works perfectly well:
cityvox_prod=# CREATE OR REPLACE FUNCTION
getTypesLieuFromTheme(codeTheme text) returns text[] AS
$f$
SELECT ARRAY(SELECT codetylieu::text FROM rubtylieu WHERE codeth = $1);
$f$ LANGUAGE SQL IMMUTABLE;
CREATE FUNCTION

cityvox_prod=# EXPLAIN ANALYZE SELECT vq.codequar, vq.liblong, vq.libcourt
FROM lieu l, vilquartier vq, genrelieu gl, lieugelieu lgl
WHERE l.codequar = vq.codequar AND l.dfinvalidlieu is null AND
vq.codevil = 'MUC' AND lgl.numlieu = l.numlieu AND lgl.codegelieu =
gl.codegelieu
AND gl.codetylieu = ANY(getTypesLieuFromTheme('RES'))
GROUP BY vq.codequar, vq.liblong, vq.libcourt, vq.flagintramuros
ORDER BY vq.flagintramuros, vq.liblong;

         QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=5960.02..5960.08 rows=26 width=43) (actual
time=7.467..7.475 rows=13 loops=1)
   Sort Key: vq.flagintramuros, vq.liblong
   Sort Method:  quicksort  Memory: 26kB
   ->  HashAggregate  (cost=5959.15..5959.41 rows=26 width=43) (actual
time=7.421..7.428 rows=13 loops=1)
         ->  Hash Join  (cost=7.32..5944.52 rows=1463 width=43)
(actual time=0.241..7.212 rows=167 loops=1)
               Hash Cond: ((lgl.codegelieu)::text = (gl.codegelieu)::text)
               ->  Nested Loop  (cost=0.00..5898.00 rows=6552
width=47) (actual time=0.038..6.354 rows=973 loops=1)
                     ->  Nested Loop  (cost=0.00..4585.64 rows=3845
width=47) (actual time=0.031..1.959 rows=630 loops=1)
                           ->  Index Scan using
idx_vilquartier_codevil on vilquartier vq  (cost=0.00..34.06 rows=47
width=43) (actual time=0.015..0.047 rows=47 loops=1)
                                 Index Cond: ((codevil)::text = 'MUC'::text)
                           ->  Index Scan using idx_test on lieu l
(cost=0.00..95.53 rows=105 width=9) (actual time=0.008..0.024 rows=13
loops=47)
                                 Index Cond: ((l.codequar)::text =
(vq.codequar)::text)
                     ->  Index Scan using
idx_lieugelieu_numlieu_principal on lieugelieu lgl  (cost=0.00..0.32
rows=2 width=8) (actual time=0.003..0.004 rows=2 loops=630)
                           Index Cond: (lgl.numlieu = l.numlieu)
               ->  Hash  (cost=6.22..6.22 rows=88 width=4) (actual
time=0.146..0.146 rows=88 loops=1)
                     ->  Bitmap Heap Scan on genrelieu gl
(cost=2.23..6.22 rows=88 width=4) (actual time=0.022..0.075 rows=88
loops=1)
                           Recheck Cond: ((codetylieu)::text = ANY
('{RES}'::text[]))
                           ->  Bitmap Index Scan on ind_genrelieu2
(cost=0.00..2.21 rows=88 width=0) (actual time=0.016..0.016 rows=88
loops=1)
                                 Index Cond: ((codetylieu)::text = ANY
('{RES}'::text[]))
 Total runtime: 7.558 ms

It seems like a good tip to keep in mind.

Thanks for your help.

--
Guillaume