Re: Cursors and different settings for default_statistics_target

That's it - I found a more simple statement which has the same problem
(0.02 seconds vs. 6 seconds):

With cursor (6 seconds):
appcooelakdb2=> explain DECLARE curs_1 CURSOR FOR SELECT DISTINCT
t2.objid FROM atobjval t2 WHERE t2.aggrid = 0 AND t2.attrid =
281479288455385 ORDER BY t2.objid;
                                          QUERY PLAN
------------------------------------------------------------------------
----------------------
 Unique  (cost=0.00..1404823.63 rows=538 width=8)
   ->  Index Scan using atobjvalix on atobjval t2
(cost=0.00..1404751.32 rows=28925 width=8)
         Index Cond: ((attrid = 281479288455385::bigint) AND (aggrid =
0))


Without cursor (0.02 seconds)
appcooelakdb2=> explain SELECT DISTINCT t2.objid FROM atobjval t2 WHERE
t2.aggrid = 0 AND t2.attrid = 281479288455385 ORDER BY t2.objid;
                                          QUERY PLAN
------------------------------------------------------------------------
----------------------
 Unique  (cost=151717.85..151862.48 rows=538 width=8)
   ->  Sort  (cost=151717.85..151790.17 rows=28925 width=8)
         Sort Key: objid
         ->  Bitmap Heap Scan on atobjval t2  (cost=1692.40..149574.51
rows=28925 width=8)
               Recheck Cond: (attrid = 281479288455385::bigint)
               Filter: (aggrid = 0)
               ->  Bitmap Index Scan on ind_atobjval
(cost=0.00..1685.16 rows=59402 width=0)
                     Index Cond: (attrid = 281479288455385::bigint)

What's the difference between plan calculation for cursors and straight
queries?

Kind regards,
Robert

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Dienstag, 01. April 2008 17:30
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:
> When we use 20 as default_statistics_target the retrieval of the data
> takes 7.5 seconds - with 25 as default_statistics_target (with restart
> and analyze) it takes 0.6 seconds.
> The query plan is identical in both situations (row estimation differs
a
> little bit) - the query is always fast when it is executed without a
> cursor.

A cursor doesn't necessarily use the same plan as a straight query does.
Try "EXPLAIN DECLARE curs_285058224 CURSOR FOR ..." and see if you
aren't getting different plans in these two cases.

            regards, tom lane