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 288BFD1B19F for ; Wed, 9 Jun 2004 13:03:07 -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 86674-08 for ; Wed, 9 Jun 2004 16:02:59 +0000 (GMT) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by svr1.postgresql.org (Postfix) with ESMTP id B65A1D1B1A5 for ; Wed, 9 Jun 2004 13:02:57 -0300 (ADT) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1BY5UL-00027u-00; Wed, 09 Jun 2004 11:59:41 -0400 To: "Scott Marlowe" Cc: "Russell Smith" , pgsql-performance@postgresql.org Subject: Re: Use of Functional Indexs and Planner estimates References: <200406081724.36617.mr-russ@pws.com.au> <1086706451.27200.50.camel@localhost.localdomain> In-Reply-To: <1086706451.27200.50.camel@localhost.localdomain> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 09 Jun 2004 11:59:41 -0400 Message-ID: <87n03cvi1e.fsf@stark.xeocode.com> Lines: 37 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 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: 200406/97 X-Sequence-Number: 7155 "Scott Marlowe" writes: > > -> Seq Scan on rules > > (cost=0.00..22296.32 rows=11294 width=12) > > (actual time=540.149..2047.308 rows=1 loops=1) > Simple, the planner is choosing a sequential scan when it should be > choosing an index scan. This is usually because random_page_cost is set > too high, at the default of 4. Try settings between 1.2 and 2.x or so > to see how that helps. Be sure and test with various queries of your > own to be sure you've got about the right setting. Unless you make random_page_cost about .0004 (4/11294) it isn't going to be costing this query right (That's a joke, don't do it:). It's thinking there are 11,000 records matching the where clause when in fact there is only 1. If you know how an upper bound on how many records the query should be finding you might try a kludge involving putting a LIMIT inside the group by. ie, something like select rulename,redirect from (select rulename,redirect from ... where ... limit 100) as kludge group by rulename,redirect This would at least tell the planner not to expect more than 100 rows and to take the plan likely to produce the first 100 rows fastest. But this has the disadvantage of uglifying your code and introducing an arbitrary limit. When 7.5 comes out it you'll want to rip this out. -- greg