Quesion on the use of indexes - Mailing list pgsql-performance

From Benjamin Krajmalnik
Subject Quesion on the use of indexes
Date
Msg-id F4E6A2751A2823418A21D4A160B689887B0362@fletch.stackdump.local
Whole thread Raw
Responses Re: Quesion on the use of indexes  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance

A little background – I have various multi-column indexes whenever I have queries which restrict the output based on the values of the 2 fields (for example, a client code and the date of a transaction).

Is there a performance gain using this approach as opposed to using 2 separate indexes, one on the first column and one on the second column?

 

The reason I am asking is that my coding convetion goes back to the days where I used ISAM tables, so the systems did not know how to use more than a single index.

In some cases, I may have an index on (columna, columnb)  and one on (columnb, columna) due to the data access patterns.  If there are no performance gains in having these multi-part indexes, and performance will be the same as having one index solely on columna and one solely on columnb, then I can reduce the disk usage significantly in some cases.

pgsql-performance by date:

Previous
From: "Aaron Burnett"
Date:
Subject: Re: Very poor performance
Next
From: Tom Lane
Date:
Subject: Re: Quesion on the use of indexes