thanks for the prompt response. some comments / questions below : On Oct 28, 2010, at 10:31 AM, Joshua D. Drake wrote: >> ...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. in my situation, the update come in-order (it is timeseries data and the clustered index is on time.) so the table should remain relatively clustered. updates also happen relatively infrequently (once a day in one batch.) so it appears that we will continue to get the I/O benefits described above. are there any other benefits which partitioning provides for query performance (as opposed to update performance) besides the ones which i have mentioned? > Yes the constraints have to be static. Not sure about the operator > question honestly. this seems to severely restrict their usefulness -- our queries are data warehouse analytical -type queries, so the constraints are usually data-driven (come from joining against other tables.) >> is my intuition completely off on this? > > You may actually want to look into expression indexes, not clustered > ones. what would expression indexes give me? thanks and best regards, ben -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
