Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id C44072E0079 for ; Tue, 1 Apr 2008 13:35:31 -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 97846-07 for ; Tue, 1 Apr 2008 13:35:18 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from fabaglexfr.fabasoft.com (fabaglexfr.fabasoft.com [192.84.221.196]) by developer.postgresql.org (Postfix) with ESMTP id 9ACB12E006F for ; Tue, 1 Apr 2008 13:35:17 -0300 (ADT) Received: from FABAMAIL.fabagl.fabasoft.com ([10.10.5.137]) by fabaglexfr.fabasoft.com with InterScan Messaging Security Suite; Tue, 01 Apr 2008 18:34:33 +0200 Content-class: urn:content-classes:message Subject: Re: Cursors and different settings for default_statistics_target MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Date: Tue, 1 Apr 2008 18:33:19 +0200 x-mimeole: Produced By Microsoft Exchange V6.5 Message-ID: In-Reply-To: <9629.1207066619@sss.pgh.pa.us> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [PERFORM] Cursors and different settings for default_statistics_target Thread-Index: AciUE421Cf3vO8vVRiWL1Vsoam7MDgAAIwAg References: <2798.1207063805@sss.pgh.pa.us> <9629.1207066619@sss.pgh.pa.us> From: "Hell, Robert" To: "Tom Lane" Cc: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200804/11 X-Sequence-Number: 29504 Here are the query plans for the original query - looks very similar (to me): EXPLAIN SELECT objid, attrid, aggrid, lineid, objval FROM atobjval WHERE objid IN (281479288456304,,285774255837674) ORDER BY objid, attrid, aggrid, lineid; QUERY PLAN ------------------------------------------------------------------------ ---------------------- Sort (cost=3D116851.38..117196.22 rows=3D137935 width=3D32) Sort Key: objid, attrid, aggrid, lineid -> Bitmap Heap Scan on atobjval (cost=3D4947.40..105076.13 rows=3D137935 width=3D32) Recheck Cond: (objid =3D ANY ('{281479288456304,,285774255837674}'::bigint[])) -> Bitmap Index Scan on atobjvalix (cost=3D0.00..4912.92 rows=3D137935 width=3D0) Index Cond: (objid =3D ANY ('{281479288456304,,285774255837674}'::bigint[])) explain DECLARE curs_285058224 CURSOR FOR SELECT objid, attrid, aggrid, lineid, objval FROM atobjval WHERE objid IN (281479288456304,,285774255837674) ORDER BY objid, attrid, aggrid, lineid; QUERY PLAN ------------------------------------------------------------------------ ---------------------- Index Scan using atobjvalix on atobjval (cost=3D0.00..1041413.49 rows=3D137935 width=3D32) Filter: (objid =3D ANY ('{281479288456304,,285774255837674}'::bigint[])) That's CURSOR_OPT_FAST_PLAN and isn't it? Our application reads the full results of most cursors. Regards, Robert -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20 Sent: Dienstag, 01. April 2008 18:17 To: Hell, Robert Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Cursors and different settings for default_statistics_target=20 "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