Partial index usage

From: Laszlo Nagy <gandalf(at)shopzeus(dot)com>
To: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Cc: Daniel Fekete <danieleff(at)gmail(dot)com>
Subject: Partial index usage
Date: 2009-02-16 08:54:07
Message-ID: 499929AF.6090704@shopzeus.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi All,

I have these indexes on a table:

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? Will postgresql use
(partno,producer_id) when it only needs to order by partno? (partno is a
text field, producer_id is int4). Index sizes: 172MB and 137MB. I guess
if I only had one index, it would save memory and increase performance.

Another pair of incides, 144MB and 81MB respecively:

CREATE INDEX idx_product_producer_uploads
ON product
USING btree
(producer_id, am_upload_status_id);

CREATE INDEX idx_product_producer_id
ON product
USING btree
(producer_id);

am_upload_status_id is also an int4. Can I delete the second index
without performance drawback?

Thanks,

Laszlo

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Gregory Stark 2009-02-16 13:31:37 Re: suggestions for postgresql setup on Dell 2950 , PERC6i controller
Previous Message Greg Smith 2009-02-15 17:38:00 Re: I/O increase after upgrading to 8.3.5