From: Kranti K K Parisa™ <kranti.parisa@gmail.com>
To: Mark Lewis <mark.lewis@mir3.com>
Cc: Matthew Wakeling <matthew@flymine.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL+Hibernate Performance
Date: Fri, 22 Aug 2008 11:22:16 +0530
Message-ID: <4478b4ac0808212252y931566aj95d4e6316be1ae8b@mail.gmail.com> (raw)
In-Reply-To: <1219327320.7023.142.camel@archimedes.mir3.com>
References: <4478b4ac0808200525ia6eabe8q886ac7fbba53ff05@mail.gmail.com>
<1219242910.7023.94.camel@archimedes.mir3.com>
<4478b4ac0808200810p7eb1f0b2ga307d858a8d94e60@mail.gmail.com>
<alpine.DEB.1.10.0808201621340.4454@aragorn.flymine.org>
<4478b4ac0808210003x163e3d22u7fda12bd0e0ffeba@mail.gmail.com>
<1219327320.7023.142.camel@archimedes.mir3.com>
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 <mark.lewis@mir3.com> 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
> > <matthew@flymine.org> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kranti.parisa@gmail.com, mark.lewis@mir3.com, matthew@flymine.org
Subject: Re: PostgreSQL+Hibernate Performance
In-Reply-To: <4478b4ac0808212252y931566aj95d4e6316be1ae8b@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox