1 
SELECT * FROM table_1 ORDER BY time;

The above query will query all rows sitting in table_1, while sorting all the rows by a column time. However, if the size of the table gets millions of rows, fetching all rows in a table will be inefficient with the addition of ORDER BY time. However luckily, the table I have is a time-series table that uses TimescaleDB extension, and all rows are inserted in timely order.

In this case, what would be the most efficient way of fetching everything in a table, while ensuring the resulting query is ordered by time?

Do I just delete ORDER BY time? If I remove this, can I be sure that the resulting query will ALWAYS be in a order of time?

Also, I've heard that PostgreSQL has some driver issues when fetching all rows in a table, because its optimized for querying small fraction of data in a table. How can I optimize the performance?

Improve this question

1 Answer 1

Reset to default
2

There is no way to ensure the order of a dataset returned by a SELECT statement without using an explicit ORDER BY clause; this is true even if the data is stored within the table in an ordered manner. If you must have the data ordered, it is safest to define an ORDER BY clause.

Running a SELECT * against a TimescaleDB Hypertable isn't going to be efficient. TimescaleDB stores data in chunks within a hypertable, the idea being that you time-bound your query such that it hits a single specific chunk, which results in optimal performance. When you run a query that must hit all records in the table, it now has to look at all data contained within all of the chunks, and the only way to do this is via a sequential scan.

This leads to your question about PostgreSQL and fetching all rows. PostgreSQL leverages parallel sequential scans when a large portion of a table is going to be hit by a query. While this is going to yield better performance than a single-shot sequential scan, it's still not going to be as quick as index scan pulling a subset of data would be against the same table.

What is the reasoning behind needing to query all rows in the table? By virtue that any SQL engine would have to look at each row at least once in order to ensure that all rows are returned, there is no way that a SELECT * is ever going to be able to leverage the lookup advantages associated with hashing and indexing.

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.