Search code examples
sqltime-seriesquestdb

Why is `last` Aggregation Slower than `ORDER BY` and `LIMIT`?

I'm getting a big difference in performance when selecting the most recent timestamp from a table in QuestDB using these two queries.

This query takes ~46ms.

SELECT timestamp FROM <table_name> ORDER BY timestamp DESC LIMIT 1

This one takes ~165ms.

SELECT last(timestamp) FROM <table_name>

Both queries return the same, but when using last is way slower. Am I missing something here?

Solution

  • The query with ORDER BY and LIMIT is highly optimized, especially when using the designated timestamp. Data in questdb tables is physically stored sorted by incremental timestamp, so we can efficiently retrieve the most recent entry.

    The last aggregation function is internally treated as a GROUP BY, so it needs to do a full scan to get the last value. At the moment QuestDB has no optimisations for no-op cases (i.e. when there is no meaningful aggregation). For those queries, you are better off rewriting with an alternative like you did.

    By the way, QuestDB accepts a negative limit, so you can do

    SELECT timestamp FROM <table_name> LIMIT -1

    To access the last row, as data is sorted by incremental designated timestamp.

    There is a related issue on GitHub to optimize First and Last aggregate functions automatically, but at the moment your best option is a query rewrite