From: Joe Conway <mail@joeconway.com>
To: Iain <iain@mst.co.jp>
Cc: Christopher Browne <cbbrowne@acm.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Data Warehouse Reevaluation - MySQL vs Postgres --
Date: Thu, 16 Sep 2004 08:36:31 -0700
Message-ID: <4149B2FF.6030808@joeconway.com> (raw)
In-Reply-To: <00fb01c49bcc$c08dd210$7201a8c0@mst1x5r347kymb>
References: <20040914223333.GL56059@decibel.org>
<NOEFLCFHBPDAFHEIPGBOEEHPCEAA.simon@2ndquadrant.com>
<60k6uw1cqa.fsf@dev6.int.libertyrms.info>
<4147C560.9060702@joeconway.com>
<m34qm06osz.fsf@wolfe.cbbrowne.com>
<414921F9.400@joeconway.com>
<00fb01c49bcc$c08dd210$7201a8c0@mst1x5r347kymb>
Iain wrote:
> Joe's example wasn't excluding partions, as he didn't use a predicated UNION
> ALL view to select from. His queries use an indexed column that allow the
> various partitions to be probed at low cost, and he was satisfied wth that.
Right.
> My point in my previous post was that you could still do all that that if
> you wanted to, by building the predicated view with UNION ALL of each of the
> child tables.
Right. It doesn't look that much different:
create or replace view foo_vw as
select * from foo_2004_01 where f2 >= '2004-jan-01' and f2 <= '2004-jan-31'
union all
select * from foo_2004_02 where f2 >= '2004-feb-01' and f2 <= '2004-feb-29'
union all
select * from foo_2004_03 where f2 >= '2004-mar-01' and f2 <= '2004-mar-31'
;
-- needed just for illustration since these are toy tables
set enable_seqscan to false;
explain analyze select * from foo_vw where f2 = '2004-feb-15';
QUERY PLAN
----------------------------------------------------------------------------------
Subquery Scan foo_vw (cost=0.00..14.54 rows=3 width=16) (actual
time=0.022..0.027 rows=1 loops=1)
-> Append (cost=0.00..14.51 rows=3 width=16) (actual
time=0.019..0.022 rows=1 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..4.84 rows=1
width=16) (actual time=0.004..0.004 rows=0 loops=1)
-> Index Scan using foo_2004_01_idx2 on foo_2004_01
(cost=0.00..4.83 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=1)
Index Cond: ((f2 >= '2004-01-01'::date) AND (f2 <=
'2004-01-31'::date) AND (f2 = '2004-02-15'::date))
-> Subquery Scan "*SELECT* 2" (cost=0.00..4.84 rows=1
width=16) (actual time=0.013..0.015 rows=1 loops=1)
-> Index Scan using foo_2004_02_idx2 on foo_2004_02
(cost=0.00..4.83 rows=1 width=16) (actual time=0.009..0.010 rows=1 loops=1)
Index Cond: ((f2 >= '2004-02-01'::date) AND (f2 <=
'2004-02-29'::date) AND (f2 = '2004-02-15'::date))
-> Subquery Scan "*SELECT* 3" (cost=0.00..4.84 rows=1
width=16) (actual time=0.001..0.001 rows=0 loops=1)
-> Index Scan using foo_2004_03_idx2 on foo_2004_03
(cost=0.00..4.83 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: ((f2 >= '2004-03-01'::date) AND (f2 <=
'2004-03-31'::date) AND (f2 = '2004-02-15'::date))
Total runtime: 0.188 ms
(12 rows)
regression=# explain analyze select * from foo where f2 = '2004-feb-15';
QUERY PLAN
----------------------------------------------------------------------------------
Result (cost=100000000.00..100000073.70 rows=20 width=16) (actual
time=0.059..0.091 rows=1 loops=1)
-> Append (cost=100000000.00..100000073.70 rows=20 width=16)
(actual time=0.055..0.086 rows=1 loops=1)
-> Seq Scan on foo (cost=100000000.00..100000022.50 rows=5
width=16) (actual time=0.001..0.001 rows=0 loops=1)
Filter: (f2 = '2004-02-15'::date)
-> Index Scan using foo_2004_01_idx2 on foo_2004_01 foo
(cost=0.00..17.07 rows=5 width=16) (actual time=0.045..0.045 rows=0 loops=1)
Index Cond: (f2 = '2004-02-15'::date)
-> Index Scan using foo_2004_02_idx2 on foo_2004_02 foo
(cost=0.00..17.07 rows=5 width=16) (actual time=0.008..0.009 rows=1 loops=1)
Index Cond: (f2 = '2004-02-15'::date)
-> Index Scan using foo_2004_03_idx2 on foo_2004_03 foo
(cost=0.00..17.07 rows=5 width=16) (actual time=0.029..0.029 rows=0 loops=1)
Index Cond: (f2 = '2004-02-15'::date)
Total runtime: 0.191 ms
(11 rows)
The main difference being that the view needs to be recreated every time
a table is added or dropped, whereas with the inherited tables method
that isn't needed.
Joe
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: mail@joeconway.com, iain@mst.co.jp, cbbrowne@acm.org
Subject: Re: Data Warehouse Reevaluation - MySQL vs Postgres --
In-Reply-To: <4149B2FF.6030808@joeconway.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