Feat: Support TopK on Order By ... Limit ...

[KKould]

Feature Request

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

Tips: TopK should also use index

[crwen]

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]

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)

#187 (comment)

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]

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