/tx API is slow when it's queried with the tracker of a transaction
Closed this issue · 2 comments
joojis commented
2019-11-11 08:59:09.620 UTC [2827] indexer-user@codechain-indexer-corgi-2 LOG: duration: 1455.755 ms statement: SELECT "hash", "blockNumber", "blockHash", "tracker", "transactionIndex", "type", "seq", "fee", "networkId", "sig", "signer", "errorHint", "timestamp", "isPending", "pendingTimestamp", "createdAt", "updatedAt" FROM "Transactions" AS "Transaction" W
HERE "Transaction"."tracker" = '8cb9eb73de1c2767d5914d1771cdcb8ecd526684494d94b6e7d597713e232520' AND "Transaction"."isPending" = false LIMIT 1;
joojis commented
EXPLAIN ANALYZE:
codechain-indexer-dev=# EXPLAIN ANALYZE SELECT "hash" FROM "Transactions" AS "Transaction" WHERE "Transaction"."tracker" = '988d59abc48aaa0677ee14b1a2404127f63fc2fd9529801e551e6b74029e9c57' AND "Transaction"."isPending" = false ORDER BY "Transaction"."blockNumber" DESC, "Transaction"."transactionIndex" DESC LIMIT 15 OFFSET 0;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=321610.04..321610.04 rows=1 width=73) (actual time=508.794..508.795 rows=1 loops=1)
-> Sort (cost=321610.04..321610.04 rows=1 width=73) (actual time=508.791..508.792 rows=1 loops=1)
Sort Key: "blockNumber" DESC, "transactionIndex" DESC
Sort Method: quicksort Memory: 25kB
-> Gather (cost=1000.00..321610.03 rows=1 width=73) (actual time=508.746..512.684 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on "Transactions" "Transaction" (cost=0.00..320609.93 rows=1 width=73) (actual time=488.852..504.731 rows=0 loops=3)
Filter: ((NOT "isPending") AND ((tracker)::text = '988d59abc48aaa0677ee14b1a2404127f63fc2fd9529801e551e6b74029e9c57'::text))
Rows Removed by Filter: 1934451
Planning time: 0.616 ms
Execution time: 512.772 ms
joojis commented
After the fix:
codechain-indexer-dev=# EXPLAIN ANALYZE SELECT "hash" FROM "Transactions" AS "Transaction" WHERE "Transaction"."tracker" = '988d59abc48aaa0677ee14b1a2404127f63fc2fd9529801e551e6b74029e9c57' AND "Transaction"."isPending" = false ORDER BY "Transaction"."blockNumber" DESC, "Transaction"."transactionIndex" DESC LIMIT 15 OFFSET 0;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=8.46..8.46 rows=1 width=73) (actual time=0.050..0.052 rows=1 loops=1)
-> Sort (cost=8.46..8.46 rows=1 width=73) (actual time=0.049..0.050 rows=1 loops=1)
Sort Key: "blockNumber" DESC, "transactionIndex" DESC
Sort Method: quicksort Memory: 25kB
-> Index Scan using transactions_tracker on "Transactions" "Transaction" (cost=0.43..8.45 rows=1 width=73) (actual time=0.033..0.035 rows=1 loops=1)
Index Cond: ((tracker)::text = '988d59abc48aaa0677ee14b1a2404127f63fc2fd9529801e551e6b74029e9c57'::text)
Filter: (NOT "isPending")
Planning time: 0.411 ms
Execution time: 0.100 ms