When querying the forwards table by channel using the SQL plugin, queries like:
SELECT * FROM forwards WHERE in_channel='735095x480x1'
appear to perform a full table scan even when an index exists on the underlying in_channel_scid integer column. I think this is because the plugin converts the scid string to an integer at the application layer rather than translating it into the in_channel_scid type (integer) before passing the query to SQLite.
As a result, SQLite cannot use the index I created. Instead, I see:
"EXPLAIN QUERY PLAN SELECT * FROM forwards WHERE in_channel_scid=735095480001 OR out_channel_scid=735095480001 LIMIT 10"
QUERY PLAN
--SCAN forwards
Expected behavior:
The plugin should translate in_channel='735095x480x1' into the in_channel_scid before executing the query, allowing SQLite to use existing or user-created indices.
Impact:
The forwards table can grow to hundreds of thousands of rows on active routing nodes. Without index support, channel-specific queries become slow as the table grows.
Workaround:
Users can query the underlying SQLite database directly using integer-encoded scids, bypassing the plugin entirely.
When querying the forwards table by channel using the SQL plugin, queries like:
SELECT * FROM forwards WHERE in_channel='735095x480x1'appear to perform a full table scan even when an index exists on the underlying in_channel_scid integer column. I think this is because the plugin converts the scid string to an integer at the application layer rather than translating it into the in_channel_scid type (integer) before passing the query to SQLite.
As a result, SQLite cannot use the index I created. Instead, I see:
Expected behavior:
The plugin should translate in_channel='735095x480x1' into the in_channel_scid before executing the query, allowing SQLite to use existing or user-created indices.
Impact:
The forwards table can grow to hundreds of thousands of rows on active routing nodes. Without index support, channel-specific queries become slow as the table grows.
Workaround:
Users can query the underlying SQLite database directly using integer-encoded scids, bypassing the plugin entirely.