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?
--
Best Regards
Kranti Kiran Kumar Parisa
M: +91 - 9391 - 438 - 738
+91 - 9849 - 625 - 625
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 <mark.lewis@xxxxxxxx> wrote:
On Thu, 2008-08-21 at 12:33 +0530, Kranti K K Parisa™ wrote:(trying to get the thread back into newest-comments-last order)
> 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?
>
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
