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

Re: Quesion on the use of indexes

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Benjamin Krajmalnik <kraj(at)servoyant(dot)com>, pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Quesion on the use of indexes
Date: 2010-08-17 15:07:39
Message-ID: 1282057261-sup-9058@alvh.no-ip.org (view raw or flat)
Thread:
Lists: pgsql-performance
Excerpts from Tom Lane's message of lun ago 16 23:33:29 -0400 2010:
> "Benjamin Krajmalnik" <kraj(at)servoyant(dot)com> writes:
> > A little background - I have various multi-column indexes whenever I
> > have queries which restrict the output based on the values of the 2
> > fields (for example, a client code and the date of a transaction).
> 
> > Is there a performance gain using this approach as opposed to using 2
> > separate indexes, one on the first column and one on the second column?
> 
> Maybe, maybe not ... it's going to depend on a bunch of factors, one of
> which is what your update load is like compared to the queries that read
> the indexes.  There's a bit of coverage of this in the fine manual: see
> http://www.postgresql.org/docs/8.4/static/indexes-multicolumn.html
> and the next few pages.

Another important factor is how selective is each clause in isolation
compared to how selective they are together.  We have found that doing
BitmapAnd of two bitmap-scanned indexes is sometimes much too slow
compared to a two-column index.  (I have yet to see a case where indexes
beyond two columns are useful; at this point, combined bitmap indexscans
are enough.)

-- 
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

In response to

Responses

pgsql-performance by date

Next:From: Maciek SakrejdaDate: 2010-08-17 15:16:25
Subject: Re: Search query is curious
Previous:From: Kevin GrittnerDate: 2010-08-17 14:18:21
Subject: Re: Very poor performance

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