Re: select vs cursor/fetch speed disparity

Bosco Rama wrote:
> Tom Lane wrote:
>>
>> Cursors are biased towards fast-start plans on the theory that you
>> may not be intending to fetch the whole result. Queries with ORDER BY
>> and/or LIMIT are particularly likely to see plan changes as a
>> consequence of that. In 8.4 and up you can frob the
>> cursor_tuple_fraction setting to adjust this preference. Use
>> "EXPLAIN query" vs "EXPLAIN DECLARE CURSOR FOR query" to see what
>> sort of plan you're getting.
>
> I'll take a look at that setting and try the two 'explain's. However,
> would that really account for an increase in time by a factor of ~630?
> Just wondering.

Apparently it does. Setting cursor_tuple_fraction to 1.0 alleviates
the problem in my test system (need end of day before I can test it in
the production system). Thanks for the hint Tom.

I guess my question now is: Why does it affect this query so badly?
This is the only one that has exhibited such behavior (... so far).

Also, is it a bad thing to set that value to 1.0 as the DB-wide setting?
Not understanding the internals that well I'm not too sure what exactly
is happening when I fool with this value. I assume you guys set the
default to 0.1 based on some sort of generalized testing during the
development/testing phase(s).

> (BTW, I'm still working on a public version of the data & schema that
> reproduce this.)

I now have a small(er) sanitized example that shows this problem with
a slow-down factor of 100 (rather than the factor of 630 I had before).
Is it of any interest or is the above GUC setting all we care about?

Bosco.