Most of the planner cost parameters are about modeling the hardware — how expensive is a random page fetch, how expensive is a CPU cycle. cursor_tuple_fraction is different. It’s about modeling you: specifically, the planner’s guess at how much of a cursor’s result you actually intend to fetch.
The default is 0.1. Context is user. The range is 0.0 to 1.0, and the number is a fraction — the planner’s estimate of what proportion of a cursor’s rows will be retrieved before the cursor is closed or abandoned.
Why a cursor is planned differently from a query
When you run an ordinary SELECT, PostgreSQL assumes you want the whole result, and the planner minimizes the total cost of producing all of it. When you DECLARE a cursor and FETCH from it, that assumption no longer holds. Cursors exist precisely so that a program can pull rows incrementally — read a few, do some work, read a few more, and quite possibly stop early. Planning a cursor for minimum total cost would be a mistake if the caller only ever fetches the first screenful.
So the planner treats cursors specially. At cursor_tuple_fraction = 0.1, it plans on the theory that only 10% of the rows will be fetched, and it prefers a plan that gets those first rows out the door quickly even if the cost of fetching everything would be higher. This is the same machinery that makes LIMIT change a plan: a query that would use a sequential scan and a sort to compute the whole result might, under a fast-start bias, switch to an index scan that produces ordered rows immediately and pays as it goes.
Tom Lane’s standard explanation on the mailing lists, when someone reports that the same query is wildly slower through a cursor than as a plain SELECT, is exactly this: cursors are biased toward fast-start plans on the theory that you may not fetch the whole result, and queries with ORDER BY or LIMIT are the ones most likely to see the plan flip. The way to confirm it is to compare EXPLAIN query against EXPLAIN DECLARE CURSOR FOR query — the two will sometimes show entirely different plans.
The knob
Lowering the value (toward 0.0) makes the planner even more eager to optimize for the first few rows — appropriate when your cursors genuinely are used to peek at the top of a large result and then stop. Raising it (toward 1.0) tells the planner to care more about total time. At exactly 1.0, cursors are planned identically to regular queries: total cost only, no fast-start bias at all.
The cases where the default actively hurts are the ones worth recognizing. If you declare a cursor and then do read the entire result — a common pattern for streaming a large result set through a cursor to keep client memory bounded — the fast-start bias is working against you. It chose a plan optimized for the first 10% when you wanted the cheapest way to fetch 100%. The fast-start plan can be dramatically slower over the full scan: an index scan that looked great for the first thousand rows becomes a liability across ten million. For that workload, SET cursor_tuple_fraction = 1.0 before declaring the cursor is the fix, and it’s the right setting for any session whose cursors are really just memory-bounded full reads.
The JDBC trap
Here’s the one that costs people real time. The PostgreSQL JDBC driver implements its cursor-like fetching by repeatedly sending an execute message for the same portal with a row-count limit. That’s a legitimate technique, but it means the server has no idea at planning time that the results will be fetched in chunks — from the planner’s point of view it’s an ordinary query. Which means it’s planned with total-cost optimization, not the cursor fast-start path, regardless of what cursor_tuple_fraction is set to.
The consequence is counterintuitive: the JDBC driver can never benefit from fast-start plans, and cursor_tuple_fraction has no effect on it unless you put an explicit LIMIT in the query. If you’ve been setting this parameter expecting it to change JDBC cursor behavior, it has been doing nothing. The behavior you want from JDBC comes from setFetchSize() and an explicit LIMIT, not from this GUC.
There’s a related diagnostic frustration worth knowing about, since it explains why this is so hard to debug: the fast-start optimization isn’t visible in EXPLAIN output. The plan tree looks the same whether or not the fast-start flag was set; only the cost numbers driving the choice differ, and those aren’t labeled “fast start.” You’re left comparing two EXPLAINs and inferring the bias from which plan won.
The default of 0.1 is correct for the original use case — interactive cursors that fetch a page and stop. If your cursors are memory-bounded full reads, set it to 1.0 for those sessions. And if your cursors are really the JDBC driver’s chunked fetching, this parameter isn’t the lever you’re looking for, and the time you’d spend tuning it is better spent adding a LIMIT.