Re: Workaround for cross column stats dependency

On Jan 23, 2008 3:02 AM, Guillaume Smet <guillaume(dot)smet(at)gmail(dot)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