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(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Dienstag, 01. April 2008 17:30
To: Hell, Robert
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Cursors and different settings for
default_statistics_target

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