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 444233A2B8D for ; Thu, 20 Jan 2005 16:32:01 +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 23071-10 for ; Thu, 20 Jan 2005 16:31:57 +0000 (GMT) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by svr1.postgresql.org (Postfix) with ESMTP id 7CD633A4847 for ; Thu, 20 Jan 2005 16:31:56 +0000 (GMT) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1CrfDs-0007NI-00; Thu, 20 Jan 2005 11:31:52 -0500 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> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 20 Jan 2005 11:31:52 -0500 Message-ID: <871xcgdptz.fsf@stark.xeocode.com> Lines: 24 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.057 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200501/314 X-Sequence-Number: 10004 "Matt Casters" writes: > I've been reading up on partitioned tabes on pgsql, will the performance > benefit will be comparable to Oracle partitioned tables? Postgres doesn't have any built-in support for partitioned tables. You can do it the same way people did it on Oracle up until 8.0 which is by creating views of UNIONs or using inherited tables. The main advantage of partitioned tables is being able to load and drop data in large chunks instantaneously. This avoids having to perform large deletes and then having to vacuum huge tables to recover the space. However in Postgres you aren't going to get most of the performance advantage of partitions in your query plans. The Oracle planner can prune partitions it knows aren't relevant to the query to avoid having to search through them. This can let it get the speed of a full table scan without the disadvantage of having to read irrelevant tuples. Postgres is sometimes going to be forced to either do a much slower index scan or read tables that aren't relevant. -- greg