Is your feature request related to a problem?

Noticed that limit -1 can be quite slow, specially while ingesting data on a large table.

This can be tested on demo box with the new dataset.

select * from market_data limit -1;
select * from market_data latest by symbol;

Explain says latest on does a frame backward scan, while limit -1 needs to skip over billions of frames, then do a row forward scan.

Limit lo: -1 skip-over-rows: 154330575 limit: 1
    PageFrame
        Row forward scan
        Frame forward scan on: core_price

or for a larger table

Limit lo: -1 skip-over-rows: 1744670104 limit: 1
    PageFrame
        Row forward scan
        Frame forward scan on: market_data

I’ve observed during heavy ingestion latest on is way faster (few ms) than limit -1 (several seconds)

Equivalent plans for the latest on

LatestByDeferredListValuesFiltered
    Frame backward scan on: core_price

LatestByDeferredListValuesFiltered
    Frame backward scan on: market_data

As hinted by @puzpuzpuz, I tried

select * from market_data order by timestamp desc limit 1;

And this was good, just a few milliseconds. With this plan

Limit lo: 1 skip-over-rows: 0 limit: 1
    PageFrame
        Row backward scan
        Frame backward scan on: market_data

Describe the solution you'd like.

I would expect limit -1 to apply the same frame backward strategy that the order by desc limit 1 is doing

Describe alternatives you've considered.

No response

Full Name:

javier

Affiliation:

questdb

Additional context

No response