X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2843F3A2D3A for ; Thu, 20 Jan 2005 21:40:28 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 83236-04 for ; Thu, 20 Jan 2005 21:40:16 +0000 (GMT) Received: from mail.coretech.co.nz (coretech.co.nz [202.36.204.41]) by svr1.postgresql.org (Postfix) with ESMTP id 51DCE3A4945 for ; Thu, 20 Jan 2005 21:40:16 +0000 (GMT) Received: (qmail 26858 invoked from network); 20 Jan 2005 21:40:15 -0000 Received: from 218-101-14-86.paradise.net.nz (HELO ?192.168.1.11?) (218.101.14.86) by 0 with SMTP; 20 Jan 2005 21:40:15 -0000 Message-ID: <41F02629.3040305@coretech.co.nz> Date: Fri, 21 Jan 2005 10:44:09 +1300 From: Mark Kirkwood User-Agent: Mozilla/5.0 (X11; U; FreeBSD i386; en-US; rv:1.7.2) Gecko/20041016 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Matt.Casters@advalvas.be Cc: pgsql-performance@postgresql.org Subject: Re: References: <16153.193.190.212.113.1106213675.squirrel@193.190.212.113> In-Reply-To: <16153.193.190.212.113.1106213675.squirrel@193.190.212.113> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200501/348 X-Sequence-Number: 10038 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