From: Karl O. Pinc <kop@meme.com>
To: pgsql-performance@postgresql.org
Subject: Poor index choice -- multiple indexes of the same columns
Date: Mon, 27 Jun 2005 23:09:26 +0000
Message-ID: <1119913766l.4428l.2l@mofo> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kop@meme.com
Subject: Re: Poor index choice -- multiple indexes of the same columns
In-Reply-To: <1119913766l.4428l.2l@mofo>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox