Content-Type: text/plain; charset=UTF-8
Cc: Benjamin Krajmalnik <kraj@servoyant.com>,
	pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Quesion on the use of indexes
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
In-reply-to: <29298.1282016009@sss.pgh.pa.us>
References: <F4E6A2751A2823418A21D4A160B689887B0362@fletch.stackdump.local>
	<29298.1282016009@sss.pgh.pa.us>
Date: Tue, 17 Aug 2010 11:07:39 -0400
Message-Id: <1282057261-sup-9058@alvh.no-ip.org>
User-Agent: Sup/git
Content-Transfer-Encoding: 8bit

Excerpts from Tom Lane's message of lun ago 16 23:33:29 -0400 2010:
> "Benjamin Krajmalnik" <kraj@servoyant.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@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support