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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
