Fee stats API takes too much time

[cubistplay]

For TransferAsset

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=366113.38..366136.71 rows=200 width=22024) (actual time=387.693..394.615 rows=200 loops=1)
   ->  Gather Merge  (cost=366113.38..369923.74 rows=32658 width=22024) (actual time=387.692..394.592 rows=200 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=365113.35..365154.17 rows=16329 width=22024) (actual time=379.665..379.702 rows=91 loops=3)
               Sort Key: "Transaction"."blockNumber" DESC, "Transaction"."transactionIndex" DESC
               Sort Method: external merge  Disk: 24896kB
               ->  Hash Left Join  (cost=1221.72..56334.72 rows=16329 width=22024) (actual time=9.071..335.636 rows=13344 loops=3)
                     Hash Cond: (("Transaction".hash)::text = (custom."transactionHash")::text)
                     ->  Hash Left Join  (cost=1210.14..56280.28 rows=16329 width=20972) (actual time=9.053..326.089 rows=13344 loops=3)
                           Hash Cond: (("Transaction".hash)::text = (remove."transactionHash")::text)
                           ->  Hash Left Join  (cost=1199.02..56226.29 rows=16329 width=19408) (actual time=9.040..317.207 rows=13344 loops=3)
                                 Hash Cond: (("Transaction".hash)::text = (store."transactionHash")::text)
                                 ->  Hash Left Join  (cost=1188.34..56172.75 rows=16329 width=17328) (actual time=9.028..308.476 rows=13344 loops=3)
                                       Hash Cond: (("Transaction".hash)::text = ("setShardUsers"."transactionHash")::text)
                                       ->  Hash Left Join  (cost=1175.42..56116.96 rows=16329 width=16760) (actual time=9.016..300.048 rows=13344 loops=3)
                                             Hash Cond: (("Transaction".hash)::text = ("setShardOwners"."transactionHash")::text)
                                             ->  Hash Left Join  (cost=1162.49..56061.18 rows=16329 width=16192) (actual time=9.004..291.551 rows=13344 loops=3)
                                                   Hash Cond: (("Transaction".hash)::text = ("createShard"."transactionHash")::text)
                                                   ->  Hash Left Join  (cost=1149.57..56005.39 rows=16329 width=15624) (actual time=8.985..283.183 rows=13344 loops=3)
                                                         Hash Cond: (("Transaction".hash)::text = ("setRegularKey"."transactionHash")::text)
                                                         ->  Nested Loop Left Join  (cost=1102.44..55915.40 rows=16329 width=15411) (actual time=8.747..274.090 rows=13344 loops=3)
                                                               ->  Nested Loop Left Join  (cost=1102.03..44098.88 rows=16329 width=15281) (actual time=8.725..209.855 rows=13344 loops=3)
                                                                     ->  Hash Left Join  (cost=1101.62..33269.84 rows=16329 width=14231) (actual time=8.681..154.180 rows=13344 loops=3)
                                                                           Hash Cond: (("Transaction".hash)::text = ("wrapCCC"."transactionHash")::text)
                                                                           ->  Hash Left Join  (cost=247.62..32372.98 rows=16329 width=14005) (actual time=2.283..137.943 rows=13344 loops=3)
                                                                                 Hash Cond: (("Transaction".hash)::text = ("increaseAssetSupply"."transactionHash")::text)
                                                                                 ->  Hash Left Join  (cost=236.95..32319.45 rows=16329 width=11317) (actual time=2.272..130.587 rows=13344 loops=3)
                                                                                       Hash Cond: (("Transaction".hash)::text = ("changeAssetScheme"."transactionHash")::text)
                                                                                       ->  Hash Left Join  (cost=226.50..32266.13 rows=16329 width=8617) (actual time=2.259..123.599 rows=13344 loops=3)
                                                                                             Hash Cond: (("Transaction".hash)::text = ("decomposeAsset"."transactionHash")::text)
                                                                                             ->  Hash Left Join  (cost=215.15..32211.92 rows=16329 width=7473) (actual time=2.248..117.120 rows=13344 loops=3)
                                                                                                   Hash Cond: (("Transaction".hash)::text = ("composeAsset"."transactionHash")::text)
                                                                                                   ->  Nested Loop Left Join  (cost=204.92..32158.83 rows=16329 width=3145) (actual time=2.229..110.616 rows=13344 loops=3)
                                                                                                         ->  Hash Left Join  (cost=204.51..9309.63 rows=16329 width=1472) (actual time=2.186..27.899 rows=13344 loops=3)
                                                                                                               Hash Cond: (("Transaction".hash)::text = ("mintAsset"."transactionHash")::text)
                                                                                                               ->  Parallel Seq Scan on "Transactions" "Transaction"  (cost=0.00..9062.26 rows=16329 width=458) (actual time=0.021..19.110 rows=13344 loops=3)
                                                                                                                     Filter: ((NOT "isPending") AND ((type)::text = 'transferAsset'::text))
                                                                                                                     Rows Removed by Filter: 36559
                                                                                                               ->  Hash  (cost=177.56..177.56 rows=2156 width=1014) (actual time=2.112..2.112 rows=2155 loops=3)
                                                                                                                     Buckets: 4096  Batches: 1  Memory Usage: 1082kB
                                                                                                                     ->  Seq Scan on "MintAssets" "mintAsset"  (cost=0.00..177.56 rows=2156 width=1014) (actual time=0.009..0.936 rows=2155 loops=3)
                                                                                                         ->  Index Scan using "TransferAssets_pkey" on "TransferAssets" "transferAsset"  (cost=0.41..1.40 rows=1 width=1673) (actual time=0.005..0.005 rows=1 loops=40031)
                                                                                                               Index Cond: (("Transaction".hash)::text = ("transactionHash")::text)
                                                                                                   ->  Hash  (cost=10.10..10.10 rows=10 width=4328) (actual time=0.004..0.004 rows=0 loops=3)
                                                                                                         Buckets: 1024  Batches: 1  Memory Usage: 8kB
                                                                                                         ->  Seq Scan on "ComposeAssets" "composeAsset"  (cost=0.00..10.10 rows=10 width=4328) (actual time=0.003..0.003 rows=0 loops=3)
                                                                                             ->  Hash  (cost=10.60..10.60 rows=60 width=1144) (actual time=0.003..0.003 rows=0 loops=3)
                                                                                                   Buckets: 1024  Batches: 1  Memory Usage: 8kB
                                                                                                   ->  Seq Scan on "DecomposeAssets" "decomposeAsset"  (cost=0.00..10.60 rows=60 width=1144) (actual time=0.003..0.003 rows=0 loops=3)
                                                                                       ->  Hash  (cost=10.20..10.20 rows=20 width=2700) (actual time=0.003..0.003 rows=0 loops=3)
                                                                                             Buckets: 1024  Batches: 1  Memory Usage: 8kB
                                                                                             ->  Seq Scan on "ChangeAssetSchemes" "changeAssetScheme"  (cost=0.00..10.20 rows=20 width=2700) (actual time=0.003..0.003 rows=0 loops=3)
                                                                                 ->  Hash  (cost=10.30..10.30 rows=30 width=2688) (actual time=0.003..0.003 rows=0 loops=3)
                                                                                       Buckets: 1024  Batches: 1  Memory Usage: 8kB
                                                                                       ->  Seq Scan on "IncreaseAssetSupplies" "increaseAssetSupply"  (cost=0.00..10.30 rows=30 width=2688) (actual time=0.003..0.003 rows=0 loops=3)
                                                                           ->  Hash  (cost=668.44..668.44 rows=14844 width=226) (actual time=6.340..6.340 rows=14870 loops=3)
                                                                                 Buckets: 16384  Batches: 1  Memory Usage: 3962kB
                                                                                 ->  Seq Scan on "WrapCCCs" "wrapCCC"  (cost=0.00..668.44 rows=14844 width=226) (actual time=0.008..2.425 rows=14870 loops=3)
                                                                     ->  Index Scan using "UnwrapCCCs_pkey" on "UnwrapCCCs" "unwrapCCC"  (cost=0.41..0.66 rows=1 width=1050) (actual time=0.003..0.003 rows=0 loops=40031)
                                                                           Index Cond: (("Transaction".hash)::text = ("transactionHash")::text)
                                                               ->  Index Scan using "Pays_pkey" on "Pays" pay  (cost=0.42..0.72 rows=1 width=130) (actual time=0.004..0.004 rows=0 loops=40031)
                                                                     Index Cond: (("Transaction".hash)::text = ("transactionHash")::text)
                                                         ->  Hash  (cost=41.50..41.50 rows=450 width=213) (actual time=0.224..0.224 rows=468 loops=3)
                                                               Buckets: 1024  Batches: 1  Memory Usage: 122kB
                                                               ->  Seq Scan on "SetRegularKeys" "setRegularKey"  (cost=0.00..41.50 rows=450 width=213) (actual time=0.008..0.115 rows=468 loops=3)
                                                   ->  Hash  (cost=11.30..11.30 rows=130 width=568) (actual time=0.004..0.004 rows=0 loops=3)
                                                         Buckets: 1024  Batches: 1  Memory Usage: 8kB
                                                         ->  Seq Scan on "CreateShards" "createShard"  (cost=0.00..11.30 rows=130 width=568) (actual time=0.004..0.004 rows=0 loops=3)
                                             ->  Hash  (cost=11.30..11.30 rows=130 width=568) (actual time=0.004..0.004 rows=0 loops=3)
                                                   Buckets: 1024  Batches: 1  Memory Usage: 8kB
                                                   ->  Seq Scan on "SetShardOwners" "setShardOwners"  (cost=0.00..11.30 rows=130 width=568) (actual time=0.004..0.004 rows=0 loops=3)
                                       ->  Hash  (cost=11.30..11.30 rows=130 width=568) (actual time=0.003..0.003 rows=0 loops=3)
                                             Buckets: 1024  Batches: 1  Memory Usage: 8kB
                                             ->  Seq Scan on "SetShardUsers" "setShardUsers"  (cost=0.00..11.30 rows=130 width=568) (actual time=0.002..0.003 rows=0 loops=3)
                                 ->  Hash  (cost=10.30..10.30 rows=30 width=2080) (actual time=0.003..0.003 rows=0 loops=3)
                                       Buckets: 1024  Batches: 1  Memory Usage: 8kB
                                       ->  Seq Scan on "Stores" store  (cost=0.00..10.30 rows=30 width=2080) (actual time=0.003..0.003 rows=0 loops=3)
                           ->  Hash  (cost=10.50..10.50 rows=50 width=1564) (actual time=0.003..0.003 rows=0 loops=3)
                                 Buckets: 1024  Batches: 1  Memory Usage: 8kB
                                 ->  Seq Scan on "Removes" remove  (cost=0.00..10.50 rows=50 width=1564) (actual time=0.003..0.003 rows=0 loops=3)
                     ->  Hash  (cost=10.70..10.70 rows=70 width=1052) (actual time=0.009..0.009 rows=7 loops=3)
                           Buckets: 1024  Batches: 1  Memory Usage: 10kB
                           ->  Seq Scan on "Customs" custom  (cost=0.00..10.70 rows=70 width=1052) (actual time=0.005..0.006 rows=7 loops=3)
 Planning time: 29.504 ms
 Execution time: 397.914 ms
(86 rows)

For Pay

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=677855.16..677878.49 rows=200 width=22024) (actual time=639.206..645.607 rows=200 loops=1)
   ->  Gather Merge  (cost=677855.16..685297.14 rows=63784 width=22024) (actual time=639.205..645.582 rows=200 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=676855.13..676934.86 rows=31892 width=22024) (actual time=562.693..562.728 rows=151 loops=3)
               Sort Key: "Transaction"."blockNumber" DESC, "Transaction"."transactionIndex" DESC
               Sort Method: external merge  Disk: 10544kB
               ->  Merge Left Join  (cost=20282.08..73624.47 rows=31892 width=22024) (actual time=144.169..522.829 rows=25950 loops=3)
                     Merge Cond: (("Transaction".hash)::text = (custom."transactionHash")::text)
                     ->  Merge Left Join  (cost=20269.23..73531.39 rows=31892 width=20972) (actual time=144.158..506.040 rows=25950 loops=3)
                           Merge Cond: (("Transaction".hash)::text = (remove."transactionHash")::text)
                           ->  Merge Left Join  (cost=20257.32..73439.39 rows=31892 width=19408) (actual time=144.151..493.550 rows=25950 loops=3)
                                 Merge Cond: (("Transaction".hash)::text = (store."transactionHash")::text)
                                 ->  Merge Left Join  (cost=20246.28..73348.42 rows=31892 width=17328) (actual time=144.143..481.081 rows=25950 loops=3)
                                       Merge Cond: (("Transaction".hash)::text = ("setShardUsers"."transactionHash")::text)
                                       ->  Merge Left Join  (cost=20230.42..73251.89 rows=31892 width=16760) (actual time=144.136..469.202 rows=25950 loops=3)
                                             Merge Cond: (("Transaction".hash)::text = ("setShardOwners"."transactionHash")::text)
                                             ->  Merge Left Join  (cost=20214.56..73155.37 rows=31892 width=16192) (actual time=144.130..457.401 rows=25950 loops=3)
                                                   Merge Cond: (("Transaction".hash)::text = ("createShard"."transactionHash")::text)
                                                   ->  Merge Left Join  (cost=20198.69..73058.84 rows=31892 width=15624) (actual time=144.120..445.846 rows=25950 loops=3)
                                                         Merge Cond: (("Transaction".hash)::text = ("setRegularKey"."transactionHash")::text)
                                                         ->  Merge Left Join  (cost=20137.36..72914.57 rows=31892 width=15411) (actual time=143.613..429.940 rows=25950 loops=3)
                                                               Merge Cond: (("Transaction".hash)::text = (pay."transactionHash")::text)
                                                               ->  Merge Left Join  (cost=20136.94..59993.88 rows=31892 width=15281) (actual time=143.598..343.518 rows=25950 loops=3)
                                                                     Merge Cond: (("Transaction".hash)::text = ("unwrapCCC"."transactionHash")::text)
                                                                     ->  Merge Left Join  (cost=20136.53..50396.48 rows=31892 width=14231) (actual time=143.566..313.799 rows=25950 loops=3)
                                                                           Merge Cond: (("Transaction".hash)::text = ("wrapCCC"."transactionHash")::text)
                                                                           ->  Merge Left Join  (cost=18439.58..48513.90 rows=31892 width=14005) (actual time=96.866..245.964 rows=25950 loops=3)
                                                                                 Merge Cond: (("Transaction".hash)::text = ("increaseAssetSupply"."transactionHash")::text)
                                                                                 ->  Merge Left Join  (cost=18428.55..48422.92 rows=31892 width=11317) (actual time=96.856..236.106 rows=25950 loops=3)
                                                                                       Merge Cond: (("Transaction".hash)::text = ("changeAssetScheme"."transactionHash")::text)
                                                                                       ->  Merge Left Join  (cost=18417.91..48332.42 rows=31892 width=8617) (actual time=96.849..227.341 rows=25950 loops=3)
                                                                                             Merge Cond: (("Transaction".hash)::text = ("decomposeAsset"."transactionHash")::text)
                                                                                             ->  Merge Left Join  (cost=18405.54..48239.89 rows=31892 width=7473) (actual time=96.839..219.271 rows=25950 loops=3)
                                                                                                   Merge Cond: (("Transaction".hash)::text = ("composeAsset"."transactionHash")::text)
                                                                                                   ->  Nested Loop Left Join  (cost=18395.28..48149.82 rows=31892 width=3145) (actual time=96.820..211.483 rows=25950 loops=3)
                                                                                                         ->  Merge Left Join  (cost=18394.86..18569.70 rows=31892 width=1472) (actual time=96.787..123.483 rows=25950 loops=3)
                                                                                                               Merge Cond: (("Transaction".hash)::text = ("mintAsset"."transactionHash")::text)
                                                                                                               ->  Sort  (cost=18097.92..18177.65 rows=31892 width=458) (actual time=92.585..105.922 rows=25950 loops=3)
                                                                                                                     Sort Key: "Transaction".hash
                                                                                                                     Sort Method: external merge  Disk: 7560kB
                                                                                                                     ->  Parallel Seq Scan on "Transactions" "Transaction"  (cost=0.00..9062.26 rows=31892 width=458) (actual time=0.019..17.486 rows=25950 loops=3)
                                                                                                                           Filter: ((NOT "isPending") AND ((type)::text = 'pay'::text))
                                                                                                                           Rows Removed by Filter: 23953
                                                                                                               ->  Sort  (cost=296.94..302.33 rows=2156 width=1014) (actual time=4.195..4.686 rows=2155 loops=3)
                                                                                                                     Sort Key: "mintAsset"."transactionHash"
                                                                                                                     Sort Method: quicksort  Memory: 1262kB
                                                                                                                     ->  Seq Scan on "MintAssets" "mintAsset"  (cost=0.00..177.56 rows=2156 width=1014) (actual time=0.026..0.915 rows=2155 loops=3)
                                                                                                         ->  Index Scan using "TransferAssets_pkey" on "TransferAssets" "transferAsset"  (cost=0.41..0.93 rows=1 width=1673) (actual time=0.003..0.003 rows=0 loops=77849)
                                                                                                               Index Cond: (("Transaction".hash)::text = ("transactionHash")::text)
                                                                                                   ->  Sort  (cost=10.27..10.29 rows=10 width=4328) (actual time=0.017..0.017 rows=0 loops=3)
                                                                                                         Sort Key: "composeAsset"."transactionHash"
                                                                                                         Sort Method: quicksort  Memory: 25kB
                                                                                                         ->  Seq Scan on "ComposeAssets" "composeAsset"  (cost=0.00..10.10 rows=10 width=4328) (actual time=0.007..0.007 rows=0 loops=3)
                                                                                             ->  Sort  (cost=12.37..12.52 rows=60 width=1144) (actual time=0.007..0.007 rows=0 loops=3)
                                                                                                   Sort Key: "decomposeAsset"."transactionHash"
                                                                                                   Sort Method: quicksort  Memory: 25kB
                                                                                                   ->  Seq Scan on "DecomposeAssets" "decomposeAsset"  (cost=0.00..10.60 rows=60 width=1144) (actual time=0.004..0.004 rows=0 loops=3)
                                                                                       ->  Sort  (cost=10.63..10.68 rows=20 width=2700) (actual time=0.006..0.006 rows=0 loops=3)
                                                                                             Sort Key: "changeAssetScheme"."transactionHash"
                                                                                             Sort Method: quicksort  Memory: 25kB
                                                                                             ->  Seq Scan on "ChangeAssetSchemes" "changeAssetScheme"  (cost=0.00..10.20 rows=20 width=2700) (actual time=0.003..0.003 rows=0 loops=3)
                                                                                 ->  Sort  (cost=11.04..11.11 rows=30 width=2688) (actual time=0.008..0.008 rows=0 loops=3)
                                                                                       Sort Key: "increaseAssetSupply"."transactionHash"
                                                                                       Sort Method: quicksort  Memory: 25kB
                                                                                       ->  Seq Scan on "IncreaseAssetSupplies" "increaseAssetSupply"  (cost=0.00..10.30 rows=30 width=2688) (actual time=0.003..0.003 rows=0 loops=3)
                                                                           ->  Sort  (cost=1696.95..1734.06 rows=14844 width=226) (actual time=46.695..49.354 rows=14870 loops=3)
                                                                                 Sort Key: "wrapCCC"."transactionHash"
                                                                                 Sort Method: external merge  Disk: 3528kB
                                                                                 ->  Seq Scan on "WrapCCCs" "wrapCCC"  (cost=0.00..668.44 rows=14844 width=226) (actual time=0.007..2.391 rows=14870 loops=3)
                                                                     ->  Index Scan using "UnwrapCCCs_pkey" on "UnwrapCCCs" "unwrapCCC"  (cost=0.41..9451.27 rows=14328 width=1050) (actual time=0.026..11.500 rows=14327 loops=3)
                                                               ->  Index Scan using "Pays_pkey" on "Pays" pay  (cost=0.42..12480.18 rows=77851 width=130) (actual time=0.010..52.259 rows=77847 loops=3)
                                                         ->  Sort  (cost=61.33..62.46 rows=450 width=213) (actual time=0.504..0.635 rows=468 loops=3)
                                                               Sort Key: "setRegularKey"."transactionHash"
                                                               Sort Method: quicksort  Memory: 149kB
                                                               ->  Seq Scan on "SetRegularKeys" "setRegularKey"  (cost=0.00..41.50 rows=450 width=213) (actual time=0.011..0.121 rows=468 loops=3)
                                                   ->  Sort  (cost=15.86..16.19 rows=130 width=568) (actual time=0.008..0.008 rows=0 loops=3)
                                                         Sort Key: "createShard"."transactionHash"
                                                         Sort Method: quicksort  Memory: 25kB
                                                         ->  Seq Scan on "CreateShards" "createShard"  (cost=0.00..11.30 rows=130 width=568) (actual time=0.005..0.005 rows=0 loops=3)
                                             ->  Sort  (cost=15.86..16.19 rows=130 width=568) (actual time=0.004..0.004 rows=0 loops=3)
                                                   Sort Key: "setShardOwners"."transactionHash"
                                                   Sort Method: quicksort  Memory: 25kB
                                                   ->  Seq Scan on "SetShardOwners" "setShardOwners"  (cost=0.00..11.30 rows=130 width=568) (actual time=0.003..0.003 rows=0 loops=3)
                                       ->  Sort  (cost=15.86..16.19 rows=130 width=568) (actual time=0.004..0.004 rows=0 loops=3)
                                             Sort Key: "setShardUsers"."transactionHash"
                                             Sort Method: quicksort  Memory: 25kB
                                             ->  Seq Scan on "SetShardUsers" "setShardUsers"  (cost=0.00..11.30 rows=130 width=568) (actual time=0.003..0.003 rows=0 loops=3)
                                 ->  Sort  (cost=11.04..11.11 rows=30 width=2080) (actual time=0.005..0.005 rows=0 loops=3)
                                       Sort Key: store."transactionHash"
                                       Sort Method: quicksort  Memory: 25kB
                                       ->  Seq Scan on "Stores" store  (cost=0.00..10.30 rows=30 width=2080) (actual time=0.004..0.004 rows=0 loops=3)
                           ->  Sort  (cost=11.91..12.04 rows=50 width=1564) (actual time=0.004..0.004 rows=0 loops=3)
                                 Sort Key: remove."transactionHash"
                                 Sort Method: quicksort  Memory: 25kB
                                 ->  Seq Scan on "Removes" remove  (cost=0.00..10.50 rows=50 width=1564) (actual time=0.003..0.003 rows=0 loops=3)
                     ->  Sort  (cost=12.85..13.02 rows=70 width=1052) (actual time=0.009..0.011 rows=7 loops=3)
                           Sort Key: custom."transactionHash"
                           Sort Method: quicksort  Memory: 26kB
                           ->  Seq Scan on "Customs" custom  (cost=0.00..10.70 rows=70 width=1052) (actual time=0.005..0.005 rows=7 loops=3)
 Planning time: 25.181 ms
 Execution time: 648.608 ms
(102 rows)

[cubistplay]

log after queryInterface.addIndex("Transactions", ["blockNumber", "transactionIndex"]).

transferAsset

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=366113.38..366136.71 rows=200 width=22024) (actual time=394.331..401.540 rows=200 loops=1)
   ->  Gather Merge  (cost=366113.38..369923.74 rows=32658 width=22024) (actual time=394.330..401.515 rows=200 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=365113.35..365154.17 rows=16329 width=22024) (actual time=385.965..386.016 rows=88 loops=3)
               Sort Key: "Transaction"."blockNumber" DESC, "Transaction"."transactionIndex" DESC
               Sort Method: external merge  Disk: 24120kB
               ->  Hash Left Join  (cost=1221.72..56334.72 rows=16329 width=22024) (actual time=9.148..341.665 rows=13344 loops=3)
                     Hash Cond: (("Transaction".hash)::text = (custom."transactionHash")::text)
                     ->  Hash Left Join  (cost=1210.14..56280.28 rows=16329 width=20972) (actual time=9.132..332.596 rows=13344 loops=3)
                           Hash Cond: (("Transaction".hash)::text = (remove."transactionHash")::text)
                           ->  Hash Left Join  (cost=1199.02..56226.29 rows=16329 width=19408) (actual time=9.120..323.649 rows=13344 loops=3)
                                 Hash Cond: (("Transaction".hash)::text = (store."transactionHash")::text)
                                 ->  Hash Left Join  (cost=1188.34..56172.75 rows=16329 width=17328) (actual time=9.108..310.775 rows=13344 loops=3)
                                       Hash Cond: (("Transaction".hash)::text = ("setShardUsers"."transactionHash")::text)
                                       ->  Hash Left Join  (cost=1175.42..56116.96 rows=16329 width=16760) (actual time=9.096..302.190 rows=13344 loops=3)
                                             Hash Cond: (("Transaction".hash)::text = ("setShardOwners"."transactionHash")::text)
                                             ->  Hash Left Join  (cost=1162.49..56061.18 rows=16329 width=16192) (actual time=9.083..293.678 rows=13344 loops=3)
                                                   Hash Cond: (("Transaction".hash)::text = ("createShard"."transactionHash")::text)
                                                   ->  Hash Left Join  (cost=1149.57..56005.39 rows=16329 width=15624) (actual time=9.065..284.594 rows=13344 loops=3)
                                                         Hash Cond: (("Transaction".hash)::text = ("setRegularKey"."transactionHash")::text)
                                                         ->  Nested Loop Left Join  (cost=1102.44..55915.40 rows=16329 width=15411) (actual time=8.817..275.355 rows=13344 loops=3)
                                                               ->  Nested Loop Left Join  (cost=1102.03..44098.88 rows=16329 width=15281) (actual time=8.802..211.622 rows=13344 loops=3)
                                                                     ->  Hash Left Join  (cost=1101.62..33269.84 rows=16329 width=14231) (actual time=8.777..151.827 rows=13344 loops=3)
                                                                           Hash Cond: (("Transaction".hash)::text = ("wrapCCC"."transactionHash")::text)
                                                                           ->  Hash Left Join  (cost=247.62..32372.98 rows=16329 width=14005) (actual time=2.334..135.642 rows=13344 loops=3)
                                                                                 Hash Cond: (("Transaction".hash)::text = ("increaseAssetSupply"."transactionHash")::text)
                                                                                 ->  Hash Left Join  (cost=236.95..32319.45 rows=16329 width=11317) (actual time=2.324..128.267 rows=13344 loops=3)
                                                                                       Hash Cond: (("Transaction".hash)::text = ("changeAssetScheme"."transactionHash")::text)
                                                                                       ->  Hash Left Join  (cost=226.50..32266.13 rows=16329 width=8617) (actual time=2.311..121.300 rows=13344 loops=3)
                                                                                             Hash Cond: (("Transaction".hash)::text = ("decomposeAsset"."transactionHash")::text)
                                                                                             ->  Hash Left Join  (cost=215.15..32211.92 rows=16329 width=7473) (actual time=2.299..114.733 rows=13344 loops=3)
                                                                                                   Hash Cond: (("Transaction".hash)::text = ("composeAsset"."transactionHash")::text)
                                                                                                   ->  Nested Loop Left Join  (cost=204.92..32158.83 rows=16329 width=3145) (actual time=2.275..108.261 rows=13344 loops=3)
                                                                                                         ->  Hash Left Join  (cost=204.51..9309.63 rows=16329 width=1472) (actual time=2.237..27.782 rows=13344 loops=3)
                                                                                                               Hash Cond: (("Transaction".hash)::text = ("mintAsset"."transactionHash")::text)
                                                                                                               ->  Parallel Seq Scan on "Transactions" "Transaction"  (cost=0.00..9062.26 rows=16329 width=458) (actual time=0.031..19.047 rows=13344 loops=3)
                                                                                                                     Filter: ((NOT "isPending") AND ((type)::text = 'transferAsset'::text))
                                                                                                                     Rows Removed by Filter: 36559
                                                                                                               ->  Hash  (cost=177.56..177.56 rows=2156 width=1014) (actual time=2.154..2.154 rows=2155 loops=3)
                                                                                                                     Buckets: 4096  Batches: 1  Memory Usage: 1082kB
                                                                                                                     ->  Seq Scan on "MintAssets" "mintAsset"  (cost=0.00..177.56 rows=2156 width=1014) (actual time=0.011..0.964 rows=2155 loops=3)
                                                                                                         ->  Index Scan using "TransferAssets_pkey" on "TransferAssets" "transferAsset"  (cost=0.41..1.40 rows=1 width=1673) (actual time=0.005..0.005 rows=1 loops=40031)
                                                                                                               Index Cond: (("Transaction".hash)::text = ("transactionHash")::text)
                                                                                                   ->  Hash  (cost=10.10..10.10 rows=10 width=4328) (actual time=0.009..0.009 rows=0 loops=3)
                                                                                                         Buckets: 1024  Batches: 1  Memory Usage: 8kB
                                                                                                         ->  Seq Scan on "ComposeAssets" "composeAsset"  (cost=0.00..10.10 rows=10 width=4328) (actual time=0.009..0.009 rows=0 loops=3)
                                                                                             ->  Hash  (cost=10.60..10.60 rows=60 width=1144) (actual time=0.003..0.003 rows=0 loops=3)
                                                                                                   Buckets: 1024  Batches: 1  Memory Usage: 8kB
                                                                                                   ->  Seq Scan on "DecomposeAssets" "decomposeAsset"  (cost=0.00..10.60 rows=60 width=1144) (actual time=0.003..0.003 rows=0 loops=3)
                                                                                       ->  Hash  (cost=10.20..10.20 rows=20 width=2700) (actual time=0.003..0.003 rows=0 loops=3)
                                                                                             Buckets: 1024  Batches: 1  Memory Usage: 8kB
                                                                                             ->  Seq Scan on "ChangeAssetSchemes" "changeAssetScheme"  (cost=0.00..10.20 rows=20 width=2700) (actual time=0.003..0.003 rows=0 loops=3)
                                                                                 ->  Hash  (cost=10.30..10.30 rows=30 width=2688) (actual time=0.002..0.002 rows=0 loops=3)
                                                                                       Buckets: 1024  Batches: 1  Memory Usage: 8kB
                                                                                       ->  Seq Scan on "IncreaseAssetSupplies" "increaseAssetSupply"  (cost=0.00..10.30 rows=30 width=2688) (actual time=0.002..0.002 rows=0 loops=3)
                                                                           ->  Hash  (cost=668.44..668.44 rows=14844 width=226) (actual time=6.390..6.390 rows=14870 loops=3)
                                                                                 Buckets: 16384  Batches: 1  Memory Usage: 3962kB
                                                                                 ->  Seq Scan on "WrapCCCs" "wrapCCC"  (cost=0.00..668.44 rows=14844 width=226) (actual time=0.007..2.507 rows=14870 loops=3)
                                                                     ->  Index Scan using "UnwrapCCCs_pkey" on "UnwrapCCCs" "unwrapCCC"  (cost=0.41..0.66 rows=1 width=1050) (actual time=0.004..0.004 rows=0 loops=40031)
                                                                           Index Cond: (("Transaction".hash)::text = ("transactionHash")::text)
                                                               ->  Index Scan using "Pays_pkey" on "Pays" pay  (cost=0.42..0.72 rows=1 width=130) (actual time=0.004..0.004 rows=0 loops=40031)
                                                                     Index Cond: (("Transaction".hash)::text = ("transactionHash")::text)
                                                         ->  Hash  (cost=41.50..41.50 rows=450 width=213) (actual time=0.235..0.235 rows=468 loops=3)
                                                               Buckets: 1024  Batches: 1  Memory Usage: 122kB
                                                               ->  Seq Scan on "SetRegularKeys" "setRegularKey"  (cost=0.00..41.50 rows=450 width=213) (actual time=0.015..0.125 rows=468 loops=3)
                                                   ->  Hash  (cost=11.30..11.30 rows=130 width=568) (actual time=0.005..0.005 rows=0 loops=3)
                                                         Buckets: 1024  Batches: 1  Memory Usage: 8kB
                                                         ->  Seq Scan on "CreateShards" "createShard"  (cost=0.00..11.30 rows=130 width=568) (actual time=0.005..0.005 rows=0 loops=3)
                                             ->  Hash  (cost=11.30..11.30 rows=130 width=568) (actual time=0.004..0.004 rows=0 loops=3)
                                                   Buckets: 1024  Batches: 1  Memory Usage: 8kB
                                                   ->  Seq Scan on "SetShardOwners" "setShardOwners"  (cost=0.00..11.30 rows=130 width=568) (actual time=0.004..0.004 rows=0 loops=3)
                                       ->  Hash  (cost=11.30..11.30 rows=130 width=568) (actual time=0.003..0.003 rows=0 loops=3)
                                             Buckets: 1024  Batches: 1  Memory Usage: 8kB
                                             ->  Seq Scan on "SetShardUsers" "setShardUsers"  (cost=0.00..11.30 rows=130 width=568) (actual time=0.003..0.003 rows=0 loops=3)
                                 ->  Hash  (cost=10.30..10.30 rows=30 width=2080) (actual time=0.003..0.003 rows=0 loops=3)
                                       Buckets: 1024  Batches: 1  Memory Usage: 8kB
                                       ->  Seq Scan on "Stores" store  (cost=0.00..10.30 rows=30 width=2080) (actual time=0.003..0.003 rows=0 loops=3)
                           ->  Hash  (cost=10.50..10.50 rows=50 width=1564) (actual time=0.003..0.003 rows=0 loops=3)
                                 Buckets: 1024  Batches: 1  Memory Usage: 8kB
                                 ->  Seq Scan on "Removes" remove  (cost=0.00..10.50 rows=50 width=1564) (actual time=0.002..0.002 rows=0 loops=3)
                     ->  Hash  (cost=10.70..10.70 rows=70 width=1052) (actual time=0.008..0.008 rows=7 loops=3)
                           Buckets: 1024  Batches: 1  Memory Usage: 10kB
                           ->  Seq Scan on "Customs" custom  (cost=0.00..10.70 rows=70 width=1052) (actual time=0.004..0.005 rows=7 loops=3)
 Planning time: 29.420 ms
 Execution time: 404.720 ms
(86 rows)

pay

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=677855.16..677878.49 rows=200 width=22024) (actual time=577.751..585.048 rows=200 loops=1)
   ->  Gather Merge  (cost=677855.16..685297.14 rows=63784 width=22024) (actual time=577.750..585.021 rows=200 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=676855.13..676934.86 rows=31892 width=22024) (actual time=551.052..551.086 rows=151 loops=3)
               Sort Key: "Transaction"."blockNumber" DESC, "Transaction"."transactionIndex" DESC
               Sort Method: external merge  Disk: 12288kB
               ->  Merge Left Join  (cost=20282.08..73624.47 rows=31892 width=22024) (actual time=126.006..511.023 rows=25950 loops=3)
                     Merge Cond: (("Transaction".hash)::text = (custom."transactionHash")::text)
                     ->  Merge Left Join  (cost=20269.23..73531.39 rows=31892 width=20972) (actual time=125.993..494.152 rows=25950 loops=3)
                           Merge Cond: (("Transaction".hash)::text = (remove."transactionHash")::text)
                           ->  Merge Left Join  (cost=20257.32..73439.39 rows=31892 width=19408) (actual time=125.987..481.625 rows=25950 loops=3)
                                 Merge Cond: (("Transaction".hash)::text = (store."transactionHash")::text)
                                 ->  Merge Left Join  (cost=20246.28..73348.42 rows=31892 width=17328) (actual time=125.979..469.030 rows=25950 loops=3)
                                       Merge Cond: (("Transaction".hash)::text = ("setShardUsers"."transactionHash")::text)
                                       ->  Merge Left Join  (cost=20230.42..73251.89 rows=31892 width=16760) (actual time=125.972..457.201 rows=25950 loops=3)
                                             Merge Cond: (("Transaction".hash)::text = ("setShardOwners"."transactionHash")::text)
                                             ->  Merge Left Join  (cost=20214.56..73155.37 rows=31892 width=16192) (actual time=125.964..445.579 rows=25950 loops=3)
                                                   Merge Cond: (("Transaction".hash)::text = ("createShard"."transactionHash")::text)
                                                   ->  Merge Left Join  (cost=20198.69..73058.84 rows=31892 width=15624) (actual time=125.948..434.216 rows=25950 loops=3)
                                                         Merge Cond: (("Transaction".hash)::text = ("setRegularKey"."transactionHash")::text)
                                                         ->  Merge Left Join  (cost=20137.36..72914.57 rows=31892 width=15411) (actual time=125.431..418.392 rows=25950 loops=3)
                                                               Merge Cond: (("Transaction".hash)::text = (pay."transactionHash")::text)
                                                               ->  Merge Left Join  (cost=20136.94..59993.88 rows=31892 width=15281) (actual time=125.414..328.731 rows=25950 loops=3)
                                                                     Merge Cond: (("Transaction".hash)::text = ("unwrapCCC"."transactionHash")::text)
                                                                     ->  Merge Left Join  (cost=20136.53..50396.48 rows=31892 width=14231) (actual time=125.386..296.733 rows=25950 loops=3)
                                                                           Merge Cond: (("Transaction".hash)::text = ("wrapCCC"."transactionHash")::text)
                                                                           ->  Merge Left Join  (cost=18439.58..48513.90 rows=31892 width=14005) (actual time=78.124..228.323 rows=25950 loops=3)
                                                                                 Merge Cond: (("Transaction".hash)::text = ("increaseAssetSupply"."transactionHash")::text)
                                                                                 ->  Merge Left Join  (cost=18428.55..48422.92 rows=31892 width=11317) (actual time=78.115..218.342 rows=25950 loops=3)
                                                                                       Merge Cond: (("Transaction".hash)::text = ("changeAssetScheme"."transactionHash")::text)
                                                                                       ->  Merge Left Join  (cost=18417.91..48332.42 rows=31892 width=8617) (actual time=78.105..209.524 rows=25950 loops=3)
                                                                                             Merge Cond: (("Transaction".hash)::text = ("decomposeAsset"."transactionHash")::text)
                                                                                             ->  Merge Left Join  (cost=18405.54..48239.89 rows=31892 width=7473) (actual time=78.098..201.614 rows=25950 loops=3)
                                                                                                   Merge Cond: (("Transaction".hash)::text = ("composeAsset"."transactionHash")::text)
                                                                                                   ->  Nested Loop Left Join  (cost=18395.28..48149.82 rows=31892 width=3145) (actual time=78.082..193.815 rows=25950 loops=3)
                                                                                                         ->  Merge Left Join  (cost=18394.86..18569.70 rows=31892 width=1472) (actual time=78.049..104.671 rows=25950 loops=3)
                                                                                                               Merge Cond: (("Transaction".hash)::text = ("mintAsset"."transactionHash")::text)
                                                                                                               ->  Sort  (cost=18097.92..18177.65 rows=31892 width=458) (actual time=73.685..86.972 rows=25950 loops=3)
                                                                                                                     Sort Key: "Transaction".hash
                                                                                                                     Sort Method: external merge  Disk: 8808kB
                                                                                                                     ->  Parallel Seq Scan on "Transactions" "Transaction"  (cost=0.00..9062.26 rows=31892 width=458) (actual time=0.017..15.660 rows=25950 loops=3)
                                                                                                                           Filter: ((NOT "isPending") AND ((type)::text = 'pay'::text))
                                                                                                                           Rows Removed by Filter: 23953
                                                                                                               ->  Sort  (cost=296.94..302.33 rows=2156 width=1014) (actual time=4.357..4.864 rows=2155 loops=3)
                                                                                                                     Sort Key: "mintAsset"."transactionHash"
                                                                                                                     Sort Method: quicksort  Memory: 1262kB
                                                                                                                     ->  Seq Scan on "MintAssets" "mintAsset"  (cost=0.00..177.56 rows=2156 width=1014) (actual time=0.024..0.971 rows=2155 loops=3)
                                                                                                         ->  Index Scan using "TransferAssets_pkey" on "TransferAssets" "transferAsset"  (cost=0.41..0.93 rows=1 width=1673) (actual time=0.003..0.003 rows=0 loops=77849)
                                                                                                               Index Cond: (("Transaction".hash)::text = ("transactionHash")::text)
                                                                                                   ->  Sort  (cost=10.27..10.29 rows=10 width=4328) (actual time=0.014..0.014 rows=0 loops=3)
                                                                                                         Sort Key: "composeAsset"."transactionHash"
                                                                                                         Sort Method: quicksort  Memory: 25kB
                                                                                                         ->  Seq Scan on "ComposeAssets" "composeAsset"  (cost=0.00..10.10 rows=10 width=4328) (actual time=0.008..0.008 rows=0 loops=3)
                                                                                             ->  Sort  (cost=12.37..12.52 rows=60 width=1144) (actual time=0.005..0.005 rows=0 loops=3)
                                                                                                   Sort Key: "decomposeAsset"."transactionHash"
                                                                                                   Sort Method: quicksort  Memory: 25kB
                                                                                                   ->  Seq Scan on "DecomposeAssets" "decomposeAsset"  (cost=0.00..10.60 rows=60 width=1144) (actual time=0.003..0.003 rows=0 loops=3)
                                                                                       ->  Sort  (cost=10.63..10.68 rows=20 width=2700) (actual time=0.007..0.007 rows=0 loops=3)
                                                                                             Sort Key: "changeAssetScheme"."transactionHash"
                                                                                             Sort Method: quicksort  Memory: 25kB
                                                                                             ->  Seq Scan on "ChangeAssetSchemes" "changeAssetScheme"  (cost=0.00..10.20 rows=20 width=2700) (actual time=0.004..0.004 rows=0 loops=3)
                                                                                 ->  Sort  (cost=11.04..11.11 rows=30 width=2688) (actual time=0.007..0.007 rows=0 loops=3)
                                                                                       Sort Key: "increaseAssetSupply"."transactionHash"
                                                                                       Sort Method: quicksort  Memory: 25kB
                                                                                       ->  Seq Scan on "IncreaseAssetSupplies" "increaseAssetSupply"  (cost=0.00..10.30 rows=30 width=2688) (actual time=0.003..0.003 rows=0 loops=3)
                                                                           ->  Sort  (cost=1696.95..1734.06 rows=14844 width=226) (actual time=47.256..49.932 rows=14870 loops=3)
                                                                                 Sort Key: "wrapCCC"."transactionHash"
                                                                                 Sort Method: external merge  Disk: 3528kB
                                                                                 ->  Seq Scan on "WrapCCCs" "wrapCCC"  (cost=0.00..668.44 rows=14844 width=226) (actual time=0.007..2.478 rows=14870 loops=3)
                                                                     ->  Index Scan using "UnwrapCCCs_pkey" on "UnwrapCCCs" "unwrapCCC"  (cost=0.41..9451.27 rows=14328 width=1050) (actual time=0.023..13.868 rows=14327 loops=3)
                                                               ->  Index Scan using "Pays_pkey" on "Pays" pay  (cost=0.42..12480.18 rows=77851 width=130) (actual time=0.009..55.275 rows=77848 loops=3)
                                                         ->  Sort  (cost=61.33..62.46 rows=450 width=213) (actual time=0.513..0.640 rows=468 loops=3)
                                                               Sort Key: "setRegularKey"."transactionHash"
                                                               Sort Method: quicksort  Memory: 149kB
                                                               ->  Seq Scan on "SetRegularKeys" "setRegularKey"  (cost=0.00..41.50 rows=450 width=213) (actual time=0.010..0.125 rows=468 loops=3)
                                                   ->  Sort  (cost=15.86..16.19 rows=130 width=568) (actual time=0.013..0.013 rows=0 loops=3)
                                                         Sort Key: "createShard"."transactionHash"
                                                         Sort Method: quicksort  Memory: 25kB
                                                         ->  Seq Scan on "CreateShards" "createShard"  (cost=0.00..11.30 rows=130 width=568) (actual time=0.009..0.009 rows=0 loops=3)
                                             ->  Sort  (cost=15.86..16.19 rows=130 width=568) (actual time=0.006..0.006 rows=0 loops=3)
                                                   Sort Key: "setShardOwners"."transactionHash"
                                                   Sort Method: quicksort  Memory: 25kB
                                                   ->  Seq Scan on "SetShardOwners" "setShardOwners"  (cost=0.00..11.30 rows=130 width=568) (actual time=0.004..0.004 rows=0 loops=3)
                                       ->  Sort  (cost=15.86..16.19 rows=130 width=568) (actual time=0.004..0.004 rows=0 loops=3)
                                             Sort Key: "setShardUsers"."transactionHash"
                                             Sort Method: quicksort  Memory: 25kB
                                             ->  Seq Scan on "SetShardUsers" "setShardUsers"  (cost=0.00..11.30 rows=130 width=568) (actual time=0.002..0.002 rows=0 loops=3)
                                 ->  Sort  (cost=11.04..11.11 rows=30 width=2080) (actual time=0.005..0.005 rows=0 loops=3)
                                       Sort Key: store."transactionHash"
                                       Sort Method: quicksort  Memory: 25kB
                                       ->  Seq Scan on "Stores" store  (cost=0.00..10.30 rows=30 width=2080) (actual time=0.003..0.003 rows=0 loops=3)
                           ->  Sort  (cost=11.91..12.04 rows=50 width=1564) (actual time=0.004..0.004 rows=0 loops=3)
                                 Sort Key: remove."transactionHash"
                                 Sort Method: quicksort  Memory: 25kB
                                 ->  Seq Scan on "Removes" remove  (cost=0.00..10.50 rows=50 width=1564) (actual time=0.003..0.003 rows=0 loops=3)
                     ->  Sort  (cost=12.85..13.02 rows=70 width=1052) (actual time=0.009..0.012 rows=7 loops=3)
                           Sort Key: custom."transactionHash"
                           Sort Method: quicksort  Memory: 26kB
                           ->  Seq Scan on "Customs" custom  (cost=0.00..10.70 rows=70 width=1052) (actual time=0.005..0.006 rows=7 loops=3)
 Planning time: 61.347 ms
 Execution time: 588.194 ms
(102 rows)

[sgkim126]

SELECT
       "Transaction"."hash", "Transaction"."blockNumber", "Transaction"."blockHash", "Transaction"."tracker",
       "Transaction"."transactionIndex", "Transaction"."type", "Transaction"."seq", "Transaction"."fee",
       "Transaction"."networkId", "Transaction"."sig", "Transaction"."signer", "Transaction"."errorHint",
       "Transaction"."timestamp", "Transaction"."isPending", "Transaction"."pendingTimestamp",
       "Transaction"."createdAt", "Transaction"."updatedAt",
       "mintAsset"."transactionHash" AS "mintAsset.transactionHash", "mintAsset"."networkId" AS "mintAsset.networkId",
       "mintAsset"."shardId" AS "mintAsset.shardId", "mintAsset"."metadata" AS "mintAsset.metadata",
       "mintAsset"."approver" AS "mintAsset.approver", "mintAsset"."registrar" AS "mintAsset.registrar",
       "mintAsset"."allowedScriptHashes" AS "mintAsset.allowedScriptHashes",
       "mintAsset"."approvals" AS "mintAsset.approvals", "mintAsset"."lockScriptHash" AS "mintAsset.lockScriptHash",
       "mintAsset"."parameters" AS "mintAsset.parameters", "mintAsset"."supply" AS "mintAsset.supply",
       "mintAsset"."assetName" AS "mintAsset.assetName", "mintAsset"."assetType" AS "mintAsset.assetType",
       "mintAsset"."recipient" AS "mintAsset.recipient", "mintAsset"."createdAt" AS "mintAsset.createdAt",
       "mintAsset"."updatedAt" AS "mintAsset.updatedAt",
       "transferAsset"."transactionHash" AS "transferAsset.transactionHash",
       "transferAsset"."networkId" AS "transferAsset.networkId", "transferAsset"."metadata" AS "transferAsset.metadata",
       "transferAsset"."approvals" AS "transferAsset.approvals",
       "transferAsset"."expiration" AS "transferAsset.expiration", "transferAsset"."inputs" AS "transferAsset.inputs",
       "transferAsset"."burns" AS "transferAsset.burns", "transferAsset"."outputs" AS "transferAsset.outputs",
       "transferAsset"."orders" AS "transferAsset.orders", "transferAsset"."createdAt" AS "transferAsset.createdAt",
       "transferAsset"."updatedAt" AS "transferAsset.updatedAt",
       "composeAsset"."transactionHash" AS "composeAsset.transactionHash",
       "composeAsset"."networkId" AS "composeAsset.networkId", "composeAsset"."shardId" AS "composeAsset.shardId",
       "composeAsset"."metadata" AS "composeAsset.metadata", "composeAsset"."approver" AS "composeAsset.approver",
       "composeAsset"."registrar" AS "composeAsset.registrar",
       "composeAsset"."allowedScriptHashes" AS "composeAsset.allowedScriptHashes",
       "composeAsset"."approvals" AS "composeAsset.approvals",
       "composeAsset"."lockScriptHash" AS "composeAsset.lockScriptHash",
       "composeAsset"."parameters" AS "composeAsset.parameters",
       "composeAsset"."supply" AS "composeAsset.supply", "composeAsset"."assetName" AS "composeAsset.assetName",
       "composeAsset"."assetType" AS "composeAsset.assetType", "composeAsset"."recipient" AS "composeAsset.recipient",
       "composeAsset"."inputs" AS "composeAsset.inputs", "composeAsset"."createdAt" AS "composeAsset.createdAt",
       "composeAsset"."updatedAt" AS "composeAsset.updatedAt",
       "decomposeAsset"."transactionHash" AS "decomposeAsset.transactionHash",
       "decomposeAsset"."networkId" AS "decomposeAsset.networkId",
       "decomposeAsset"."approvals" AS "decomposeAsset.approvals", "decomposeAsset"."input" AS "decomposeAsset.input",
       "decomposeAsset"."outputs" AS "decomposeAsset.outputs",
       "decomposeAsset"."createdAt" AS "decomposeAsset.createdAt",
       "decomposeAsset"."updatedAt" AS "decomposeAsset.updatedAt",
       "changeAssetScheme"."transactionHash" AS "changeAssetScheme.transactionHash",
       "changeAssetScheme"."assetType" AS "changeAssetScheme.assetType",
       "changeAssetScheme"."networkId" AS "changeAssetScheme.networkId",
       "changeAssetScheme"."shardId" AS "changeAssetScheme.shardId",
       "changeAssetScheme"."metadata" AS "changeAssetScheme.metadata",
       "changeAssetScheme"."approver" AS "changeAssetScheme.approver",
       "changeAssetScheme"."registrar" AS "changeAssetScheme.registrar",
       "changeAssetScheme"."allowedScriptHashes" AS "changeAssetScheme.allowedScriptHashes",
       "changeAssetScheme"."approvals" AS "changeAssetScheme.approvals",
       "changeAssetScheme"."seq" AS "changeAssetScheme.seq",
       "changeAssetScheme"."createdAt" AS "changeAssetScheme.createdAt",
       "changeAssetScheme"."updatedAt" AS "changeAssetScheme.updatedAt",
       "increaseAssetSupply"."transactionHash" AS "increaseAssetSupply.transactionHash",
       "increaseAssetSupply"."networkId" AS "increaseAssetSupply.networkId",
       "increaseAssetSupply"."shardId" AS "increaseAssetSupply.shardId",
       "increaseAssetSupply"."assetType" AS "increaseAssetSupply.assetType",
       "increaseAssetSupply"."approvals" AS "increaseAssetSupply.approvals",
       "increaseAssetSupply"."seq" AS "increaseAssetSupply.seq",
       "increaseAssetSupply"."lockScriptHash" AS "increaseAssetSupply.lockScriptHash",
       "increaseAssetSupply"."parameters" AS "increaseAssetSupply.parameters",
       "increaseAssetSupply"."supply" AS "increaseAssetSupply.supply",
       "increaseAssetSupply"."recipient" AS "increaseAssetSupply.recipient",
       "increaseAssetSupply"."createdAt" AS "increaseAssetSupply.createdAt",
       "increaseAssetSupply"."updatedAt" AS "increaseAssetSupply.updatedAt",
       "wrapCCC"."transactionHash" AS "wrapCCC.transactionHash", "wrapCCC"."shardId" AS "wrapCCC.shardId",
       "wrapCCC"."lockScriptHash" AS "wrapCCC.lockScriptHash", "wrapCCC"."parameters" AS "wrapCCC.parameters",
       "wrapCCC"."quantity" AS "wrapCCC.quantity", "wrapCCC"."recipient" AS "wrapCCC.recipient",
       "wrapCCC"."createdAt" AS "wrapCCC.createdAt", "wrapCCC"."updatedAt" AS "wrapCCC.updatedAt",
       "unwrapCCC"."transactionHash" AS "unwrapCCC.transactionHash", "unwrapCCC"."receiver" AS "unwrapCCC.receiver",
       "unwrapCCC"."burn" AS "unwrapCCC.burn", "unwrapCCC"."createdAt" AS "unwrapCCC.createdAt",
       "unwrapCCC"."updatedAt" AS "unwrapCCC.updatedAt", "pay"."transactionHash" AS "pay.transactionHash",
       "pay"."receiver" AS "pay.receiver", "pay"."quantity" AS "pay.quantity", "pay"."createdAt" AS "pay.createdAt",
       "pay"."updatedAt" AS "pay.updatedAt", "setRegularKey"."transactionHash" AS "setRegularKey.transactionHash",
       "setRegularKey"."key" AS "setRegularKey.key", "setRegularKey"."createdAt" AS "setRegularKey.createdAt",
       "setRegularKey"."updatedAt" AS "setRegularKey.updatedAt",
       "createShard"."transactionHash" AS "createShard.transactionHash",
       "createShard"."shardId" AS "createShard.shardId", "createShard"."users" AS "createShard.users",
       "createShard"."createdAt" AS "createShard.createdAt", "createShard"."updatedAt" AS "createShard.updatedAt",
       "setShardOwners"."transactionHash" AS "setShardOwners.transactionHash",
       "setShardOwners"."shardId" AS "setShardOwners.shardId", "setShardOwners"."owners" AS "setShardOwners.owners",
       "setShardOwners"."createdAt" AS "setShardOwners.createdAt",
       "setShardOwners"."updatedAt" AS "setShardOwners.updatedAt",
       "setShardUsers"."transactionHash" AS "setShardUsers.transactionHash",
       "setShardUsers"."shardId" AS "setShardUsers.shardId",
       "setShardUsers"."users" AS "setShardUsers.users", "setShardUsers"."createdAt" AS "setShardUsers.createdAt",
       "setShardUsers"."updatedAt" AS "setShardUsers.updatedAt", "store"."transactionHash" AS "store.transactionHash",
       "store"."content" AS "store.content", "store"."certifier" AS "store.certifier",
       "store"."signature" AS "store.signature", "store"."createdAt" AS "store.createdAt",
       "store"."updatedAt" AS "store.updatedAt", "remove"."transactionHash" AS "remove.transactionHash",
       "remove"."textHash" AS "remove.textHash", "remove"."signature" AS "remove.signature",
       "remove"."createdAt" AS "remove.createdAt", "remove"."updatedAt" AS "remove.updatedAt",
       "custom"."transactionHash" AS "custom.transactionHash", "custom"."handlerId" AS "custom.handlerId",
       "custom"."content" AS "custom.content", "custom"."createdAt" AS "custom.createdAt",
       "custom"."updatedAt" AS "custom.updatedAt"
FROM "Transactions" AS "Transaction"
LEFT OUTER JOIN "MintAssets" AS "mintAsset"
    ON "Transaction"."hash" = "mintAsset"."transactionHash"
LEFT OUTER JOIN "TransferAssets" AS "transferAsset"
    ON "Transaction"."hash" = "transferAsset"."transactionHash"
LEFT OUTER JOIN "ComposeAssets" AS "composeAsset"
    ON "Transaction"."hash" = "composeAsset"."transactionHash"
LEFT OUTER JOIN "DecomposeAssets" AS "decomposeAsset"
    ON "Transaction"."hash" = "decomposeAsset"."transactionHash"
LEFT OUTER JOIN "ChangeAssetSchemes" AS "changeAssetScheme"
    ON "Transaction"."hash" = "changeAssetScheme"."transactionHash"
LEFT OUTER JOIN "IncreaseAssetSupplies" AS "increaseAssetSupply"
    ON "Transaction"."hash" = "increaseAssetSupply"."transactionHash"
LEFT OUTER JOIN "WrapCCCs" AS "wrapCCC"
    ON "Transaction"."hash" = "wrapCCC"."transactionHash"
LEFT OUTER JOIN "UnwrapCCCs" AS "unwrapCCC"
    ON "Transaction"."hash" = "unwrapCCC"."transactionHash"
LEFT OUTER JOIN "Pays" AS "pay"
    ON "Transaction"."hash" = "pay"."transactionHash"
LEFT OUTER JOIN "SetRegularKeys" AS "setRegularKey"
    ON "Transaction"."hash" = "setRegularKey"."transactionHash"
LEFT OUTER JOIN "CreateShards" AS "createShard"
    ON "Transaction"."hash" = "createShard"."transactionHash"
LEFT OUTER JOIN "SetShardOwners" AS "setShardOwners"
    ON "Transaction"."hash" = "setShardOwners"."transactionHash"
LEFT OUTER JOIN "SetShardUsers" AS "setShardUsers"
    ON "Transaction"."hash" = "setShardUsers"."transactionHash"
LEFT OUTER JOIN "Stores" AS "store"
    ON "Transaction"."hash" = "store"."transactionHash"
LEFT OUTER JOIN "Removes" AS "remove"
    ON "Transaction"."hash" = "remove"."transactionHash"
LEFT OUTER JOIN "Customs" AS "custom"
    ON "Transaction"."hash" = "custom"."transactionHash"
WHERE "Transaction"."isPending" = false
ORDER BY "Transaction"."blockNumber" DESC, "Transaction"."transactionIndex" DESC LIMIT 15 OFFSET 0;

Limit  (cost=81238.98..81239.01 rows=15 width=22072)
  ->  Sort  (cost=81238.98..81488.69 rows=99887 width=22072)
        Sort Key: "Transaction"."blockNumber" DESC, "Transaction"."transactionIndex" DESC
        ->  Hash Left Join  (cost=20725.30..78788.30 rows=99887 width=22072)
              Hash Cond: (("Transaction".hash)::text = (custom."transactionHash")::text)
              ->  Gather  (cost=20713.72..78514.51 rows=99887 width=21020)
                    Workers Planned: 2
                    ->  Merge Left Join  (cost=19713.72..67525.81 rows=41620 width=21020)
                          Merge Cond: (("Transaction".hash)::text = (remove."transactionHash")::text)
                          ->  Merge Left Join  (cost=19701.81..67409.39 rows=41620 width=19456)
                                Merge Cond: (("Transaction".hash)::text = (store."transactionHash")::text)
                                ->  Merge Left Join  (cost=19690.77..67294.03 rows=41620 width=17376)
                                      Merge Cond: (("Transaction".hash)::text = ("setShardUsers"."transactionHash")::text)
                                      ->  Merge Left Join  (cost=19674.91..67172.92 rows=41620 width=16808)
                                            Merge Cond: (("Transaction".hash)::text = ("setShardOwners"."transactionHash")::text)
                                            ->  Merge Left Join  (cost=19659.05..67051.82 rows=41620 width=16240)
                                                  Merge Cond: (("Transaction".hash)::text = ("createShard"."transactionHash")::text)
                                                  ->  Merge Left Join  (cost=19643.18..66930.71 rows=41620 width=15672)
                                                        Merge Cond: (("Transaction".hash)::text = ("setRegularKey"."transactionHash")::text)
                                                        ->  Merge Left Join  (cost=19613.61..66794.14 rows=41620 width=15459)
                                                              Merge Cond: (("Transaction".hash)::text = (pay."transactionHash")::text)
                                                              ->  Merge Left Join  (cost=19612.71..59929.62 rows=41620 width=15329)
                                                                    Merge Cond: (("Transaction".hash)::text = ("unwrapCCC"."transactionHash")::text)
                                                                    ->  Merge Left Join  (cost=19611.65..50683.36 rows=41620 width=14279)
                                                                          Merge Cond: (("Transaction".hash)::text = ("wrapCCC"."transactionHash")::text)
                                                                          ->  Merge Left Join  (cost=18037.67..48875.85 rows=41620 width=14053)
                                                                                Merge Cond: (("Transaction".hash)::text = ("increaseAssetSupply"."transactionHash")::text)
                                                                                ->  Merge Left Join  (cost=18026.64..48760.49 rows=41620 width=11365)
                                                                                      Merge Cond: (("Transaction".hash)::text = ("changeAssetScheme"."transactionHash")::text)
                                                                                      ->  Merge Left Join  (cost=18016.01..48645.63 rows=41620 width=8665)
                                                                                            Merge Cond: (("Transaction".hash)::text = ("decomposeAsset"."transactionHash")::text)
                                                                                            ->  Merge Left Join  (cost=18003.63..48528.65 rows=41620 width=7521)
                                                                                                  Merge Cond: (("Transaction".hash)::text = ("composeAsset"."transactionHash")::text)
                                                                                                  ->  Nested Loop Left Join  (cost=17993.37..48414.25 rows=41620 width=3193)
                                                                                                        ->  Merge Left Join  (cost=17992.95..18207.52 rows=41620 width=1470)
                                                                                                              Merge Cond: (("Transaction".hash)::text = ("mintAsset"."transactionHash")::text)
                                                                                                              ->  Sort  (cost=17908.49..18012.54 rows=41620 width=458)
                                                                                                                    Sort Key: "Transaction".hash
                                                                                                                    ->  Parallel Seq Scan on "Transactions" "Transaction"  (cost=0.00..6035.20 rows=41620 width=458)
                                                                                                                          Filter: (NOT "isPending")
                                                                                                              ->  Sort  (cost=84.47..86.23 rows=706 width=1012)
                                                                                                                    Sort Key: "mintAsset"."transactionHash"
                                                                                                                    ->  Seq Scan on "MintAssets" "mintAsset"  (cost=0.00..51.06 rows=706 width=1012)
                                                                                                        ->  Index Scan using "TransferAssets_pkey" on "TransferAssets" "transferAsset"  (cost=0.41..0.73 rows=1 width=1723)
                                                                                                              Index Cond: (("Transaction".hash)::text = ("transactionHash")::text)
                                                                                                  ->  Sort  (cost=10.27..10.29 rows=10 width=4328)
                                                                                                        Sort Key: "composeAsset"."transactionHash"
                                                                                                        ->  Seq Scan on "ComposeAssets" "composeAsset"  (cost=0.00..10.10 rows=10 width=4328)
                                                                                            ->  Sort  (cost=12.37..12.52 rows=60 width=1144)
                                                                                                  Sort Key: "decomposeAsset"."transactionHash"
                                                                                                  ->  Seq Scan on "DecomposeAssets" "decomposeAsset"  (cost=0.00..10.60 rows=60 width=1144)
                                                                                      ->  Sort  (cost=10.63..10.68 rows=20 width=2700)
                                                                                            Sort Key: "changeAssetScheme"."transactionHash"
                                                                                            ->  Seq Scan on "ChangeAssetSchemes" "changeAssetScheme"  (cost=0.00..10.20 rows=20 width=2700)
                                                                                ->  Sort  (cost=11.04..11.11 rows=30 width=2688)
                                                                                      Sort Key: "increaseAssetSupply"."transactionHash"
                                                                                      ->  Seq Scan on "IncreaseAssetSupplies" "increaseAssetSupply"  (cost=0.00..10.30 rows=30 width=2688)
                                                                          ->  Sort  (cost=1573.97..1609.28 rows=14126 width=226)
                                                                                Sort Key: "wrapCCC"."transactionHash"
                                                                                ->  Seq Scan on "WrapCCCs" "wrapCCC"  (cost=0.00..600.26 rows=14126 width=226)
                                                                    ->  Index Scan using "UnwrapCCCs_pkey" on "UnwrapCCCs" "unwrapCCC"  (cost=0.41..9049.32 rows=13934 width=1050)
                                                              ->  Index Scan using "Pays_pkey" on "Pays" pay  (cost=0.41..6487.19 rows=40992 width=130)
                                                        ->  Sort  (cost=29.57..30.38 rows=321 width=213)
                                                              Sort Key: "setRegularKey"."transactionHash"
                                                              ->  Seq Scan on "SetRegularKeys" "setRegularKey"  (cost=0.00..16.21 rows=321 width=213)
                                                  ->  Sort  (cost=15.86..16.19 rows=130 width=568)
                                                        Sort Key: "createShard"."transactionHash"
                                                        ->  Seq Scan on "CreateShards" "createShard"  (cost=0.00..11.30 rows=130 width=568)
                                            ->  Sort  (cost=15.86..16.19 rows=130 width=568)
                                                  Sort Key: "setShardOwners"."transactionHash"
                                                  ->  Seq Scan on "SetShardOwners" "setShardOwners"  (cost=0.00..11.30 rows=130 width=568)
                                      ->  Sort  (cost=15.86..16.19 rows=130 width=568)
                                            Sort Key: "setShardUsers"."transactionHash"
                                            ->  Seq Scan on "SetShardUsers" "setShardUsers"  (cost=0.00..11.30 rows=130 width=568)
                                ->  Sort  (cost=11.04..11.11 rows=30 width=2080)
                                      Sort Key: store."transactionHash"
                                      ->  Seq Scan on "Stores" store  (cost=0.00..10.30 rows=30 width=2080)
                          ->  Sort  (cost=11.91..12.04 rows=50 width=1564)
                                Sort Key: remove."transactionHash"
                                ->  Seq Scan on "Removes" remove  (cost=0.00..10.50 rows=50 width=1564)
              ->  Hash  (cost=10.70..10.70 rows=70 width=1052)
                    ->  Seq Scan on "Customs" custom  (cost=0.00..10.70 rows=70 width=1052)

[sgkim126]

I think querying twice would enhance the performance.
The first query.

SELECT "Transaction"."hash" as "hash"
FROM "Transactions" AS "Transaction"
WHERE "Transaction"."isPending" = false
ORDER BY "Transaction"."blockNumber" DESC, "Transaction"."transactionIndex" DESC LIMIT 15 OFFSET 0

The second query

/* ... */
WHERE "Transaction"."hash" in (the results of the first query)
ORDER BY "Transaction"."blockNumber" DESC, "Transaction"."transactionIndex" DESC

[sgkim126]

Closed by #253