Skip site navigation (1) Skip section navigation (2)

Re: PostgreSQL+Hibernate Performance

From: Kranti K K Parisa™ <kranti(dot)parisa(at)gmail(dot)com>
To: "Mark Lewis" <mark(dot)lewis(at)mir3(dot)com>
Cc: "Matthew Wakeling" <matthew(at)flymine(dot)org>, pgsql-performance(at)postgresql(dot)org
Subject: Re: PostgreSQL+Hibernate Performance
Date: 2008-08-22 05:52:16
Message-ID: 4478b4ac0808212252y931566aj95d4e6316be1ae8b@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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(dot)lewis(at)mir3(dot)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(at)flymine(dot)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

In response to

pgsql-performance by date

Next:From: RWDate: 2008-08-22 06:18:25
Subject: Re: The state of PG replication in 2008/Q2?
Previous:From: Decibel!Date: 2008-08-22 04:53:58
Subject: Re: Optimizing a VIEW

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group