Re: BUG #5120: Performance difference between running a query with named cursor and straight SELECT

>"Steven McLellan" <smclellan(at)mintel(dot)com> writes:
>> I've found what appears to be a bug seriously affecting performance
running
>> a particular query using a named cursor versus running it as a simple
>> SELECT.

> You haven't shown us a plan for the cursor case, but I'm thinking the
> issue here is that Postgres prefers fast-start plans for cursors, on
> the theory that if you're using a cursor you probably care more about
> incremental fetching than the total elapsed time. As of 8.4 you can
> twiddle the strength of that preference via cursor_tuple_fraction.
>
http://www.postgresql.org/docs/8.4/static/runtime-config-query.html#GUC-CURSOR-TUPLE-FRACTION
>
> regards, tom lane

Thanks! Installing 8.4 and setting cursor_tuple_fraction to 1.0 does seem to
force it to execute in the same time as not using the cursor, and we'll
probably go with this solution (the only reason we're using cursors is to
avoid retrieving vast result sets through psycopg2/fetchmany). Your
explanation makes sense, and I'm curious to see why this particular query
ends up being so different, but I couldn't figure out how to run the explain
- the DECLARE syntax doesn't seem to allow it. Do I need to do it through
plpgsql?

Thanks again,

Steve McLellan