explain select cab_type, vendor_id, pickup_datetime from trips order by pickup_datetime desc, cab_type desc limit 100;
produces the following plan:
This seems suboptimal. In my opinion, instead of scanning the entire table, we should leverage the fact that the designated timestamp is the first column in the ORDER BY clause and apply a backward scan + LimitedSizePartiallySortedLightRecordCursor.
The same applies to queries with DISTINCT:
explain select DISTINCT cab_type, vendor_id, pickup_datetime from trips order by pickup_datetime desc, cab_type desc limit 100;
The trips table comes form QuestDB's demo.
QuestDB version:
7.3.9
OS, in case of Docker specify Docker and the Host OS:
Linux
File System, in case of Docker specify Host File System:
ext4
Full Name:
Piotr Rżysko
Affiliation:
QuestDB
Have you followed Linux, MacOs kernel configuration steps to increase Maximum open files and Maximum virtual memory areas limit?
To reproduce
While working on #4180, I noticed that:
produces the following plan:
This seems suboptimal. In my opinion, instead of scanning the entire table, we should leverage the fact that the designated timestamp is the first column in the ORDER BY clause and apply a backward scan +
LimitedSizePartiallySortedLightRecordCursor.The same applies to queries with DISTINCT:
The
tripstable comes form QuestDB's demo.QuestDB version:
7.3.9
OS, in case of Docker specify Docker and the Host OS:
Linux
File System, in case of Docker specify Host File System:
ext4
Full Name:
Piotr Rżysko
Affiliation:
QuestDB
Have you followed Linux, MacOs kernel configuration steps to increase Maximum open files and Maximum virtual memory areas limit?
Additional context
No response