Date: Mon, 27 Jun 2005 23:09:26 +0000
From: "Karl O. Pinc" <kop@meme.com>
Subject: Poor index choice -- multiple indexes of the same columns
To: pgsql-performance@postgresql.org
Message-Id: <1119913766l.4428l.2l@mofo>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii; DelSp=Yes; Format=Flowed
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

Postgresql 8.0.3

Hi,

I have a query

select 1
  from census
  where date < '1975-9-21' and sname = 'RAD' and status != 'A'
  limit 1;

Explain analyze says it always uses the index made by:

   CREATE INDEX census_date_sname ON census (date, sname);

this is even after I made the index:

   CREATE INDEX census_sname_date ON census (sname, date);

I made census_sname_date because it ran too slow. By deleting
census_date_sname (temporarly, because my apps don't like this)
I can force the use of census_sname_date and the query runs fine.

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.

As you might imagine there are relatively few sname values and
relatively many date values in my data.  I use a query like the
above in a trigger to enforce bounds limitations.  I don't
expect (want) to find any rows returned.

I've figured out how to avoid executing this code very often,
so this is not presently a serious problem for me.

Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein