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

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 (view raw or flat)
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

pgsql-performance by date

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

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