Getting the pending transactions count is too slow

[joojis]

A simple SQL that counts pending transaction is slow.

explain analyze select count(*) from "Transactions" where "isPending"=true;
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=198137.95..198137.96 rows=1 width=8) (actual time=12279.098..12279.099 rows=1 loops=1)
   ->  Gather  (cost=1000.00..198137.95 rows=1 width=0) (actual time=12279.089..12420.670 rows=0 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Parallel Seq Scan on "Transactions"  (cost=0.00..197137.85 rows=1 width=0) (actual time=12219.238..12219.238 rows=0 loops=3)
               Filter: "isPending"
               Rows Removed by Filter: 1200893
 Planning time: 19.111 ms
 Execution time: 12421.378 ms

I think we need to add an index for the isPending column.

Here is the current table info:

                         Table "public.Transactions"
      Column      |           Type           | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+---------
 hash             | character varying(255)   |           | not null |
 blockNumber      | integer                  |           |          |
 blockHash        | character varying(255)   |           |          |
 tracker          | character varying(255)   |           |          |
 transactionIndex | integer                  |           |          |
 type             | character varying(255)   |           | not null |
 seq              | numeric(20,0)            |           | not null |
 fee              | character varying(255)   |           | not null |
 networkId        | character varying(255)   |           | not null |
 sig              | character varying(255)   |           | not null |
 signer           | character varying(255)   |           | not null |
 errorHint        | text                     |           |          |
 timestamp        | integer                  |           |          |
 isPending        | boolean                  |           | not null |
 pendingTimestamp | integer                  |           |          |
 createdAt        | timestamp with time zone |           | not null |
 updatedAt        | timestamp with time zone |           | not null |
Indexes:
    "Transactions_pkey" PRIMARY KEY, btree (hash)
    "transactions_block_hash" btree ("blockHash")
    "transactions_block_number_transaction_index" btree ("blockNumber", "transactionIndex")

[joojis]

Now it's fast

explain analyze select count(*) from "Transactions" where "isPending"=true;
                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4.45..4.46 rows=1 width=8) (actual time=32.786..32.786 rows=1 loops=1)
   ->  Index Only Scan using transactions_is_pending on "Transactions"  (cost=0.43..4.45 rows=1 width=0) (actual time=32.778..32.778 rows=0 loops=1)
         Index Cond: ("isPending" = true)
         Filter: "isPending"
         Heap Fetches: 0
 Planning time: 7.238 ms
 Execution time: 34.391 ms
(7 rows)