Well, I never said unique, but you're correct, it's pretty undesirable to put a global index on your partitioning key.
On Tue, Oct 04, 2005 at 06:16:21PM +0200, Zeugswetter Andreas DAZ SD wrote: > > > Another possibility is optimizing for the special case of > > indexing on a partitioning key. In this case, index values > > would be very localized to one table, so just storing the > > table info on each index page (or something similar) would work well. > > If you have the partitioning key in the index and the partitions don't > overlap, it is better to create separate [unique] indexes on the > subtables. > Building separate indexes per partition is usually preferred because of: > 1. performance of dropping a partition > 2. smaller index for CE > > Only if you need an "order by" without a sort step, that spawns more > than one partition > things usually get ugly. Imho the best solution would be a merge node, > that merges results of > several index accesses to avoid a sort and still use separate indexes. > Such > a merge node could probably also detect the case where accessing > partitions in a certain > order still produces ordered results. > > Usually you will only want the "one big unique index" when the > partitioning is not > reflectable in the index keys, and then (also in other db's) such an > index is usually a pain ... > > Andreas > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
