search

KipData / FnckSQL

SQL as a Function for Rust
Apache License 2.0
567 stars 48 forks source link

Feat: Support `TopK` on `Order By ... Limit ...` #178

Open KKould opened 8 months ago

KKould commented 8 months ago

Feature Request

When OrderBy .. Limit .. exists, the TopK operator will be used

Tips: TopK should also use index

crwen commented 8 months ago

It seems that IndexScan will not be used, because the the calculated cost is None(StatisticsMeta file is not found, only Analyze can generate these files, but Analyze may have something wrong)

Analyze table t;
ERROR:  there are more buckets: 100 than elements: 0
KKould commented 8 months ago

It seems that IndexScan will not be used, because the the calculated cost is None(StatisticsMeta file is not found, only Analyze can generate these files, but Analyze may have something wrong)

Analyze table t;
ERROR:  there are more buckets: 100 than elements: 0

@crwen

Index arranges data in ascending order, so when OrderBy...ASC, is it possible to remove the Order Operator and convert SeqScan to IndexScan (it cannot be executed if IndexScan has already used other indexes)

https://github.com/KipData/FnckSQL/issues/187#issue-2214992372

Tips: Analyze Table can only be created when the amount of data is greater than 100. Maybe I need to make the information more user-friendly. If you want to use alert table, you can execute benchmark and the internal data will have an index.

KKould commented 8 months ago

Tips: IndexScan only makes sense based on large amounts of data. if not, there is not much difference with SeqScan. At the same time, Index data is arranged in ascending order. You can refer to the test cases here to understand the execution of index.

https://github.com/KipData/FnckSQL/blob/main/tests/slt/where_by_index.slt