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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
