Re: PostgreSQL+Hibernate Performance - Mailing list pgsql-performance

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