From: Tobias Brox <tobias@nordicbet.com>
To: pgsql-performance@postgresql.org
Subject: extract(field from timestamp) vs date dimension
Date: Tue, 23 Jan 2007 13:49:37 +0100
Message-ID: <20070123124937.GA13122@oppetid.no> (raw)
Does anyone have experience with using postgres for data warehousing?
Right, I saw one post suggestion to use mysql for a mostly read-only
database ... but anyway, I think it's not a question to change the
database platform for this project, at least not today ;-)
Ralph Kimball seems to be some kind of guru on data warehousing, and
in his books he's strongly recommending to have a date dimension -
simply a table describing all dates in the system, and having
attributes for what day of the week it is, month, day of the month,
week number, bank holiday, anything special, etc. Well, it does make
sense if adding lots of information there that cannot easily be pulled
out from elsewhere - but as for now, I'm mostly only interessted in
grouping turnover/profit by weeks/months/quarters/years/weekdays. It
seems so much bloated to store this information, my gut feeling tells it
should be better to generate them on the fly. Postgres even allows to
create an index on an expression.
The question is ... I'm curious about what would yield the highest
performance - when choosing between:
select extract(week from created), ...
from some_table
where ...
group by extract(week from created), ...
sort by extract(week from created), ...
and:
select date_dim.week_num, ...
from some_table join date_dim ...
where ...
group by date_dim.week_num, ...
sort by date_dim, week_num, ...
The date_dim table would eventually cover ~3 years of operation, that
is less than 1000 rows.
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: tobias@nordicbet.com
Subject: Re: extract(field from timestamp) vs date dimension
In-Reply-To: <20070123124937.GA13122@oppetid.no>
* 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