Search code examples
questdb

How to speed up this QuestDB query?

I have a query

select sym, price
from core_price
latest on ts PARTITION by sym
order by sym;

and it runs fast, in milliseconds. However this one on nearly identical by size and structure table runs in tens of seconds

select ccy1, ccy2, rate
from fx_price
latest on ts PARTITION by ccy1, ccy2
order by ccy1, ccy2

There result of both queiries is in similar range of few hundred row count.

Why the second query is 100 times slower and how can I speed it up?

Solution

  • The reason the first query is much faster than the second one is that QuestDB can stop searching for latest price by symbol when it finds all the distinct symbols.

    The second query has LATEST BY with 2 symbols and QuestDB does not optimise to stop early when it finds all the combinations of the symbol pairs. Hence it has to scan the whole table all the time to execute the second query.

    If the data in currency pairs traded every day and there is always a pair of currencies within last 24 hours, you can limit the backward scan of the second query to 1 day like this:

    select ccy1, ccy2, rate
from fx_price
WHERE timestamp > dateadd('d', -1, now())
latest on ts PARTITION by ccy1, ccy2
order by ccy1, ccy2