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 BA55353BE0 for ; Fri, 1 Apr 2005 17:36:48 +0100 (BST) 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 84648-01 for ; Fri, 1 Apr 2005 16:36:45 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 935D253BAE for ; Fri, 1 Apr 2005 17:36:45 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j31Gah9p007069; Fri, 1 Apr 2005 11:36:43 -0500 (EST) To: "Peterson, Bjorn" Cc: pgsql-performance@postgresql.org Subject: Re: enable_XXX options In-reply-to: <42E7C401A4CBCD49829BB53DDD538BA40211439B@chmpgexgmail3.ic.ncs.com> References: <42E7C401A4CBCD49829BB53DDD538BA40211439B@chmpgexgmail3.ic.ncs.com> Comments: In-reply-to "Peterson, Bjorn" message dated "Fri, 01 Apr 2005 10:04:08 -0600" Date: Fri, 01 Apr 2005 11:36:43 -0500 Message-ID: <7068.1112373403@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/9 X-Sequence-Number: 11406 "Peterson, Bjorn" writes: > I have a query in my application that takes an unreasonable amount of time > to complete (>4.5 hours execution time). After reviewing the EXPLAIN and > EXPLAIN ANALYZE output for that and similar queries, my colleagues and I > determined that turning off the enable_nestloop option might help - we > noticed dramatic speed improvements for that specific query after doing so > (<2 minutes execution time). I was warned not to mess with the enable_XXX > options in a production environment, but does anyone see any problem with > turning off the enable_nestloop option right before executing my query and > turning it back on afterwards? That's what it's there for ... but it would be useful to look into why the planner gets it so wrong without that hint. Could we see EXPLAIN ANALYZE both ways? regards, tom lane