From: Artimenko Igor <igorart7@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: Why those queries do not utilize indexes?
Date: Fri, 27 Aug 2004 12:29:11 -0700 (PDT)
Message-ID: <20040827192911.72205.qmail@web11905.mail.yahoo.com> (raw)
Hi everybody!
Here is my queries:
1. explain SELECT * FROM messageinfo WHERE user_id = CAST( 20000 AS BIGINT ) and msgstatus = CAST(
0 AS smallint );
2. explain SELECT * FROM messageinfo WHERE messageinfo.user_id = 20000::int8 and msgstatus =
0::smallint;
In both cases Explain command shows:
1. Sequential search and very high cost if set enable_seqscan to on;
Seq scan on messageinfo ( cost=0.00..24371.30, rows =36802 )
2. Index scan but even bigger cost if set enable_seqscan to off;
Index messagesStatus on messageinfo ( Cost=0.00..27220.72, rows=36802 )
messageinfo table has 200 records which meet this criteria and 662420 in total:
CREATE TABLE messageinfo
(
user_id int8 NOT NULL,
msgstatus int2 NOT NULL DEFAULT (0)::smallint,
receivedtime timestamp NOT NULL DEFAULT now(),
msgread bool DEFAULT false,
CONSTRAINT "$1" FOREIGN KEY (user_id) REFERENCES users (id) ON UPDATE CASCADE ON DELETE CASCADE,
)
WITH OIDS;
CREATE INDEX msgstatus
ON messageinfo
USING btree
(user_id, msgstatus);
CREATE INDEX "messagesStatus"
ON messageinfo
USING btree
(msgstatus);
CREATE INDEX msgread
ON messageinfo
USING btree
(user_id, msgread);
CREATE INDEX "receivedTime"
ON messageinfo
USING btree
(receivedtime);
MY QUESTIONS ARE:
1. Should I afraid of high cost indexes? Or query will still be very efficient?
2. Postgres does not use the index I need. For my data sets its always msgstatus index is
narrowest compare with messagesStatus one. Is any way to enforce to use a particular index?
Whats the logic when Postgres chooses one index compare with the other.
3. I can change db structure to utilize Postgres specifics if you can tell them to me.
4. Also, originally I had messagesStatus index having 2 components ( msgstatus, user_id ).
But query SELECT * FROM messageinfo WHERE msgstatus = 0 did not utilize indexes in this case. It
only worked if both index components are in WHERE part. So I have to remove 2-nd component
user_id from messagesStatus index even I wanted it. Is any way that where clause has only 1-st
component but index is utilized?
Igor Artimenko
__________________________________
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
http://promotions.yahoo.com/new_mail
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: igorart7@yahoo.com
Subject: Re: Why those queries do not utilize indexes?
In-Reply-To: <20040827192911.72205.qmail@web11905.mail.yahoo.com>
* 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