/tx API is slow when it's queried with the tracker of a transaction

[joojis]

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]

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]

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