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 7F871329CD9 for ; Thu, 16 Sep 2004 19:07:53 +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 19206-07 for ; Thu, 16 Sep 2004 18:07:43 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id A23BD329CD2 for ; Thu, 16 Sep 2004 19:07:44 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i8GI7hTh029149; Thu, 16 Sep 2004 14:07:43 -0400 (EDT) To: Kevin Neufeld Cc: pgsql-performance@postgresql.org Subject: Re: declared cursor uses slow plan In-reply-to: <4148A13E.4080207@refractions.net> References: <4148A13E.4080207@refractions.net> Comments: In-reply-to Kevin Neufeld message dated "Wed, 15 Sep 2004 13:08:30 -0700" Date: Thu, 16 Sep 2004 14:07:43 -0400 Message-ID: <29148.1095358063@sss.pgh.pa.us> From: Tom Lane 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: 200409/168 X-Sequence-Number: 8296 Kevin Neufeld writes: > I have a relatively simple query that takes about 150ms using explain > analyze. However, when I wrap the same query in a declared cursor > statement, the subsequent fetch statement takes almost 30seconds. For > some reason, the planner decided to do a nested loop left join instead > of a hash left join. Does anyone know why the planner would choose this > course? Plans for cursors are optimized partly for startup speed as opposed to total time, on the assumption that you'd rather get some of the rows sooner so you can crunch on them. Probably there should be a knob you can fool with to adjust the strength of the effect, but at present I think it's hard-wired. The real problem here of course is that the total cost of the nestloop is being underestimated so badly (the estimate is only 5x more than the hash join where reality is 200x more). It looks like this is mainly because the number of matching rows from csn_waterbodies is badly underestimated, which comes from the fact that we have no useful statistics for geometric operators :-(. I think that the PostGIS crew is working that problem but I have no idea how far along they are... regards, tom lane