select vs cursor/fetch speed disparity

Hi folks,

I have a strange disparity between a query that is run as a
straight select and the same query via a cursor. I hope I can
jog someone's memory with the description as I have been unable
to create a sanitized and/or reduced data set & schema that will
reproduce this ... so far. :-(

Running on Ubuntu 10.04 LTS fully updated, PG 8.4.8 and the machine
is no slouch.

I have the following tables and select that express the 'shape' of
the query while they don't actually produce the problem (names and
faces changed to protect the innocent):

===================================================================
create table parent (
uid serial8 NOT NULL primary key,
bits int8 NOT NULL,
status integer NOT NULL
);

create table subs (
uid serial8 NOT NULL primary key,
bits int8 NOT NULL,
parent int8 NOT NULL,
name varchar(127) NOT NULL
);

select p.uid, p.status
from parent p
where (p.bits & 1) = 0 and
(p.status in ( 5,8,9,10,11,14)) and
(p.uid in (select s.parent
from subs s
where (s.bits & 1) = 0 and
s.parent != -1 and
lower(s.name) like lower('%xyz%')
)
)
order by p.uid desc;
===================================================================

(The tables above represent a much reduced table 'width' as they have
many more fields in our DB.)

When I run the query above (which is actually machine generated and
identical to the one causing the issue) on our data-set as a simple
'select' the query takes ~75ms according to \timing.

When I run the following sequence:
start transaction;
declare xyz cursor for (the above select)
fetch xyz;
rollback;

the 'fetch' takes ~47.3 seconds (i.e. ~47300ms).

In our system the 'parent' table only has ~11k rows and the 'subs'
table only has ~60k rows.

One note that may be important is that the PG backend process that
is running the fetch pegs the CPU it is running on at 100% during
the entire running time for the operation. (The machine has dual
quad core Opterons & 32GB of RAM.)

I sure hope this reminds someone of some problem I wasn't able to
find in the archives. In the meantime I will be working on a test
case that reproduces the problem.

TIA.

Bosco.