Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 7B5491337B85 for ; Thu, 28 Oct 2010 14:31:59 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 88629-09 for ; Thu, 28 Oct 2010 17:31:51 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from spinlock.commandprompt.com (host-215.commandprompt.net [207.173.203.215]) by mail.postgresql.org (Postfix) with ESMTP id 8F99B1337B7F for ; Thu, 28 Oct 2010 14:31:51 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by spinlock.commandprompt.com (Postfix) with ESMTP id F0DE6348017; Thu, 28 Oct 2010 10:31:42 -0700 (PDT) X-Virus-Scanned: Debian amavisd-new at commandprompt.com Received: from spinlock.commandprompt.com ([127.0.0.1]) by localhost (spinlock.commandprompt.com.commandprompt.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 6vSilGzHITh1; Thu, 28 Oct 2010 10:31:35 -0700 (PDT) Received: from [192.168.1.105] (unknown [75.142.155.118]) by spinlock.commandprompt.com (Postfix) with ESMTPSA id 7E5E3348015; Thu, 28 Oct 2010 10:31:35 -0700 (PDT) Subject: Re: partitioning question 1 From: "Joshua D. Drake" Reply-To: jd@commandprompt.com To: Ben Cc: pgsql-performance@postgresql.org In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" Organization: Command Prompt, Inc. Date: Thu, 28 Oct 2010 10:31:32 -0700 Message-ID: <1288287092.22359.23.camel@jd-desktop> Mime-Version: 1.0 X-Mailer: Evolution 2.30.3 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201010/551 X-Sequence-Number: 40969 On Thu, 2010-10-28 at 09:36 -0700, Ben wrote: > hello -- > > my last email was apparently too long to respond to so i'll split it up into shorter pieces. my first question : > > my understanding of how range partitioning and constraint exclusion works leads me to believe that it does not buy any query performance that a clustered index doesn't already give you -- the advantages are all in maintainability. an index is able to eliminate pages just as well as constraint exclusion is able to eliminate table partitions. the I/O advantages of having queries target small subtables are the same as the I/O advantages of clustering the index : result pages in a small range are very close to each other on disk. Not entirely true. One a clustered index will not stay clustered if you are still updating data that is in the partition. You shouldn't underestimate the benefit of smaller relations in terms of maintenance either. > > finally, since constraint exclusion isn't as flexible as indexing (i've seen old mailing list posts that say that constraint exclusion only works with static constants in where clauses, and only works with simple operators like >, < which basically forces btree indexes when i want to use gist) it is indeed likely that partitioning can be slower than one big table with a clustered index. Yes the constraints have to be static. Not sure about the operator question honestly. > is my intuition completely off on this? You may actually want to look into expression indexes, not clustered ones. Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt