To: pgsql-performance@postgresql.org
Subject: Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables
References: <20040914073943.95193.qmail@web41502.mail.yahoo.com>
Message-ID: <opsea7nlytcq72hf@musicbox>
Date: Tue, 14 Sep 2004 11:07:59 +0200
From: =?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?=
	<lists@boutiquenumerique.com>
Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?=
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
In-Reply-To: <20040914073943.95193.qmail@web41502.mail.yahoo.com>
User-Agent: Opera M2/7.53 (Linux, build 737)


	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 ;