mirror of
https://github.com/lightningnetwork/lnd.git
synced 2026-08-13 12:32:48 +02:00
e02f77ec9f
The invoice filter queries (FetchPendingInvoices, FilterInvoicesBySettleIndex, FilterInvoicesByAddIndex, FilterInvoicesForward, FilterInvoicesReverse) all used LIMIT+OFFSET for internal pagination. This causes SQLite to build an ephemeral temp B-tree for every page to implement the OFFSET skip, making each successive page O(offset+limit). On nodes with large invoice histories this compounds into a significant CPU cost — profiling showed FilterInvoicesReverse consuming 53% of total CPU, with _sqlite3BtreeInsert and _balance_nonroot (2.4s combined) appearing inside the SELECT due to the temp B-tree being built and rebalanced to skip rows. Replace the OFFSET loop (queryWithLimit) with cursor-based pagination across all four callers in sql_store.go: - FetchPendingInvoices: add id_cursor param, advance cursor to last_id + 1 each page. - InvoicesSettledSince: add id_cursor param alongside the existing settle_index lower bound, advance cursor to last_id + 1 each page. - InvoicesAddedSince: cursor starts at idx+1, advances to last_id+1. - QueryInvoices: forward cursor starts at IndexOffset+1 and advances by +1; reverse cursor starts at IndexOffset-1 (or MaxInt64) and advances by -1. Inclusive SQL bounds (>= / <=) are preserved so query semantics and all existing callers are unchanged. The queryWithLimit helper is removed as it has no remaining callers. Each page now performs a single PK seek + forward scan of exactly page_size rows with no temp sort structure, matching the cursor-based pattern already used by the payments filter queries.
221 lines
6.8 KiB
SQL
221 lines
6.8 KiB
SQL
-- name: InsertInvoice :one
|
||
INSERT INTO invoices (
|
||
hash, preimage, memo, amount_msat, cltv_delta, expiry, payment_addr,
|
||
payment_request, payment_request_hash, state, amount_paid_msat, is_amp,
|
||
is_hodl, is_keysend, created_at
|
||
) VALUES (
|
||
$1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15
|
||
) RETURNING id;
|
||
|
||
-- name: InsertMigratedInvoice :one
|
||
INSERT INTO invoices (
|
||
hash, preimage, settle_index, settled_at, memo, amount_msat, cltv_delta,
|
||
expiry, payment_addr, payment_request, payment_request_hash, state,
|
||
amount_paid_msat, is_amp, is_hodl, is_keysend, created_at
|
||
) VALUES (
|
||
$1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17
|
||
) RETURNING id;
|
||
|
||
|
||
-- name: InsertInvoiceFeature :exec
|
||
INSERT INTO invoice_features (
|
||
invoice_id, feature
|
||
) VALUES (
|
||
$1, $2
|
||
);
|
||
|
||
-- name: GetInvoiceFeatures :many
|
||
SELECT *
|
||
FROM invoice_features
|
||
WHERE invoice_id = $1;
|
||
|
||
-- name: GetInvoiceByHash :one
|
||
SELECT i.*
|
||
FROM invoices i
|
||
WHERE i.hash = $1;
|
||
|
||
-- name: GetInvoiceByAddr :one
|
||
SELECT i.*
|
||
FROM invoices i
|
||
WHERE i.payment_addr = $1;
|
||
|
||
-- name: GetInvoiceBySetID :many
|
||
-- TODO(ziggie): This query can only return one invoice if the set_id is
|
||
-- the primary key of amp_sub_invoices table.
|
||
SELECT i.*
|
||
FROM invoices i
|
||
INNER JOIN amp_sub_invoices a
|
||
ON i.id = a.invoice_id AND a.set_id = $1;
|
||
|
||
-- name: FetchPendingInvoices :many
|
||
-- FetchPendingInvoices returns all invoices in a pending state (open or
|
||
-- accepted). The invoices_state_idx index on the state column makes this a
|
||
-- fast index scan rather than a full table scan. id_cursor is an exclusive
|
||
-- lower bound on the primary key used for cursor-based pagination; the caller
|
||
-- must supply 0 when starting from the beginning.
|
||
SELECT
|
||
invoices.*
|
||
FROM invoices
|
||
WHERE state IN (0, 3) -- 0 = ContractOpen, 3 = ContractAccepted
|
||
AND id > @id_cursor
|
||
ORDER BY id ASC
|
||
LIMIT @num_limit;
|
||
|
||
-- name: FilterInvoicesBySettleIndex :many
|
||
-- FilterInvoicesBySettleIndex returns settled invoices whose settle_index is
|
||
-- greater than or equal to the given value, ordered by id. The caller must
|
||
-- always supply a concrete lower bound so the invoices_settle_index_idx index
|
||
-- can be used. id_cursor is an exclusive lower bound on the primary key used
|
||
-- for cursor-based pagination; the caller must supply 0 when starting from
|
||
-- the beginning.
|
||
SELECT
|
||
invoices.*
|
||
FROM invoices
|
||
WHERE settle_index >= @settle_index_get
|
||
AND id > @id_cursor
|
||
ORDER BY id ASC
|
||
LIMIT @num_limit;
|
||
|
||
-- name: FilterInvoicesByAddIndex :many
|
||
-- FilterInvoicesByAddIndex returns invoices whose add_index (primary key id)
|
||
-- is greater than or equal to the given value, ordered by id. Because id is
|
||
-- the primary key, this is always an efficient range scan on the clustered
|
||
-- index. For cursor-based pagination the caller advances add_index_get to
|
||
-- last_returned_id + 1 on each subsequent page.
|
||
SELECT
|
||
invoices.*
|
||
FROM invoices
|
||
WHERE id >= @add_index_get
|
||
ORDER BY id ASC
|
||
LIMIT @num_limit;
|
||
|
||
-- name: FilterInvoicesForward :many
|
||
-- FilterInvoicesForward returns invoices in ascending id order. All parameters
|
||
-- are non-nullable so the planner always sees plain range predicates and can
|
||
-- use the primary-key index. For cursor-based pagination the caller advances
|
||
-- add_index_get to last_returned_id + 1 on each subsequent page. The caller
|
||
-- is responsible for supplying Go-side defaults when a filter is not needed:
|
||
-- add_index_get → 1 (first valid invoice id)
|
||
-- created_after → time.Unix(0, 0).UTC() (epoch – before any invoice)
|
||
-- created_before → time.Date(9999, …) (far future – no upper cap)
|
||
-- pending_only → false (include all states)
|
||
SELECT
|
||
invoices.*
|
||
FROM invoices
|
||
WHERE id >= @add_index_get
|
||
AND (NOT @pending_only OR state IN (0, 3)) -- 0 = ContractOpen, 3 = ContractAccepted
|
||
AND created_at >= @created_after
|
||
AND created_at < @created_before
|
||
ORDER BY id ASC
|
||
LIMIT @num_limit;
|
||
|
||
-- name: FilterInvoicesReverse :many
|
||
-- FilterInvoicesReverse is the descending counterpart of FilterInvoicesForward.
|
||
-- It returns invoices in descending id order. For cursor-based pagination the
|
||
-- caller advances add_index_let to last_returned_id - 1 on each subsequent
|
||
-- page; pass math.MaxInt64 to start from the most recent invoice. See
|
||
-- FilterInvoicesForward for the expected Go-side defaults.
|
||
SELECT
|
||
invoices.*
|
||
FROM invoices
|
||
WHERE id <= @add_index_let
|
||
AND (NOT @pending_only OR state IN (0, 3)) -- 0 = ContractOpen, 3 = ContractAccepted
|
||
AND created_at >= @created_after
|
||
AND created_at < @created_before
|
||
ORDER BY id DESC
|
||
LIMIT @num_limit;
|
||
|
||
-- name: UpdateInvoiceState :execresult
|
||
UPDATE invoices
|
||
SET state = $2,
|
||
preimage = COALESCE(preimage, $3),
|
||
settle_index = COALESCE(settle_index, $4),
|
||
settled_at = COALESCE(settled_at, $5)
|
||
WHERE id = $1;
|
||
|
||
-- name: UpdateInvoiceAmountPaid :execresult
|
||
UPDATE invoices
|
||
SET amount_paid_msat = $2
|
||
WHERE id = $1;
|
||
|
||
-- name: NextInvoiceSettleIndex :one
|
||
UPDATE invoice_sequences SET current_value = current_value + 1
|
||
WHERE name = 'settle_index'
|
||
RETURNING current_value;
|
||
|
||
-- name: DeleteInvoice :execresult
|
||
DELETE
|
||
FROM invoices
|
||
WHERE (
|
||
id = sqlc.narg('add_index') OR
|
||
sqlc.narg('add_index') IS NULL
|
||
) AND (
|
||
hash = sqlc.narg('hash') OR
|
||
sqlc.narg('hash') IS NULL
|
||
) AND (
|
||
settle_index = sqlc.narg('settle_index') OR
|
||
sqlc.narg('settle_index') IS NULL
|
||
) AND (
|
||
payment_addr = sqlc.narg('payment_addr') OR
|
||
sqlc.narg('payment_addr') IS NULL
|
||
);
|
||
|
||
-- name: DeleteCanceledInvoices :execresult
|
||
DELETE
|
||
FROM invoices
|
||
WHERE state = 2;
|
||
|
||
-- name: InsertInvoiceHTLC :one
|
||
INSERT INTO invoice_htlcs (
|
||
htlc_id, chan_id, amount_msat, total_mpp_msat, accept_height, accept_time,
|
||
expiry_height, state, resolve_time, invoice_id
|
||
) VALUES (
|
||
$1, $2, $3, $4, $5, $6, $7, $8, $9, $10
|
||
) RETURNING id;
|
||
|
||
-- name: GetInvoiceHTLCs :many
|
||
SELECT *
|
||
FROM invoice_htlcs
|
||
WHERE invoice_id = $1;
|
||
|
||
-- name: UpdateInvoiceHTLC :exec
|
||
UPDATE invoice_htlcs
|
||
SET state=$4, resolve_time=$5
|
||
WHERE htlc_id = $1 AND chan_id = $2 AND invoice_id = $3;
|
||
|
||
-- name: UpdateInvoiceHTLCs :exec
|
||
UPDATE invoice_htlcs
|
||
SET state=$2, resolve_time=$3
|
||
WHERE invoice_id = $1 AND resolve_time IS NULL;
|
||
|
||
-- name: InsertInvoiceHTLCCustomRecord :exec
|
||
INSERT INTO invoice_htlc_custom_records (
|
||
key, value, htlc_id
|
||
) VALUES (
|
||
$1, $2, $3
|
||
);
|
||
|
||
-- name: GetInvoiceHTLCCustomRecords :many
|
||
SELECT ihcr.htlc_id, key, value
|
||
FROM invoice_htlcs ih JOIN invoice_htlc_custom_records ihcr ON ih.id=ihcr.htlc_id
|
||
WHERE ih.invoice_id = $1;
|
||
|
||
-- name: InsertKVInvoiceKeyAndAddIndex :exec
|
||
INSERT INTO invoice_payment_hashes (
|
||
id, add_index
|
||
) VALUES (
|
||
$1, $2
|
||
);
|
||
|
||
-- name: SetKVInvoicePaymentHash :exec
|
||
UPDATE invoice_payment_hashes
|
||
SET hash = $2
|
||
WHERE id = $1;
|
||
|
||
-- name: GetKVInvoicePaymentHashByAddIndex :one
|
||
SELECT hash
|
||
FROM invoice_payment_hashes
|
||
WHERE add_index = $1;
|
||
|
||
-- name: ClearKVInvoiceHashIndex :exec
|
||
DELETE FROM invoice_payment_hashes;
|