On Thu, 2008-08-21 at 12:33 +0530, Kranti K K Parisa™ wrote: > On Wed, Aug 20, 2008 at 8:54 PM, Matthew Wakeling > <matthew@xxxxxxxxxxx> wrote: > On Wed, 20 Aug 2008, Kranti K K Parisa™ wrote: > creating multiple indexes on same column will effect > performance? > for example: > > index1 : column1, column2, column3 > index2: column1 > index3: column2, > index4: column3 > index5: column1,column2 > > > The sole purpose of indexes is to affect performance. > > However, if you have index1, there is no point in having > index2 or index5. > > Matthew > > Thanks Matthew, > > does that mean i can just have index1, index3, index4? > (trying to get the thread back into newest-comments-last order) Well, yes you can get away with just index1, index3 and index4, and it may well be the optimal solution for you, but it's not entirely clear-cut. It's true that PG can use index1 to satisfy queries of the form "SELECT x FROM y WHERE column1=somevalue" or "column1=a AND column2=b". It will not be as fast as an index lookup from a single index, but depending on the size of the tables/indexes and the selectivity of leading column(s) in the index, the difference in speed may be trivial. On the other hand, if you have individual indexes on column1, column2 and column3 but no multi-column index, PG can combine the individual indexes in memory with a bitmap. This is not as fast as a normal lookup in the multi-column index would be, but can still be a big win over not having an index at all. To make an educated decision you might want to read over some of the online documentation about indexes, in particular these two sections: http://www.postgresql.org/docs/8.3/interactive/indexes-multicolumn.html and http://www.postgresql.org/docs/8.3/interactive/indexes-bitmap-scans.html -- Mark
