[bug]: `ListPayments` perceived to users as slow operation

[mohamedawnallah]

Pre-Submission Checklist

• [x] I have searched the existing issues and believe this is a new bug.
• [x] I am not asking a question about how to use lnd, but reporting a bug (otherwise open a discussion).

LND Version

All versions till LND 20.0 inclusive

Who is mainly affected by this issue?

Binance so far

What can be bottleneck?

As lightly touched upon in internal discussions, it appears to be caused by the overlapping of large datasets and insufficient relevant indexing.

LND Configuration

N/A

Backend Version

N/A

Backend Configuration

N/A

OS/Distribution

N/A

Bug Details & Steps to Reproduce

N/A

Expected Behavior

N/A

Debug Information

No response

Environment

No response

[Roasbeef]

Moving payments over to a proper SQL schema will resolve this. Today if you do time based indexing for the command, it still scans every payment 1:1 (has to decode even) to see if it's in range. Only the sequence based index is actually fast, as it's able to properly seek.