Parameter · Query Tuning / Other Planner Options
cursor_tuple_fraction
Sets the planner's estimate of the fraction of a cursor's rows that will be retrieved.
- Default:
0.1 - Type: real
- Range: 0 … 1
- Applies: Session (any user)
In plain words
When you open a cursor, the program might only read the first few rows. By default the planner assumes 10% will be fetched, so it favours plans that return the first rows quickly. Set it to 1.0 if your cursors always read everything.
For DBAs and operators
For a DECLARE … CURSOR (also WITH HOLD) the planner compares plans by startup + fraction × (total − startup) instead of total cost. A value of 1.0 means "use total cost" like a normal query; 0 is treated as a tiny fraction (purely the cost to the first row).
This affects applications that use cursors without spelling out DECLARE: PL/pgSQL explicit cursors (OPEN, FETCH) and driver-side cursors that send DECLARE (for example psycopg server-side cursors). Verified on 18.6: a PL/pgSQL FOR row IN query LOOP is not planned this way; it got the total-cost plan while OPEN on the same query got the fast-start plan. A batch job that reads all rows through a cursor may get a slow fast-start plan (index scan of the whole table, nested loops) where a sequential scan + hash join would finish sooner; setting 1.0 for that session fixes it.
The animation uses illustrative costs: an index scan that starts at once but costs 40,000 in total versus a sort that costs 29,404 before the first row and 29,904 in total. The choice flips at about 0.74.
How to change it
Any user can change it for their session with SET.