From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: "Karl O. Pinc" <kop@meme.com>
Subject: Re: Poor index choice -- multiple indexes of the same columns
Date: Mon, 27 Jun 2005 15:37:41 -0700
User-Agent: KMail/1.8
Cc: pgsql-performance@postgresql.org
References: <1119913766l.4428l.2l@mofo>
In-Reply-To: <1119913766l.4428l.2l@mofo>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200506271537.41797.josh@agliodbs.com>

Karl,

> Seems to me that when there's a constant value in the query
> and an = comparision it will always be faster to use the (b-tree)
> index that's ordered first by the constant value, as then all further
> blocks are guarenteed to have a higher relevant information
> density.  At least when compared with another index that has the
> same columns in it.

That really depends on the stats.   Such a choice would *not* be 
appropriate if the < comparison was expected to return 1- rows while the = 
condition applied to 15% of the table.

What is your STATISTICS_TARGET for the relevant columns set to?   When's 
the last time you ran analyze?  If this is all updated, you want to post 
the pg_stats rows for the relevant columns?

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco