On 09/01/2014 05:52 PM, Andres Freund wrote:
> On 2014-08-29 20:12:16 +0200, Hannu Krosing wrote:
>> It would need to replace plain tid (pagenr, tupnr) with triple of (partid,
>> pagenr, tupnr).
>>
>> Cross-partition indexes are especially needed if we want to allow putting
>> UNIQUE constraints on non-partition-key columns.
> I actually don't think this is necessary. I'm pretty sure that you can
> build an efficient and correct version of unique constraints with
> several underlying indexes in different partitions each. The way
> exclusion constraints are implemented imo is a good guide.
>
> I personally think that implementing cross partition indexes has a low
> enough cost/benefit ratio that I doubt it's wise to tackle it anytime
> soon.
Also it has the downside of (possibly) making DROP PARTITION either
slow or wasting space until next VACUUM.
So if building composite unique indexes over multiple per-partition
indexes is doable, I would much prefer this.
Cheers
--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ