LIMIT and ORDER BY pushdown in ASOF queries

[jerrinot]

Is your feature request related to a problem?

Consider this query:

SELECT *
FROM orders o
ASOF JOIN market_data md on o.order_symbol = md.market_data_symbol
ORDER BY o.ts DESC
LIMIT 100;

It first executes ASOF join and then sorts and limits the result:

Describe the solution you'd like.

The query only uses columns from the master table for ordering -> It should be possible to push both the LIMIT and ORDER BY down to the master factory and execute it before the join. The join operator would have to join just 100 rows.

Describe alternatives you've considered.

No response

Full Name:

Jaromir Hamala

Affiliation:

QuestDB

Additional context

No response

[siddharth0815]

@jerrinot please validate my understanding here :
Suppose there are 200 records in both order (sorted by ts ASC) and market_data table
The result will anyway have last 100 records order table after performing ASOF join with market_data
So why not just shortlist those first and then perform join rather than doing vice versa, which will reduce number of joins

Should expected plan look like this?

Also, the number of iterations in join will be reduced regardless of orderby column if limit clause is there

[siddharth0815]

I have thought of an approach
For a simple query like:

 select * from trades ASOF JOIN order_book
order by trades.timestamp desc limit 5

The query model before executing given function : https://github.com/questdb/questdb/blob/master/core/src/main/java/io/questdb/griffin/SqlOptimiser.java#L6682
Looks like :

select-choose trades.timestamp timestamp, trades.price price, trades.size size, order_book.timestamp timestamp1, order_book.bid_price bid_price, order_book.bid_size bid_size, order_book.ask_price ask_price, order_book.ask_size ask_size from (trades timestamp (timestamp) order by trades.timestamp desc) limit 5

Where nestedModel is trades timestamp (timestamp) order by trades.timestamp desc

Later we add ASOF join with join model in the nested model itself.

Approach:

• Create a function before optimiseJoins() which could add a new-nested(Level-1) to current nested-model (Level-0) with limit and order advice so that it will be resolved first
• Created a model taking limiLo expression node from parent-model(Level- -1) and orderBy from nestedModel(Level-0)
• Add it to the current nestedModel(Level-0)
• Now ASOF join will clause will be added in optimiseJoins()
• This has to execute recursively

[ShivA200111118]

Please assign this issue to me