Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

Joe Conway Wed, 15 Sep 2004 21:08:01 -0700

Iain wrote:

That's exactly what we're doing, but using inherited tables instead of a
union view. With inheritance, there is no need to rebuild the view each
time a table is added or removed. Basically, in our application, tables
are partitioned by either month or week, depending on the type of data
involved, and queries are normally date qualified.


That sounds interesting. I have to admit that I havn't touched iheritance in
pg at all yet so I find it hard to imagine how this would work. If you have
a chance, would you mind elaborating on it just a little?


OK, see below:
=====================

create table foo(f1 int, f2 date, f3 float8);

create table foo_2004_01() inherits (foo);
create table foo_2004_02() inherits (foo);
create table foo_2004_03() inherits (foo);

create index foo_2004_01_idx1 on foo_2004_01(f2);
create index foo_2004_02_idx1 on foo_2004_02(f2);
create index foo_2004_03_idx1 on foo_2004_03(f2);

insert into foo_2004_02 values(1,'2004-feb-15',3.14);


 -- needed just for illustration since these are toy tables
set enable_seqscan to false;
explain analyze select * from foo where f2 = '2004-feb-15';

create table foo_2004_04() inherits (foo);
create index foo_2004_04_idx1 on foo_2004_04(f2);

explain analyze select * from foo where f2 = '2004-feb-15';

QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------ Result (cost=100000000.00..100000078.38 rows=21 width=16) (actual time=0.052..0.176 rows=1 loops=1) -> Append (cost=100000000.00..100000078.38 rows=21 width=16) (actual time=0.041..0.159 rows=1 loops=1) -> Seq Scan on foo (cost=100000000.00..100000022.50 rows=5 width=16) (actual time=0.004..0.004 rows=0 loops=1) Filter: (f2 = '2004-02-15'::date) -> Index Scan using foo_2004_01_idx1 on foo_2004_01 foo (cost=0.00..17.07 rows=5 width=16) (actual time=0.012..0.012 rows=0 loops=1) Index Cond: (f2 = '2004-02-15'::date) -> Index Scan using foo_2004_02_idx1 on foo_2004_02 foo (cost=0.00..4.68 rows=1 width=16) (actual time=0.016..0.022 rows=1 loops=1) Index Cond: (f2 = '2004-02-15'::date) -> Index Scan using foo_2004_03_idx1 on foo_2004_03 foo (cost=0.00..17.07 rows=5 width=16) (actual time=0.008..0.008 rows=0 loops=1) Index Cond: (f2 = '2004-02-15'::date) -> Index Scan using foo_2004_04_idx1 on foo_2004_04 foo (cost=0.00..17.07 rows=5 width=16) (actual time=0.095..0.095 rows=0 loops=1) Index Cond: (f2 = '2004-02-15'::date) Total runtime: 0.443 ms (13 rows)

For loading data, we COPY into foo, and have a trigger that redirects the rows to the appropriate partition.

Notice that the partitions which do not contain any data of interest are still probed for data, but since they have none it is very quick. In a real life example I got the following results just this afternoon:

 - aggregate row count = 471,849,665
 - total number inherited tables = 216
   (many are future dated and therefore contain no data)
 - select one month's worth of data for one piece of equipment by serial
   number (49,257 rows) = 526.015 ms

Not too bad -- quick enough for my needs. BTW, this is using NFS mounted storage (NetApp NAS).

Joe

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

Reply via email to