Re: Partial index usage

On Feb 16, 2009, at 9:07 AM, Craig Ringer wrote:
>> CREATE INDEX uidx_product_partno_producer_id
>>  ON product
>>  USING btree
>>  (partno, producer_id);
>>
>>
>> CREATE INDEX idx_product_partno
>>  ON product
>>  USING btree
>>  (partno);
>>
>> Can I safely delete the second one?
>
> You can safely delete BOTH in that it won't hurt your data, only
> potentially hurt performance.
>
> Deleting the index on (partno) should somewhat improve insert
> performance and performance on updates that can't be done via HOT.
>
> However, the index on (partno, producer_id) is requires more
> storage and
> memory than the index on just (partno). AFAIK it's considerably slower
> to scan.


Actually, that's not necessarily true. If both partno and procuder_id
are ints and you're on a 64bit platform, there won't be any change in
index size, due to alignment issues.
--
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828