Subject: Re: PostgreSQL+Hibernate Performance
From: Mark Lewis <mark.lewis@mir3.com>
To: Kranti K K =?UTF-8?Q?Parisa=E2=84=A2?= <kranti.parisa@gmail.com>
Cc: pgsql-performance@postgresql.org, nik9000@gmail.com
In-Reply-To: <4478b4ac0808200810p7eb1f0b2ga307d858a8d94e60@mail.gmail.com>
References: <4478b4ac0808200525ia6eabe8q886ac7fbba53ff05@mail.gmail.com>
	<1219242910.7023.94.camel@archimedes.mir3.com>
	<4478b4ac0808200810p7eb1f0b2ga307d858a8d94e60@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Organization: MIR3, Inc.
Date: Wed, 20 Aug 2008 08:24:46 -0700
Message-Id: <1219245886.7023.108.camel@archimedes.mir3.com>
Mime-Version: 1.0

The tradeoffs for multiple indexes are more or less as follows:

1. Having the right indexes makes queries faster, often dramatically so.

2. But more indexes makes inserts/updates slower, although generally not
dramatically slower.

3. Each index requires disk space.  With several indexes, you can easily
have more of your disk taken up by indexes than with actual data.

I would be careful to only create the indexes you need, but it's
probably worse to have too few indexes than too many.  Depends on your
app though.

-- Mark

On Wed, 2008-08-20 at 20:40 +0530, Kranti K K Parisa=E2=84=A2 wrote:
> creating multiple indexes on same column will effect performance?
>  for example:
>=20
> index1 : column1, column2, column3
> index2: column1
> index3: column2,
> index4: column3
> index5: column1,column2
>=20
> which means, i am trying fire the SQL queries keeping columns in the
> where conditions. and the possibilities are like the above.
>=20
> if we create such indexes will it effect on performance?
> and what is the best go in this case?
>=20
>=20
> On Wed, Aug 20, 2008 at 8:10 PM, Mark Lewis <mark.lewis@mir3.com>
> wrote:
>         On Wed, 2008-08-20 at 17:55 +0530, Kranti K K Parisa=E2=84=A2 wro=
te:
>        =20
>        =20
>         > 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 ?
>        =20
>        =20
>        =20
>         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.
>        =20
>         So there is no performance difference between a PG index and a
>         Hibernate
>         column index, because they are the same thing.
>        =20
>         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.
>        =20
>         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.
>        =20
>         -- Mark
>        =20
>        =20
>        =20
>        =20
>         --
>         Sent via pgsql-performance mailing list
>         (pgsql-performance@postgresql.org)
>         To make changes to your subscription:
>         http://www.postgresql.org/mailpref/pgsql-performance
>=20
>=20
>=20
> --=20
>=20
> Best Regards
> Kranti Kiran Kumar Parisa
> M: +91 - 9391 - 438 - 738
> +91 - 9849 - 625 - 625
>=20
>=20