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 EDA745E46EC for ; Fri, 27 Aug 2004 18:09:03 -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 58133-07 for ; Fri, 27 Aug 2004 21:08:58 +0000 (GMT) Received: from web11904.mail.yahoo.com (web11904.mail.yahoo.com [216.136.172.188]) by svr1.postgresql.org (Postfix) with SMTP id DF68E5E3F15 for ; Fri, 27 Aug 2004 18:08:56 -0300 (ADT) Message-ID: <20040827210858.16196.qmail@web11904.mail.yahoo.com> Received: from [209.167.177.36] by web11904.mail.yahoo.com via HTTP; Fri, 27 Aug 2004 14:08:58 PDT Date: Fri, 27 Aug 2004 14:08:58 -0700 (PDT) From: Artimenko Igor Subject: Re: Why those queries do not utilize indexes? To: Dennis Bjorklund In-Reply-To: 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/405 X-Sequence-Number: 8041 I could force Postgres to use the best index by removing condition "msgstatus = CAST( 0 AS smallint );" from WHERE clause & set enable_seqscan to off; Total runtime in this case dropped from 1883 ms ( sequential reads ) to 1.598 ms ( best index ). But unfortunatelly It does not resolve my problem. I can not remove above condition. I need to find a way to use whole condition "WHERE user_id = CAST( 20000 AS BIGINT ) and msgstatus = CAST( 0 AS smallint );" and still utilyze index. Yes you are right. Using "messagesStatus" index is even worse for my data set then sequential scan. Igor Artimenko --- Dennis Bjorklund wrote: > On Fri, 27 Aug 2004, Artimenko Igor wrote: > > > 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 ) > > So pg thinks that a sequential scan will be a little bit faster (The cost > is a little bit smaller). If you compare the actual runtimes maybe you > will see that pg was right. In this case the cost is almost the same so > the runtime is probably almost the same. > > When you have more data pg will start to use the index since then it will > be faster to use an index compared to a seq. scan. > > -- > /Dennis Björklund > > _______________________________ Do you Yahoo!? Win 1 of 4,000 free domain names from Yahoo! Enter now. http://promotions.yahoo.com/goldrush