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 6E7203A4A45 for ; Thu, 20 Jan 2005 18:51:55 +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 57302-07 for ; Thu, 20 Jan 2005 18:51:38 +0000 (GMT) Received: from priv-edtnes56.telusplanet.net (outbound01.telus.net [199.185.220.220]) by svr1.postgresql.org (Postfix) with ESMTP id 34F143A4954 for ; Thu, 20 Jan 2005 18:50:56 +0000 (GMT) Received: from localhost ([199.185.220.240]) by priv-edtnes56.telusplanet.net (InterMail vM.6.01.04.00 201-2131-118-20041027) with ESMTP id <20050120185055.TJAT16962.priv-edtnes56.telusplanet.net@localhost>; Thu, 20 Jan 2005 11:50:55 -0700 Received: from 209.17.183.249 ( [209.17.183.249]) as user a3a18850@192.168.200.1 by webmail.telus.net with HTTP; Thu, 20 Jan 2005 10:50:55 -0800 Message-ID: <1106247055.41effd8f837c4@webmail.telus.net> Date: Thu, 20 Jan 2005 10:50:55 -0800 From: Mischa To: Greg Stark Cc: pgsql-performance@postgresql.org Subject: Re: References: <16153.193.190.212.113.1106213675.squirrel@193.190.212.113> <871xcgdptz.fsf@stark.xeocode.com> In-Reply-To: <871xcgdptz.fsf@stark.xeocode.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit User-Agent: Internet Messaging Program (IMP) 3.1-cvs X-Originating-IP: 209.17.183.249 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.426 tagged_above=0 required=5 tests=DNS_FROM_RFC_POST, FORGED_RCVD_HELO X-Spam-Level: * X-Archive-Number: 200501/369 X-Sequence-Number: 10059 No support for partitioned tables? Perhaps in name ... but I use a time-based "partition" tables that inherit from a base table; new partitions are "placed" (moved) round-robin on a set of drives. Somewhat manual, but if you really need a solution now, it works. Quoting Greg Stark : > > "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 > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > -- "Dreams come true, not free."