Postgres How does Postgres execute a join query with cursor?

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?

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).