When does a SELECT query begin to return rows?

Question

Suppose the following query:

SELECT * FROM table;

Will the DBMS give me the first row as soon as it fetched it or will it first fetch all the rows (save them in some kind of buffer) and then give me all the rows at once?

If my question was not clear. Suppose that the amount of rows in the table is such that the DBMS will take exactly 60 minutes to fetch all the rows. Will the DBMS return the rows progressively through the 60 minutes, or will I have to wait 60 minutes before receiving any data?

Answer 1

In PostgreSQL, the server will indeed return rows to the client as soon as they are available if the query execution plan permits it. This is the case in your simple example. In other cases, if you might have a sort at the end, for example, and will have to wait for that to finish.

But if you use the standard libpq interface, the client library will build up the entire result in memory before it returns it to the client program. To get the results row by row, you need to use the single-row mode in libpq. If you use other interfaces or other languages, results might vary.