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 378BE529A0 for ; Thu, 28 Jul 2005 13:13:35 -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 91158-10 for ; Thu, 28 Jul 2005 16:13:26 +0000 (GMT) Received: from crestone.coronasolutions.com (crestone.coronasolutions.com [66.45.104.24]) by svr1.postgresql.org (Postfix) with ESMTP id 0E17752889 for ; Thu, 28 Jul 2005 13:13:24 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by crestone.coronasolutions.com (Postfix) with ESMTP id E2B0864419C for ; Thu, 28 Jul 2005 10:12:56 -0600 (MDT) Received: from crestone.coronasolutions.com ([127.0.0.1]) by localhost (crestone.coronasolutions.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 20114-07 for ; Thu, 28 Jul 2005 10:12:55 -0600 (MDT) Received: from [192.168.1.12] (c-24-9-24-35.hsd1.co.comcast.net [24.9.24.35]) (using TLSv1 with cipher RC4-SHA (128/128 bits)) (No client certificate requested) by crestone.coronasolutions.com (Postfix) with ESMTP id DF3D4644178 for ; Thu, 28 Jul 2005 10:12:54 -0600 (MDT) Mime-Version: 1.0 (Apple Message framework v730) In-Reply-To: <1d219a6f0507280738791f0ea3@mail.gmail.com> References: <1d219a6f05072708122cf6abeb@mail.gmail.com> <1d219a6f050727092927746db1@mail.gmail.com> <1d219a6f0507280738791f0ea3@mail.gmail.com> Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: Content-Transfer-Encoding: 7bit From: Dan Harris Subject: Re: Fwd: Help with view performance problem Date: Thu, 28 Jul 2005 10:14:32 -0600 To: pgsql-performance@postgresql.org X-Mailer: Apple Mail (2.730) X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at drivefaster.net X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200507/439 X-Sequence-Number: 13680 On Jul 28, 2005, at 8:38 AM, Chris Hoover wrote: > > > I did some more testing, and ran the explain analyze on the problem. > In my session I did a set enable_hashjoin = false and then ran the > analyze. This caused it to use the indexes as I have been expecting > it to do. > > Now, how can I get it to use the indexes w/o manipulating the > environment? What make postgresql want to sequentially scan and use a > hash join? > > thanks, > > Chris > > explain analyze with set_hashjoin=false; > prob_db=#explain analyze select * from clm_com; > > I had something similar to this happen recently. The planner was choosing a merge join and seq scan because my 'random_page_cost' was set too high. I had it at 3 , and ended up settling at 1.8 to get it to correctly use my indices. Once that change was in place, the planner did the 'right' thing for me. Not sure if this will help you, but it sounds similar. -Dan