Postgres How does Postgres execute a join query with cursor?

Question

As opposed to a regular query, how does Postgres execute a join query when using a cursor and FETCH? Does it wait for the entire join to finish and just return a small number of rows? Or, does it do a partial join on a subset of both tables and return its result and so on?

Laurenz Albe · Accepted Answer · 2022-12-16 08:51:55Z

1

That depends on the join strategy chosen. A nested loop join can return the first rows quickly. A hash join will take longer to return the first few rows, and a merge join even longer than that. PostgreSQL prefers plans that can return the first 10 percent of the data if you use a cursor (configurable via cursor_tuple_fraction), but that means that fetching all the rows can become slower (PostgreSQL bets that it will take long until the application has fetched the whole result set).

answered Dec 16, 2022 at 8:51

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres How does Postgres execute a join query with cursor?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related