Re: Cursors and different settings for default_statistics_target

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,<many of 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(at)sss(dot)pgh(dot)pa(dot)us]
Gesendet: Dienstag, 01. April 2008 18:42
An: Hell, Robert
Cc: pgsql-performance(at)postgresql(dot)org
Betreff: Re: [PERFORM] Cursors and different settings for default_statistics_target

"Hell, Robert" <Robert(dot)Hell(at)fabasoft(dot)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