X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5D64B5E3F15 for ; Fri, 27 Aug 2004 16:29:25 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 28051-05 for ; Fri, 27 Aug 2004 19:29:19 +0000 (GMT) Received: from web11905.mail.yahoo.com (web11905.mail.yahoo.com [216.136.172.189]) by svr1.postgresql.org (Postfix) with SMTP id 392E95E46D4 for ; Fri, 27 Aug 2004 16:29:18 -0300 (ADT) Message-ID: <20040827192911.72205.qmail@web11905.mail.yahoo.com> Received: from [209.167.177.36] by web11905.mail.yahoo.com via HTTP; Fri, 27 Aug 2004 12:29:11 PDT Date: Fri, 27 Aug 2004 12:29:11 -0700 (PDT) From: Artimenko Igor Subject: Why those queries do not utilize indexes? To: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/399 X-Sequence-Number: 8035 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 it’s always msgstatus index is narrowest compare with ‘messagesStatus’ one. Is any way to “enforce” to use a particular index? What’s 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