From: Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
To: pgsql-performance@postgresql.org
Subject: Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables
Date: Tue, 14 Sep 2004 11:07:59 +0200
Message-ID: <opsea7nlytcq72hf@musicbox> (raw)
In-Reply-To: <20040914073943.95193.qmail@web41502.mail.yahoo.com>
References: <20040914073943.95193.qmail@web41502.mail.yahoo.com>
Performance hint :
For static data, do not normalize too much.
For instance if you have a row which can be linked to several other rows,
you can do this :
create table parents (
id serial primary key,
values... )
create table children (
id serial primary key,
parent_id references parents(id),
integer slave_value )
Or you can do this, using an array :
create table everything (
id serial primary key,
integer[] children_values,
values... )
Pros :
No Joins. Getting the list of chilndren_values from table everything is
just a select.
On an application with several million rows, a query lasting 150 ms with
a Join takes 30 ms with an array.
You can build the arrays from normalized tables by using an aggregate
function.
You can index the array elements with a GIST index...
Cons :
No joins, thus your queries are a little bit limited ; problems if the
array is too long ;
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: lists@boutiquenumerique.com
Subject: Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables
In-Reply-To: <opsea7nlytcq72hf@musicbox>
* 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