plugins/sql: add payment_hash index to channelmoves table.
What the developer wrote
Authored by Rusty Russell
plugins/sql: add payment_hash index to channelmoves table.
This significantly speeds up the query which bookkeeper often does:
"SELECT created_index"
" FROM channelmoves"
" WHERE payment_hash = X'%s'"
" AND credit_msat = %"PRIu64
" AND created_index <= %"PRIu64,
On large databases this scan is expensive, and a payment_hash index
cuts it down a great deal. It does take longer to load the channelmoves
in the first place though (about 3x).
Before:
$ while sleep 10; do wc -l /tmp/bkpr-progress; done
169505 /tmp/bkpr-progress
196010 /tmp/bkpr-progress
219370 /tmp/bkpr-progress
235671 /tmp/bkpr-progress
244242 /tmp/bkpr-progress
255362 /tmp/bkpr-progress
265636 /tmp/bkpr-progress
276966 /tmp/bkpr-progress
284451 /tmp/bkpr-progress
288836 /tmp/bkpr-progress
296578 /tmp/bkpr-progress
304571 /tmp/bkpr-progress
After:
$ while sleep 10; do wc -l /tmp/bkpr-progress; done
161421 /tmp/bkpr-progress
238273 /tmp/bkpr-progress
281185 /tmp/bkpr-progress
305787 /tmp/bkpr-progress
Signed-off-by: Rusty Russell <rusty@rustcorp.com.au>
Changelog-Changed: plugins: the sql plugin now keeps an index on `channelmoves` by `payment_hash`.
What changed, and why it matters
This commit is a routine performance improvement for the Core Lightning SQL plugin. It adds a database index on the payment_hash column of the channelmoves table so that bookkeeping queries run faster on large databases. There is no security fix or vulnerability here.
No security action needed. Treat as a normal performance optimization.
Security signals we found
No strong security signals were identified.
Evidence from the diff
The change adds a new entry to the static indices[] array in plugins/sql.c for the channelmoves table on the payment_hash column. It also updates fmt_indexes() to support formatting multiple indices for a single table instead of asserting there is only one, and updates the corresponding test expectation in tests/test_plugin.py. The stated purpose is to speed up a bookkeeper query that filters channelmoves by payment_hash, credit_msat, and created_index. No security-sensitive logic is modified.
Changed components
plugins/sql.ctests/test_plugin.pyInspect captured patch +15 / −9
diff --git a/plugins/sql.c b/plugins/sql.c
index 7b7e21bf..a1c361da 100644
--- a/plugins/sql.c
+++ b/plugins/sql.c
@@ -197,6 +197,10 @@ static const struct index indices[] = {
"channelmoves",
{ "account_id", NULL },
},
+ {
+ "channelmoves",
+ { "payment_hash", NULL },
+ },
};
static enum fieldtype find_fieldtype(const jsmntok_t *name)
@@ -1757,20 +1761,22 @@ static const char *fmt_indexes(const tal_t *ctx, const char *table)
for (size_t i = 0; i < ARRAY_SIZE(indices); i++) {
if (!streq(indices[i].tablename, table))
continue;
- /* FIXME: Handle multiple indices! */
- assert(!ret);
+ if (!ret)
+ ret = tal_fmt(ctx, " indexed by ");
+ else
+ tal_append_fmt(&ret, ", also indexed by ");
BUILD_ASSERT(ARRAY_SIZE(indices[i].fields) == 2);
if (indices[i].fields[1])
- ret = tal_fmt(tmpctx, "`%s` and `%s`",
- indices[i].fields[0],
- indices[i].fields[1]);
+ tal_append_fmt(&ret, "`%s` and `%s`",
+ indices[i].fields[0],
+ indices[i].fields[1]);
else
- ret = tal_fmt(tmpctx, "`%s`",
- indices[i].fields[0]);
+ tal_append_fmt(&ret, "`%s`",
+ indices[i].fields[0]);
}
if (!ret)
return "";
- return tal_fmt(ctx, " indexed by %s", ret);
+ return ret;
}
static const char *json_prefix(const tal_t *ctx,
diff --git a/tests/test_plugin.py b/tests/test_plugin.py
index 82f832d4..48f4dcfe 100644
--- a/tests/test_plugin.py
+++ b/tests/test_plugin.py
@@ -3795,7 +3795,7 @@ def test_sql(node_factory, bitcoind):
{'name': 'extra_tags',
'type': 'string'}]},
'channelmoves': {
- 'indices': [['account_id']],
+ 'indices': [['account_id'], ['payment_hash']],
'columns': [{'name': 'created_index',
'type': 'u64'},
{'name': 'account_id',
Why this scored 15/100
Community notes
Notes can correct, qualify, or add evidence to the AI analysis. Every note shown here has been validated by a human moderator.
The AI analysis stands alone for now. Submit a note if you can add evidence or important context.