Re: partitioning question 1

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
anyquery performance that a clustered index doesn't already give you -- the advantages are all in maintainability.  an
indexis able to eliminate pages just as well as constraint exclusion is able to eliminate table partitions.  the I/O
advantagesof having queries target small subtables are the same as the I/O advantages of clustering the index : result
pagesin 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
constraintexclusion only works with static constants in where clauses, and only works with simple operators like >, <
whichbasically forces btree indexes when i want to use gist) it is indeed likely that partitioning can be slower than
onebig 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