Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id B94E02E007B for ; Tue, 1 Apr 2008 13:17:07 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 92487-07 for ; Tue, 1 Apr 2008 13:17:02 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by developer.postgresql.org (Postfix) with ESMTP id CDA312E0072 for ; Tue, 1 Apr 2008 13:17:01 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m31GGxos009630; Tue, 1 Apr 2008 12:16:59 -0400 (EDT) To: "Hell, Robert" cc: pgsql-performance@postgresql.org Subject: Re: Cursors and different settings for default_statistics_target In-reply-to: References: <2798.1207063805@sss.pgh.pa.us> Comments: In-reply-to "Hell, Robert" message dated "Tue, 01 Apr 2008 17:48:04 +0200" Date: Tue, 01 Apr 2008 12:16:59 -0400 Message-ID: <9629.1207066619@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/10 X-Sequence-Number: 29503 "Hell, Robert" writes: > That's it - I found a more simple statement which has the same problem > (0.02 seconds vs. 6 seconds): This isn't necessarily the very same problem --- what are the plans for your original case with the two different stats settings? > What's the difference between plan calculation for cursors and straight > queries? The planner is set up to favor fast-start plans a little bit when planning a cursor, on the theory that you are probably more interested in getting some of the rows sooner than you are in the total runtime, and that you might not ever intend to fetch all the rows anyway. In the example you give here, it likes the indexscan/unique plan because of the zero startup cost, even though the total cost is (correctly) estimated as much higher. (Looking at this example, I wonder if the fast-start bias isn't a bit too strong...) It's not immediately apparent to me though how that would affect your original query. regards, tom lane