CODEWITHSUNDEEP
sqlpostgresqlselect
André Puel
André Puel

Reputation: 9189

When does a SELECT query begin to return rows?

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?

Upvotes: 10

Views: 602

Answers (4)

Joe R.
Joe R.

Reputation: 2052

Most, if not all, SQL-based servers will not allow you to view any rows until the query completes searching. Some servers provide a FIRST ROWS directive (a hint) for the server to provide the first set of rows sooner. See my related SO question and answers for more info on this subject.

Upvotes: 1

Peter Eisentraut
Peter Eisentraut

Reputation: 36729

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.

Upvotes: 8

Andomar
Andomar

Reputation: 238136

There is no hard rule here. But in practice, the database engine should prefer to return rows as soon as they are available. The efficiency advantage is large and obvious.

Note that this is not possible for all queries. A very common example is an order by clause that has no supporting index. In order to sort, the database has to create a server-side copy of the table that is sorted. This means it can't start returning rows until the sort operation is completed.

Upvotes: 3

Danilo Piazzalunga
Danilo Piazzalunga

Reputation: 7802

It depends.

For example, the Oracle Database will optimize for throughput by default and try to return all the rows, or you can instruct the optimizer to return the first n rows with the `/*+ FIRST_ROWS(n) */ optimizer hint.

As for PostgresSQL, it doesn't have optmizer hints (see these two links). I can assume it will try to optimize for throughput by default.

Upvotes: 0

Related Questions