CODEWITHSUNDEEP
pythonsqlpostgresqlsqlalchemy
codeape
codeape

Reputation: 100766

Why does not postgresql start returning rows immediately?

The following query returns data right away:

SELECT time, value from data order by time limit 100;

Without the limit clause, it takes a long time before the server starts returning rows:

SELECT time, value from data order by time;

I observe this both by using the query tool (psql) and when querying using an API.

Questions/issues:

The column time is the primary key, BTW.

EDIT:

I believe this excerpt from the JDBC driver documentation describes the problem and hints at a solution (I still need help - see the last bullet list item above):

By default the driver collects all the results for the query at once. This can be inconvenient for large data sets so the JDBC driver provides a means of basing a ResultSet on a database cursor and only fetching a small number of rows.

and

Changing code to cursor mode is as simple as setting the fetch size of the Statement to the appropriate size. Setting the fetch size back to 0 will cause all rows to be cached (the default behaviour).

// make sure autocommit is off
conn.setAutoCommit(false);
Statement st = conn.createStatement();

// Turn use of the cursor on.
st.setFetchSize(50);

Upvotes: 7

Views: 1491

Answers (2)

Ants Aasma
Ants Aasma

Reputation: 54882

The psycopg2 dbapi driver buffers the whole query result before returning any rows. You'll need to use server side cursor to incrementally fetch results. For SQLAlchemy see server_side_cursors in the docs and if you're using the ORM the Query.yield_per() method.

SQLAlchemy currently doesn't have an option to set that per single query, but there is a ticket with a patch for implementing that.

Upvotes: 4

Carl Smotricz
Carl Smotricz

Reputation: 67760

In theory, because your ORDER BY is by primary key, a sort of the results should not be necessary, and the DB could indeed return data right away in key order.

I would expect a capable DB of noticing this, and optimizing for it. It seems that PGSQL is not. * shrug *

You don't notice any impact if you have LIMIT 100 because it's very quick to pull those 100 results out of the DB, and you won't notice any delay if they're first gathered up and sorted before being shipped out to your client.

I suggest trying to drop the ORDER BY. Chances are, your results will be correctly ordered by time anyway (there may even be a standard or specification that mandates this, given your PK), and you might get your results more quickly.

Upvotes: 0

Related Questions