/fee-stats API is slow

[joojis]

This is used by CodeChain Explorer.

2019-11-11 08:53:36.984 UTC [2828] indexer-user@codechain-indexer-corgi-2 LOG:
  duration: 1829.223 ms
  statement:
    SELECT "hash"
      FROM "Transactions" AS "Transaction" 
      WHERE "Transaction"."type" IN ('transferAsset') AND "Transaction"."isPending" = false
      ORDER BY "Transaction"."blockNumber" DESC, "Transaction"."transactionIndex" DESC
      LIMIT 200 OFFSET 0;

[joojis]

I'm going to replace the index [isPending] with [isPending, type, blockNumber, transactionIndex]

[joojis]

EXPLAIN ANALYZE:

codechain-indexer-dev=# EXPLAIN ANALYZE SELECT "hash" FROM "Transactions" AS "Transaction" WHERE "Transaction"."type" IN ('transferAsset') AND "Transaction"."isPending" = false ORDER BY "Transaction"."blockNumber" DESC, "Transaction"."transactionIndex" DESC LIMIT 200 OFFSET 0;
                                                                                                     QUERY PLAN                                                                                                     
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1000.46..221386.14 rows=200 width=73) (actual time=19.022..1038.438 rows=200 loops=1)
   ->  Gather Merge  (cost=1000.46..858300.76 rows=778 width=73) (actual time=19.020..1038.401 rows=200 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Parallel Index Scan Backward using transactions_block_number_transaction_index on "Transactions" "Transaction"  (cost=0.43..857210.94 rows=324 width=73) (actual time=10.598..786.933 rows=68 loops=3)
               Filter: ((NOT "isPending") AND ((type)::text = 'transferAsset'::text))
               Rows Removed by Filter: 1915271
 Planning time: 0.263 ms
 Execution time: 1038.523 ms
(9 rows)

[joojis]

EXPLAIN ANALYZE after the fix:

codechain-indexer-dev=# EXPLAIN ANALYZE SELECT "hash" FROM "Transactions" AS "Transaction" WHERE "Transaction"."type" IN ('transferAsset') AND "Transaction"."isPending" = false ORDER BY "Transaction"."blockNumber" DESC, "Transaction"."transactionIndex" DESC LIMIT 200 OFFSET 0;
                                                                                                   QUERY PLAN                                                                                                    
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..363.15 rows=200 width=73) (actual time=0.078..1.315 rows=200 loops=1)
   ->  Index Scan Backward using transactions_is_pending_type_block_number_transaction_index on "Transactions" "Transaction"  (cost=0.43..1409.60 rows=777 width=73) (actual time=0.076..1.264 rows=200 loops=1)
         Index Cond: (("isPending" = false) AND ((type)::text = 'transferAsset'::text))
         Filter: (NOT "isPending")
 Planning time: 0.419 ms
 Execution time: 1.391 ms
(6 rows)