Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 48B8B64FF9A for ; Fri, 22 Aug 2008 02:52:24 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 20217-05 for ; Fri, 22 Aug 2008 02:52:19 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from ti-out-0910.google.com (ti-out-0910.google.com [209.85.142.186]) by postgresql.org (Postfix) with ESMTP id 0C4EC64FD04 for ; Fri, 22 Aug 2008 02:52:18 -0300 (ADT) Received: by ti-out-0910.google.com with SMTP id y8so228715tia.5 for ; Thu, 21 Aug 2008 22:52:17 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=VUIrUPd3jz6+5RyOw8icuBdNXqhNNrDMqm9oJukW+zs=; b=ujzCOgfweV3TW7xzvO1uwG3VQWFIocku0Waq3cD528tbFSSVt+WJNZWNlFVPwlj7nK cnrZLBFMIOTssEq9Z7BQCA3/GZ9LVC/6ARfUFVpd9Gp4o6L0SkXurdX+m4Xcsj9z9RAN Wp3OEX22WvaXZNCaiGYrh8eFBNRQC99cIWMzA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:references; b=wlIyktFK7iqijZ8LcXFkAGQuh2zaYLtw/Q4uCLHXvOr+DTl1PpwyJhiFwVylBBCwKQ GPQstHSAynnvACIXkOD6h+8srZQoHSmX+oBbLwI3Wu5Y/20C9aoif1x15DAEj8i1+tjk eple5FVHNo4/evZmjAwrcCL0A4bp2lIWjr5Qo= Received: by 10.110.109.12 with SMTP id h12mr784610tic.51.1219384336971; Thu, 21 Aug 2008 22:52:16 -0700 (PDT) Received: by 10.110.8.20 with HTTP; Thu, 21 Aug 2008 22:52:16 -0700 (PDT) Message-ID: <4478b4ac0808212252y931566aj95d4e6316be1ae8b@mail.gmail.com> Date: Fri, 22 Aug 2008 11:22:16 +0530 From: "=?WINDOWS-1252?Q?Kranti_K_K_Parisa=99?=" To: "Mark Lewis" Subject: Re: PostgreSQL+Hibernate Performance Cc: "Matthew Wakeling" , pgsql-performance@postgresql.org In-Reply-To: <1219327320.7023.142.camel@archimedes.mir3.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_123497_16134356.1219384336958" References: <4478b4ac0808200525ia6eabe8q886ac7fbba53ff05@mail.gmail.com> <1219242910.7023.94.camel@archimedes.mir3.com> <4478b4ac0808200810p7eb1f0b2ga307d858a8d94e60@mail.gmail.com> <4478b4ac0808210003x163e3d22u7fda12bd0e0ffeba@mail.gmail.com> <1219327320.7023.142.camel@archimedes.mir3.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200808/272 X-Sequence-Number: 31153 ------=_Part_123497_16134356.1219384336958 Content-Type: text/plain; charset=WINDOWS-1252 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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 wrote: > On Thu, 2008-08-21 at 12:33 +0530, Kranti K K Parisa=99 wrote: > > > On Wed, Aug 20, 2008 at 8:54 PM, Matthew Wakeling > > wrote: > > On Wed, 20 Aug 2008, Kranti K K Parisa=99 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=3Dsomevalue" or "column1=3Da AND column2=3Db". 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 > --=20 Best Regards Kranti Kiran Kumar Parisa M: +91 - 9391 - 438 - 738 +91 - 9849 - 625 - 625 ------=_Part_123497_16134356.1219384336958 Content-Type: text/html; charset=WINDOWS-1252 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Hi Mark,

Thanks again for the info.
I shall crea= te diff sets of indexes and see the query execution time.
And one of suc= h tables might get around 700,000 records over a period of 4-5 months. So w= hat kind of other measures I need to focus on.
I thought of the following
1) Indexes
2) Better Hardware (RAM & H= DD)

And how can i estimate the size of the row?  is it like bas= ed 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@mir= 3.com> wrote:
On Thu, 2008-08-21 at 12:33 +0530, Kranti K K Parisa= =99 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=99 = wrote:
>                 creating multi= ple indexes on same column will effect
>                 performance? >                  for exam= ple:
>
>                 index1 : colum= n1, column2, column3
>                 index2: column= 1
>                 index3: column= 2,
>                 index4: column= 3
>                 index5: column= 1,column2
>
>
>         The sole purpose of indexes is to affect p= erformance.
>
>         However, if you have index1, there is no p= oint 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 "S= ELECT
x FROM y WHERE column1=3Dsomevalue" or "column1=3Da AND column2= =3Db".  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 look= up
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/i= ndexes-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

------=_Part_123497_16134356.1219384336958--