Skip to content

Commit 46f97c7

Browse files
pk910pk910
authored
Merge pull request #589 from ethpandaops/pk910/optimize-execution-db-queries
optimize execution db queries
2 parents 4a84027 + 205c960 commit 46f97c7

8 files changed

Lines changed: 189 additions & 66 deletions

‎db/el_event_index.go‎

Lines changed: 16 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -90,7 +90,7 @@ func GetElEventIndicesBySource(
9090
FROM cte
9191
UNION ALL SELECT * FROM (
9292
SELECT * FROM cte
93-
ORDER BY block_uid DESC, tx_hash DESC, event_index ASC
93+
ORDER BY block_uid DESC NULLS LAST, tx_hash DESC, event_index ASC
9494
LIMIT $%v`, len(args))
9595

9696
if offset > 0 {
@@ -142,7 +142,7 @@ func GetElEventIndicesByTopic1(
142142
FROM cte
143143
UNION ALL SELECT * FROM (
144144
SELECT * FROM cte
145-
ORDER BY block_uid DESC, tx_hash DESC, event_index ASC
145+
ORDER BY block_uid DESC NULLS LAST, tx_hash DESC, event_index ASC
146146
LIMIT $%v`, len(args))
147147

148148
if offset > 0 {
@@ -165,6 +165,20 @@ func GetElEventIndicesByTopic1(
165165
return entries[1:], count, nil
166166
}
167167

168+
// GetElEventIndexCountByTxHash returns the number of event index entries
169+
// for a given transaction hash. Uses an index-only scan for fast counting.
170+
func GetElEventIndexCountByTxHash(ctx context.Context, txHash []byte) (uint64, error) {
171+
var count uint64
172+
err := ReaderDb.GetContext(ctx, &count,
173+
"SELECT COUNT(*) FROM el_event_index WHERE tx_hash = $1",
174+
txHash,
175+
)
176+
if err != nil {
177+
return 0, err
178+
}
179+
return count, nil
180+
}
181+
168182
// GetElEventIndicesByTxHash returns all event index entries for a
169183
// given transaction hash.
170184
func GetElEventIndicesByTxHash(ctx context.Context, txHash []byte) ([]*dbtypes.ElEventIndex, error) {

‎db/el_token_transfers.go‎

Lines changed: 32 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -83,6 +83,20 @@ func GetElTokenTransfersByTxHash(ctx context.Context, txHash []byte) ([]*dbtypes
8383
return transfers, nil
8484
}
8585

86+
// GetElTokenTransferCountByBlockUidAndTxHash returns the number of token
87+
// transfers for a given block UID and transaction hash.
88+
func GetElTokenTransferCountByBlockUidAndTxHash(ctx context.Context, blockUid uint64, txHash []byte) (uint64, error) {
89+
var count uint64
90+
err := ReaderDb.GetContext(ctx, &count,
91+
"SELECT COUNT(*) FROM el_token_transfers WHERE block_uid = $1 AND tx_hash = $2",
92+
blockUid, txHash,
93+
)
94+
if err != nil {
95+
return 0, err
96+
}
97+
return count, nil
98+
}
99+
86100
func GetElTokenTransfersByBlockUid(ctx context.Context, blockUid uint64) ([]*dbtypes.ElTokenTransfer, error) {
87101
transfers := []*dbtypes.ElTokenTransfer{}
88102
err := ReaderDb.SelectContext(ctx, &transfers, "SELECT block_uid, tx_hash, tx_pos, tx_idx, token_id, token_type, token_index, from_id, to_id, amount, amount_raw FROM el_token_transfers WHERE block_uid = $1 ORDER BY tx_pos ASC, tx_idx ASC", blockUid)
@@ -106,14 +120,15 @@ func GetElTokenTransfersByTokenID(ctx context.Context, tokenID uint64, offset ui
106120
args := []any{tokenID}
107121

108122
// Use window function for count - avoids double scan
123+
// NULLS LAST matches the composite index definition to enable index scans.
109124
fmt.Fprint(&sql, `
110125
SELECT
111126
block_uid, tx_hash, tx_pos, tx_idx, token_id, token_type, token_index,
112127
from_id, to_id, amount, amount_raw,
113128
COUNT(*) OVER() AS total_count
114129
FROM el_token_transfers
115130
WHERE token_id = $1
116-
ORDER BY block_uid DESC, tx_pos DESC, tx_idx DESC
131+
ORDER BY block_uid DESC NULLS LAST, tx_pos DESC, tx_idx DESC
117132
LIMIT $2`)
118133
args = append(args, limit)
119134

@@ -160,14 +175,15 @@ func GetElTokenTransfersByAccountID(ctx context.Context, accountID uint64, isFro
160175
}
161176

162177
// Use window function for count - avoids double scan
178+
// NULLS LAST matches the composite index definition to enable index scans.
163179
fmt.Fprintf(&sql, `
164180
SELECT
165181
block_uid, tx_hash, tx_pos, tx_idx, token_id, token_type, token_index,
166182
from_id, to_id, amount, amount_raw,
167183
COUNT(*) OVER() AS total_count
168184
FROM el_token_transfers
169185
WHERE %s = $1
170-
ORDER BY block_uid DESC, tx_pos DESC, tx_idx DESC
186+
ORDER BY block_uid DESC NULLS LAST, tx_pos DESC, tx_idx DESC
171187
LIMIT $2`, column)
172188
args = append(args, limit)
173189

@@ -260,7 +276,7 @@ func GetElTokenTransfersFiltered(ctx context.Context, offset uint64, limit uint3
260276
FROM cte
261277
UNION ALL SELECT * FROM (
262278
SELECT * FROM cte
263-
ORDER BY block_uid DESC, tx_pos DESC, tx_idx DESC
279+
ORDER BY block_uid DESC NULLS LAST, tx_pos DESC, tx_idx DESC
264280
LIMIT $%v`, len(args))
265281

266282
if offset > 0 {
@@ -295,7 +311,10 @@ const MaxAccountTokenTransferCount = 100000
295311
func GetElTokenTransfersByAccountIDCombined(ctx context.Context, accountID uint64, tokenTypes []uint8, offset uint64, limit uint32) ([]*dbtypes.ElTokenTransfer, uint64, bool, error) {
296312
// Use UNION ALL instead of OR for better index usage.
297313
// The second query excludes rows where from_id = accountID to avoid duplicates.
314+
// Push LIMIT into each UNION branch so PG can use composite indexes efficiently.
315+
// NULLS LAST is required to match the index definition and enable index scans.
298316
var sql strings.Builder
317+
innerLimit := offset + uint64(limit)
299318
args := []any{accountID, accountID, accountID}
300319

301320
// Build token type filter clause
@@ -312,20 +331,27 @@ func GetElTokenTransfersByAccountIDCombined(ctx context.Context, accountID uint6
312331
tokenTypeFilter = fmt.Sprintf(" AND token_type IN (%s)", tokenTypeArgs.String())
313332
}
314333

334+
args = append(args, innerLimit)
335+
innerLimitIdx := len(args)
336+
315337
fmt.Fprintf(&sql, `
316338
SELECT block_uid, tx_hash, tx_pos, tx_idx, token_id, token_type, token_index,
317339
from_id, to_id, amount, amount_raw
318340
FROM (
319-
SELECT block_uid, tx_hash, tx_pos, tx_idx, token_id, token_type, token_index,
341+
(SELECT block_uid, tx_hash, tx_pos, tx_idx, token_id, token_type, token_index,
320342
from_id, to_id, amount, amount_raw
321343
FROM el_token_transfers WHERE from_id = $1%s
344+
ORDER BY block_uid DESC NULLS LAST
345+
LIMIT $%d)
322346
UNION ALL
323-
SELECT block_uid, tx_hash, tx_pos, tx_idx, token_id, token_type, token_index,
347+
(SELECT block_uid, tx_hash, tx_pos, tx_idx, token_id, token_type, token_index,
324348
from_id, to_id, amount, amount_raw
325349
FROM el_token_transfers WHERE to_id = $2 AND from_id != $3%s
350+
ORDER BY block_uid DESC NULLS LAST
351+
LIMIT $%d)
326352
) combined
327353
ORDER BY block_uid DESC, tx_pos DESC, tx_idx DESC
328-
LIMIT $%d`, tokenTypeFilter, tokenTypeFilter, len(args)+1)
354+
LIMIT $%d`, tokenTypeFilter, innerLimitIdx, tokenTypeFilter, innerLimitIdx, len(args)+1)
329355
args = append(args, limit)
330356

331357
if offset > 0 {

‎db/el_transactions.go‎

Lines changed: 15 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -137,6 +137,7 @@ func GetElTransactionsByAccountID(ctx context.Context, accountID uint64, isFrom
137137
}
138138

139139
// Use window function for count (PostgreSQL 9.5+) - avoids double scan
140+
// NULLS LAST matches the composite index definition to enable index scans.
140141
fmt.Fprintf(&sql, `
141142
SELECT
142143
block_uid, tx_hash, from_id, to_id, nonce, reverted, amount, amount_raw,
@@ -145,7 +146,7 @@ func GetElTransactionsByAccountID(ctx context.Context, accountID uint64, isFrom
145146
COUNT(*) OVER() AS total_count
146147
FROM el_transactions
147148
WHERE %s = $1
148-
ORDER BY block_uid DESC, tx_hash DESC
149+
ORDER BY block_uid DESC NULLS LAST, tx_hash DESC
149150
LIMIT $2`, column)
150151
args = append(args, limit)
151152

@@ -245,7 +246,7 @@ func GetElTransactionsFiltered(ctx context.Context, offset uint64, limit uint32,
245246
FROM cte
246247
UNION ALL SELECT * FROM (
247248
SELECT * FROM cte
248-
ORDER BY block_uid DESC, tx_hash DESC
249+
ORDER BY block_uid DESC NULLS LAST, tx_hash DESC
249250
LIMIT $%v`, len(args))
250251

251252
if offset > 0 {
@@ -280,26 +281,34 @@ func GetElTransactionsByAccountIDCombined(ctx context.Context, accountID uint64,
280281
// Use UNION ALL instead of OR for better index usage.
281282
// The second query excludes rows where from_id = accountID to avoid duplicates
282283
// (handles self-transfers where from_id = to_id = accountID).
284+
// Push LIMIT into each UNION branch so PG can use composite indexes
285+
// (from_id, block_uid DESC) and (to_id, block_uid DESC) efficiently.
286+
// NULLS LAST is required to match the index definition and enable index scans.
283287
var sql strings.Builder
284-
args := []any{accountID, accountID, accountID}
288+
innerLimit := offset + uint64(limit)
289+
args := []any{accountID, accountID, accountID, innerLimit}
285290

286291
fmt.Fprint(&sql, `
287292
SELECT block_uid, tx_hash, from_id, to_id, nonce, reverted, amount, amount_raw,
288293
method_id, gas_limit, gas_used, gas_price, tip_price, blob_count, block_number,
289294
tx_type, tx_index, eff_gas_price
290295
FROM (
291-
SELECT block_uid, tx_hash, from_id, to_id, nonce, reverted, amount, amount_raw,
296+
(SELECT block_uid, tx_hash, from_id, to_id, nonce, reverted, amount, amount_raw,
292297
method_id, gas_limit, gas_used, gas_price, tip_price, blob_count, block_number,
293298
tx_type, tx_index, eff_gas_price
294299
FROM el_transactions WHERE from_id = $1
300+
ORDER BY block_uid DESC NULLS LAST
301+
LIMIT $4)
295302
UNION ALL
296-
SELECT block_uid, tx_hash, from_id, to_id, nonce, reverted, amount, amount_raw,
303+
(SELECT block_uid, tx_hash, from_id, to_id, nonce, reverted, amount, amount_raw,
297304
method_id, gas_limit, gas_used, gas_price, tip_price, blob_count, block_number,
298305
tx_type, tx_index, eff_gas_price
299306
FROM el_transactions WHERE to_id = $2 AND from_id != $3
307+
ORDER BY block_uid DESC NULLS LAST
308+
LIMIT $4)
300309
) combined
301310
ORDER BY block_uid DESC, tx_index DESC
302-
LIMIT $4`)
311+
LIMIT $5`)
303312
args = append(args, limit)
304313

305314
if offset > 0 {

‎db/el_transactions_internal.go‎

Lines changed: 54 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -67,59 +67,87 @@ func InsertElTransactionsInternal(ctx context.Context, dbTx *sqlx.Tx, entries []
6767
return err
6868
}
6969

70+
// MaxAccountInternalTxCount is the maximum count returned for address internal transaction queries.
71+
// If the actual count exceeds this, the query returns this limit and sets the "more" flag.
72+
const MaxAccountInternalTxCount = 100000
73+
7074
// GetElTransactionsInternalByAccount returns internal transactions
7175
// involving the given account (as sender or receiver), ordered by block_uid DESC.
76+
// Uses UNION ALL instead of OR to enable index scans on the composite indexes
77+
// (from_id, block_uid DESC) and (to_id, block_uid DESC).
78+
// NULLS LAST is required to match the index definition and enable index scans.
7279
func GetElTransactionsInternalByAccount(
7380
ctx context.Context,
7481
accountID uint64,
7582
offset uint64,
7683
limit uint32,
7784
) ([]*dbtypes.ElTransactionInternal, uint64, error) {
85+
// Data query: UNION ALL with pushed LIMIT into each branch
7886
var sql strings.Builder
79-
args := []any{accountID}
87+
innerLimit := offset + uint64(limit)
88+
args := []any{accountID, accountID, accountID, innerLimit}
8089

8190
fmt.Fprint(&sql, `
82-
WITH cte AS (
8391
SELECT block_uid, tx_hash, tx_callidx, call_type, from_id, to_id, value, value_raw
84-
FROM el_transactions_internal
85-
WHERE from_id = $1 OR to_id = $1
86-
)`)
87-
92+
FROM (
93+
(SELECT block_uid, tx_hash, tx_callidx, call_type, from_id, to_id, value, value_raw
94+
FROM el_transactions_internal WHERE from_id = $1
95+
ORDER BY block_uid DESC NULLS LAST
96+
LIMIT $4)
97+
UNION ALL
98+
(SELECT block_uid, tx_hash, tx_callidx, call_type, from_id, to_id, value, value_raw
99+
FROM el_transactions_internal WHERE to_id = $2 AND from_id != $3
100+
ORDER BY block_uid DESC NULLS LAST
101+
LIMIT $4)
102+
) combined
103+
ORDER BY block_uid DESC, tx_hash ASC, tx_callidx ASC
104+
LIMIT $5`)
88105
args = append(args, limit)
89-
fmt.Fprintf(&sql, `
90-
SELECT
91-
count(*) AS block_uid,
92-
null AS tx_hash,
93-
0 AS tx_callidx,
94-
0 AS call_type,
95-
0 AS from_id,
96-
0 AS to_id,
97-
0 AS value,
98-
null AS value_raw
99-
FROM cte
100-
UNION ALL SELECT * FROM (
101-
SELECT * FROM cte
102-
ORDER BY block_uid DESC, tx_hash ASC, tx_callidx ASC
103-
LIMIT $%v`, len(args))
104106

105107
if offset > 0 {
106108
args = append(args, offset)
107109
fmt.Fprintf(&sql, " OFFSET $%v", len(args))
108110
}
109-
fmt.Fprint(&sql, ") AS t1")
110111

111112
entries := []*dbtypes.ElTransactionInternal{}
112113
err := ReaderDb.SelectContext(ctx, &entries, sql.String(), args...)
113114
if err != nil {
114115
return nil, 0, err
115116
}
116117

117-
if len(entries) == 0 {
118-
return []*dbtypes.ElTransactionInternal{}, 0, nil
118+
// Count query: separate index-only scans per direction, capped at MaxAccountInternalTxCount.
119+
// Counts from_id and to_id separately (without from_id != exclusion) for index-only scan
120+
// performance. May slightly overcount self-referencing internal calls, which is acceptable
121+
// since the count is already an approximation (capped).
122+
countSQL := `
123+
SELECT
124+
(SELECT COUNT(*) FROM (SELECT 1 FROM el_transactions_internal WHERE from_id = $1 LIMIT $2) a) +
125+
(SELECT COUNT(*) FROM (SELECT 1 FROM el_transactions_internal WHERE to_id = $1 LIMIT $2) b)`
126+
var totalCount uint64
127+
err = ReaderDb.GetContext(ctx, &totalCount, countSQL, accountID, MaxAccountInternalTxCount)
128+
if err != nil {
129+
return nil, 0, err
130+
}
131+
132+
if totalCount > MaxAccountInternalTxCount {
133+
totalCount = MaxAccountInternalTxCount
119134
}
120135

121-
count := entries[0].BlockUid
122-
return entries[1:], count, nil
136+
return entries, totalCount, nil
137+
}
138+
139+
// GetElTransactionsInternalCountByTxHash returns the number of internal
140+
// transactions for a given transaction hash. Uses an index-only scan.
141+
func GetElTransactionsInternalCountByTxHash(ctx context.Context, txHash []byte) (uint64, error) {
142+
var count uint64
143+
err := ReaderDb.GetContext(ctx, &count,
144+
"SELECT COUNT(*) FROM el_transactions_internal WHERE tx_hash = $1",
145+
txHash,
146+
)
147+
if err != nil {
148+
return 0, err
149+
}
150+
return count, nil
123151
}
124152

125153
// GetElTransactionsInternalByTxHash returns all internal transactions

‎db/el_withdrawals.go‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -113,7 +113,7 @@ func GetElWithdrawalsByAccountID(ctx context.Context, accountID uint64, offset u
113113
FROM cte
114114
UNION ALL SELECT * FROM (
115115
SELECT * FROM cte
116-
ORDER BY block_uid DESC, block_index ASC
116+
ORDER BY block_uid DESC NULLS LAST, block_index ASC
117117
LIMIT $%v`, len(args)+1)
118118
args = append(args, limit)
119119

@@ -190,7 +190,7 @@ func GetElWithdrawalsFiltered(ctx context.Context, offset uint64, limit uint32,
190190
FROM cte
191191
UNION ALL SELECT * FROM (
192192
SELECT * FROM cte
193-
ORDER BY block_uid DESC, block_index ASC
193+
ORDER BY block_uid DESC NULLS LAST, block_index ASC
194194
LIMIT $%v`, len(args))
195195

196196
if offset > 0 {

‎db/schema/pgsql/20260223000000_tx-detail-indexes.sql‎

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,20 @@
1+
-- +goose Up
2+
-- +goose StatementBegin
3+
4+
-- Add tx_hash index on el_event_index for transaction detail page queries.
5+
-- Without this, GetElEventIndicesByTxHash does a full sequential scan.
6+
CREATE INDEX IF NOT EXISTS "el_event_index_tx_hash_idx"
7+
ON public."el_event_index"
8+
("tx_hash" ASC NULLS FIRST);
9+
10+
-- Add tx_hash index on el_transactions_internal for transaction detail page queries.
11+
-- Without this, GetElTransactionsInternalByTxHash does a full sequential scan.
12+
CREATE INDEX IF NOT EXISTS "el_internal_tx_hash_idx"
13+
ON public."el_transactions_internal"
14+
("tx_hash" ASC NULLS FIRST);
15+
16+
-- +goose StatementEnd
17+
-- +goose Down
18+
-- +goose StatementBegin
19+
SELECT 'NOT SUPPORTED';
20+
-- +goose StatementEnd

‎db/schema/sqlite/20260223000000_tx-detail-indexes.sql‎

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
1+
-- +goose Up
2+
-- +goose StatementBegin
3+
4+
-- Add tx_hash index on el_event_index for transaction detail page queries.
5+
CREATE INDEX IF NOT EXISTS "el_event_index_tx_hash_idx"
6+
ON "el_event_index"
7+
("tx_hash" ASC);
8+
9+
-- Add tx_hash index on el_transactions_internal for transaction detail page queries.
10+
CREATE INDEX IF NOT EXISTS "el_internal_tx_hash_idx"
11+
ON "el_transactions_internal"
12+
("tx_hash" ASC);
13+
14+
-- +goose StatementEnd
15+
-- +goose Down
16+
-- +goose StatementBegin
17+
SELECT 'NOT SUPPORTED';
18+
-- +goose StatementEnd

0 commit comments

Comments
 (0)