pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Hell, Robert <Robert.Hell@fabasoft.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Cursors and different settings for default_statistics_target
Date: Tue, 1 Apr 2008 18:33:19 +0200
Message-ID: <B710F3299F04664DB6B37C258FDEEB940158B0FC@FABAMAIL.fabagl.fabasoft.com> (raw)
In-Reply-To: <9629.1207066619@sss.pgh.pa.us>
References: <B710F3299F04664DB6B37C258FDEEB940158AFF9@FABAMAIL.fabagl.fabasoft.com>
	<2798.1207063805@sss.pgh.pa.us>
	<B710F3299F04664DB6B37C258FDEEB940158B0E9@FABAMAIL.fabagl.fabasoft.com>
	<9629.1207066619@sss.pgh.pa.us>

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,<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[]))


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] 
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 

"Hell, Robert" <Robert.Hell@fabasoft.com> 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



view thread (10+ messages)  latest in thread

Message-ID: <B710F3299F04664DB6B37C258FDEEB940158B0FC@FABAMAIL.fabagl.fabasoft.com>
Permalink:  ../B710F3299F04664DB6B37C258FDEEB940158B0FC@FABAMAIL.fabagl.fabasoft.com/
Also on:    postgresql.org/message-id/B710F3299F04664DB6B37C258FDEEB940158B0FC@FABAMAIL.fabagl.fabasoft.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: Robert.Hell@fabasoft.com, tgl@sss.pgh.pa.us
  Subject: Re: Cursors and different settings for default_statistics_target
  In-Reply-To: <B710F3299F04664DB6B37C258FDEEB940158B0FC@FABAMAIL.fabagl.fabasoft.com>
  References: <B710F3299F04664DB6B37C258FDEEB940158AFF9@FABAMAIL.fabagl.fabasoft.com> <2798.1207063805@sss.pgh.pa.us> <B710F3299F04664DB6B37C258FDEEB940158B0E9@FABAMAIL.fabagl.fabasoft.com> <9629.1207066619@sss.pgh.pa.us> <B710F3299F04664DB6B37C258FDEEB940158B0FC@FABAMAIL.fabagl.fabasoft.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox