Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 72D4D64FCDE for ; Wed, 20 Aug 2008 12:12:29 -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 94975-01-4 for ; Wed, 20 Aug 2008 12:12:06 -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.188]) by postgresql.org (Postfix) with ESMTP id 6276D6501A9 for ; Wed, 20 Aug 2008 12:10:44 -0300 (ADT) Received: by ti-out-0910.google.com with SMTP id y8so372736tia.5 for ; Wed, 20 Aug 2008 08:10:42 -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=v9OM2dsLRW5hKgxDrta2ierriNedqhv8Bnfu98pi62A=; b=xn1MmJQPZcBxWTIFCt+GKRghraqUh8vcr3N65qQO9F3FGekL+lfBlEQmlDS1lGvLJO UvHwCMMbthAXNLV408uo5G6Kof/wb84/EzPU90/bDiPtoozQRgzmxzdDlwBN98IV4oBu tWTMTUL9VAOvQZQI76H0Pxp2DJghAR6t5ESyI= 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=eVsDF7c7qAQ6vhxl4iCEMQa3vvseDDVq1T0ixn1cS5C87SrojNj7Z2zpRKNPuX+N4w Qfc902DltajPKxg8B10dvKjMwQ+5354mJd0GOuJU+FCPRa9bwloIrU4XbzyUiERN3JW6 EmggiNFdQ+VNZJWVQxi52OeyjiPUOims7TLf4= Received: by 10.110.37.3 with SMTP id k3mr185216tik.29.1219245041979; Wed, 20 Aug 2008 08:10:41 -0700 (PDT) Received: by 10.110.8.20 with HTTP; Wed, 20 Aug 2008 08:10:41 -0700 (PDT) Message-ID: <4478b4ac0808200810p7eb1f0b2ga307d858a8d94e60@mail.gmail.com> Date: Wed, 20 Aug 2008 20:40:41 +0530 From: "=?WINDOWS-1252?Q?Kranti_K_K_Parisa=99?=" To: "Mark Lewis" Subject: Re: PostgreSQL+Hibernate Performance Cc: pgsql-performance@postgresql.org, nik9000@gmail.com In-Reply-To: <1219242910.7023.94.camel@archimedes.mir3.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_86967_1498963.1219245041974" References: <4478b4ac0808200525ia6eabe8q886ac7fbba53ff05@mail.gmail.com> <1219242910.7023.94.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/222 X-Sequence-Number: 31103 ------=_Part_86967_1498963.1219245041974 Content-Type: text/plain; charset=WINDOWS-1252 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline creating multiple indexes on same column will effect performance? for example: index1 : column1, column2, column3 index2: column1 index3: column2, index4: column3 index5: column1,column2 which means, i am trying fire the SQL queries keeping columns in the where conditions. and the possibilities are like the above. if we create such indexes will it effect on performance? and what is the best go in this case? On Wed, Aug 20, 2008 at 8:10 PM, Mark Lewis wrote: > On Wed, 2008-08-20 at 17:55 +0530, Kranti K K Parisa=99 wrote: > > Hi, > > > > Can anyone suggest the performance tips for PostgreSQL using > > Hibernate. > > > > One of the queries: > > > > - PostgreSQL has INDEX concept and Hibernate also has Column INDEXes. > > Which is better among them? or creating either of them is enough? or > > need to create both of them? > > > > and any more performace aspects ? > > Hibernate is a library for accessing a database such as PostgreSQL. It > does not offer any add-on capabilities to the storage layer itself. So > when you tell Hibernate that a column should be indexed, all that it > does create the associated PostgreSQL index when you ask Hibernate to > build the DB tables for you. This is part of Hibernate's effort to > protect you from the implementation details of the underlying database, > in order to make supporting multiple databases with the same application > code easier. > > So there is no performance difference between a PG index and a Hibernate > column index, because they are the same thing. > > The most useful Hibernate performance-tuning advice isn't PG-specific at > all, there are just things that you need to keep in mind when developing > for any database to avoid pathologically bad performance; those tips are > really beyond the scope of this mailing list, Google is your friend > here. > > I've been the architect for an enterprise-class application for a few > years now using PostgreSQL and Hibernate together in a > performance-critical context, and honestly I can't think of one time > that I've been bitten by a PG-specific performance issue (a lot of > performance issues with Hibernate that affected all databases though; > you need to know what you're doing to make Hibernate apps that run fast. > If you do run into problems, you can figure out the actual SQL that > Hibernate is issuing and do the normal PostgreSQL explain analyze on it; > usually caused by a missing index. > > -- Mark > > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org= ) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > --=20 Best Regards Kranti Kiran Kumar Parisa M: +91 - 9391 - 438 - 738 +91 - 9849 - 625 - 625 ------=_Part_86967_1498963.1219245041974 Content-Type: text/html; charset=WINDOWS-1252 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
creating multiple indexes on same column will effect perfo= rmance?
 for example:

index1 : column1, column2, column3
= index2: column1
index3: column2,
index4: column3
index5: column1,c= olumn2

which means, i am trying fire the SQL queries keeping columns in the wh= ere conditions. and the possibilities are like the above.

if we crea= te such indexes will it effect on performance?
and what is the best go i= n this case?


On Wed, Aug 20, 2008 at 8:10 PM, Mark Le= wis <mark.lewis= @mir3.com> wrote:
On Wed, 2008-08-20 at 17:55 +0530, Kranti K K Parisa= =99 wrote:
> Hi,
>
> Can anyone suggest the performance tips for PostgreSQL using
> Hibernate.
>
> One of the queries:
>
> - PostgreSQL has INDEX concept and Hibernate also has Column INDEXes.<= br> > Which is better among them? or creating either of them is enough? or > need to create both of them?
>
> and any more performace aspects ?

Hibernate is a library fo= r accessing a database such as PostgreSQL.  It
does not offer any add-on capabilities to the storage layer itself.  S= o
when you tell Hibernate that a column should be indexed, all that it
does create the associated PostgreSQL index when you ask Hibernate to
build the DB tables for you.  This is part of Hibernate's effort t= o
protect you from the implementation details of the underlying database,
in order to make supporting multiple databases with the same application code easier.

So there is no performance difference between a PG index and a Hibernate column index, because they are the same thing.

The most useful Hibernate performance-tuning advice isn't PG-specific a= t
all, there are just things that you need to keep in mind when developing for any database to avoid pathologically bad performance; those tips are really beyond the scope of this mailing list, Google is your friend
here.

I've been the architect for an enterprise-class application for a few years now using PostgreSQL and Hibernate together in a
performance-critical context, and honestly I can't think of one time that I've been bitten by a PG-specific performance issue (a lot of
performance issues with Hibernate that affected all databases though;
you need to know what you're doing to make Hibernate apps that run fast= .
If you do run into problems, you can figure out the actual SQL that
Hibernate is issuing and do the normal PostgreSQL explain analyze on it; usually caused by a missing index.

-- Mark



--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



--

Best Regards=
Kranti Kiran Kumar Parisa
M: +91 - 9391 - 438 - 738
+91 - 9849 -= 625 - 625

------=_Part_86967_1498963.1219245041974--