I have a problem with fetching from cursors sometimes taking an
extremely long time to run. I am attempting to use the
statement_timeout parameter to limit the runtime on these.
PostgreSQL 8.2.4
Linux 2.6.22.14-72.fc6 #1 SMP Wed Nov 21 13:44:07 EST 2007 i686 i686
i386 GNU/Linux
begin;
set search_path = testdb;
declare cur_rep cursor for select * from accounts, individual;
set statement_timeout = 1000;
fetch forward 1000000 from cur_rep;
The open join, 1000ms, and 1000000 count are all intentional. Normally
those values would be 300000 and 10000. The accounts and individual
tables have around 100 fields and 500k records each.
Nested Loop (cost=21992.28..8137785497.71 rows=347496704100 width=8)
-> Seq Scan on accounts (cost=0.00..30447.44 rows=623844 width=8)
-> Materialize (cost=21992.28..29466.53 rows=557025 width=0)
-> Seq Scan on individual (cost=0.00..19531.25 rows=557025
width=0)
I tried moving the SET statment before the cursor delcaration and
outside the transaction with the same results. I thought possibly it
was getting bogged down in I/O but the timeout seems to work fine if not
using a cursor.
What am I missing here?
Thanks,
Joe
_____________
The information contained in this message is proprietary and/or confidential. If you are not the intended recipient,
please:(i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and
(iii)notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to
archivingand review by persons other than the intended recipient. Thank you.
_____________
