pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Guillaume Smet <guillaume.smet@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Workaround for cross column stats dependency
Date: Wed, 23 Jan 2008 10:05:53 +0100
Message-ID: <1d4e0c10801230105x696dad3bod0c1d7b80da96f8e@mail.gmail.com> (raw )
In-Reply-To: <1d4e0c10801221802y38dfdc60pde9092ff5ea295a4@mail.gmail.com >
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
view thread (4+ messages)
Message-ID: <1d4e0c10801230105x696dad3bod0c1d7b80da96f8e@mail.gmail.com>
Permalink: ../1d4e0c10801230105x696dad3bod0c1d7b80da96f8e@mail.gmail.com/
Also on: postgresql.org/message-id/1d4e0c10801230105x696dad3bod0c1d7b80da96f8e@mail.gmail.com
copy link · copy postgr.es
reply 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: guillaume.smet@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: Workaround for cross column stats dependency
In-Reply-To: <1d4e0c10801230105x696dad3bod0c1d7b80da96f8e@mail.gmail.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