Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables - Mailing list pgsql-performance

    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 ;