Hi Mark, Thanks again for the info. I shall create diff sets of indexes and see the query execution time. And one of such tables might get around 700,000 records over a period of 4-5 months. So what kind of other measures I need to focus on. I thought of the following 1) Indexes 2) Better Hardware (RAM & HDD)
And how can i estimate the size of the row? is it like based on the data types of the columns i have in the table? Do you have any info to guide me on this? On Thu, Aug 21, 2008 at 7:32 PM, Mark Lewis <[EMAIL PROTECTED]> wrote: > 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 > > <[EMAIL PROTECTED]> 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 > -- Best Regards Kranti Kiran Kumar Parisa M: +91 - 9391 - 438 - 738 +91 - 9849 - 625 - 625
