Looks much better when using 0.0 for tuple_fraction in case of a cursor instead of 0.1.
But why are the first 15 fetches (15360 rows) processed in 0.5 seconds and the last fetch (998 rows) takes 7 seconds.
Are we just unlucky that the last fetch takes that long?
EXPLAIN SELECT objid, attrid, aggrid, lineid, objval FROM atobjval WHERE objid IN (281479288456304,<many of
them>,285774255837674)ORDER BY objid, attrid, aggrid, lineid;
QUERY PLAN
----------------------------------------------------------------------------------------------
Sort (cost=116851.38..117196.22 rows=137935 width=32)
Sort Key: objid, attrid, aggrid, lineid
-> Bitmap Heap Scan on atobjval (cost=4947.40..105076.13 rows=137935 width=32)
Recheck Cond: (objid = ANY ('{281479288456304,<many of them>,285774255837674}'::bigint[]))
-> Bitmap Index Scan on atobjvalix (cost=0.00..4912.92 rows=137935 width=0)
Index Cond: (objid = ANY ('{281479288456304,<many of them>,285774255837674}'::bigint[]))
explain DECLARE curs_285058224 CURSOR FOR SELECT objid, attrid, aggrid, lineid, objval FROM atobjval WHERE objid IN
(281479288456304,<manyof them>,285774255837674) ORDER BY objid, attrid, aggrid, lineid;
QUERY PLAN
----------------------------------------------------------------------------------------------
Index Scan using atobjvalix on atobjval (cost=0.00..1041413.49 rows=137935 width=32)
Filter: (objid = ANY ('{281479288456304,<many of them>,285774255837674}'::bigint[]))
Regards,
Robert
-----Ursprüngliche Nachricht-----
Von: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Gesendet: Dienstag, 01. April 2008 18:42
An: Hell, Robert
Cc: pgsql-performance@postgresql.org
Betreff: Re: [PERFORM] Cursors and different settings for default_statistics_target
"Hell, Robert" <Robert.Hell@fabasoft.com> writes:
> That's CURSOR_OPT_FAST_PLAN and isn't it? Our application reads the full
> results of most cursors.
Just out of curiosity, why use a cursor at all then? But anyway, you
might want to consider running a custom build with a higher setting for
tuple_fraction for OPT_FAST_PLAN (look into planner.c). I've
occasionally thought about exposing that as a GUC parameter, but
never gotten motivated to do it.
regards, tom lane
