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

Poor index choice -- multiple indexes of the same columns

From: "Karl O(dot) Pinc" <kop(at)meme(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Poor index choice -- multiple indexes of the same columns
Date: 2005-06-27 23:09:26
Message-ID: 1119913766l.4428l.2l@mofo (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
Postgresql 8.0.3


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(at)meme(dot)com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein


pgsql-performance by date

Next:From: Karl O. PincDate: 2005-06-27 23:16:01
Subject: Forcing use of a particular index
Previous:From: Jacques CaronDate: 2005-06-27 22:40:53
Subject: Re: Forcing use of a particular index

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