Skip site navigation (1) Skip section navigation (2)

Re: Partial index usage

From: decibel <decibel(at)decibel(dot)org>
To: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
Cc: Laszlo Nagy <gandalf(at)shopzeus(dot)com>, pgsql-performance <pgsql-performance(at)postgresql(dot)org>, Daniel Fekete <danieleff(at)gmail(dot)com>
Subject: Re: Partial index usage
Date: 2009-02-21 03:22:11
Message-ID: EF67481D-C67D-471A-8198-EEEEFCB8B0EE@decibel.org (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828



In response to

pgsql-performance by date

Next:From: sathiya psqlDate: 2009-02-21 07:14:38
Subject: how the hdd read speed is related to the query execution speed.
Previous:From: Denis LussierDate: 2009-02-21 01:40:02
Subject: Re: Benchmark comparing PostgreSQL, MySQL and Oracle

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group