Matt Casters wrote:
> Hi,
>
> 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
columns).
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
partial indexes.
regards
Mark
