Laszlo Nagy wrote: > 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? 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. Within a transaction, drop the second index then run the query of interest with EXPLAIN ANALYZE to determine just how much slower - then ROLLBACK to undo the index drop. You'll lock out other transactions while you're doing this, but you won't make any permanent changes and you can cancel it at any time. > Will postgresql use > (partno,producer_id) when it only needs to order by partno? Yes. > I guess > if I only had one index, it would save memory and increase performance. Maybe. If they both fit into memory along with the main table data, then you might end up losing instead since the second index is smaller and should be somewhat faster to scan. > am_upload_status_id is also an int4. Can I delete the second index > without performance drawback? Same answer as above - test it and find out. You may win or lose depending on your workload, table sizes, available memory, etc. -- Craig Ringer -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
