Postgresql Cursor With "order By" Clause

Let's suppose there is a Query called A and it takes 2sec. SELECT ... FROM ... ORDER BY 'users_device'.'id' # Query A # It contains join clause. # It takes 2sec However, When I

Solution 1:

I can't find this in documentation but I'd speculate, that when you use cursor the database looks more at estimated start-up cost (the time to the first row of output) than estimated total cost (the time to the last row of output).

In your example the slow plan is estimated to output the first row in 1.85 cost-units, and the fast plan in 433944.70 cost-units. So it looks like the database prefers the slow plan when you used cursor to be able to provide partial results as soon as possible.

This seems reasonable - you used a cursor instead of an ordinary query, probably because you prefer to start working on your data as soon as possible.

I think you can make it work fast and still retrieve data in chunks with fetch by explicitly creating the temporary table:

create temporary table t asselect ... /* skip order by */;
declare c cursorwithholdforselect*from t orderby id;

---

As @mastaBlasta pointed out in a comment there's an option that controls for how much is first result preferred over a whole result for cursors: cursor_tuple_fraction.