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

Why those queries do not utilize indexes?

From: Artimenko Igor <igorart7(at)yahoo(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Why those queries do not utilize indexes?
Date: 2004-08-27 19:29:11
Message-ID: 20040827192911.72205.qmail@web11905.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-performance
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 

Responses

pgsql-performance by date

Next:From: Greg StarkDate: 2004-08-27 19:31:22
Subject: Re: Equivalent praxis to CLUSTERED INDEX?
Previous:From: Mischa SandbergDate: 2004-08-27 18:26:39
Subject: Re: Equivalent praxis to CLUSTERED INDEX?

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