Matt Casters wrote:
My questions to the list are: has this sort of thing been attempted before? If
so, what where the
performance results compared to Oracle?
I've been reading up on partitioned tabes on pgsql, will the performance
benefit will be
comparable to Oracle partitioned tables?
What are the gotchas?
Should I be testing on 8 or the 7 version?
While I didn't find any documents immediately, are there any fine manuals to
read on data
warehouse performance tuning on PostgreSQL?
Some of the previous postings on this list discuss various methods for
doing partitioning (UNION and INHERIT), as well as the use of partial
indexes - see the thread titled : 'Data Warehouse Reevaluation - MySQL
vs Postgres -- merge tables'.
Unfortunately none of these work well for a standard 'star' because :
i) all conditions are on the dimension tables, and
ii) the optimizer can eliminate 'partition' tables only on the basis of
*constant* conditions, and the resulting implied restrictions caused
by the join to the dimension table(s) are not usable for this.
So I think to get it to work well some violence to your 'star' may be
required (e.g. adding constant columns to 'fact' tables to aid the
optimizer, plus rewriting queries to include conditions on the added
One other gotcha is that Pg cannot do index only access, which can hurt.
However it may be possibly to get good performance using CLUSTER on the
fact tables (or just loading them in a desirable order) plus using
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend