Counts on pop_basis table take a long time.

[ClaytonNorthey92]

Confirmation

• [X] I have checked for similar issues.

Describe the bug

The pop_basis table is our largest table, and counts on it take significantly longer than other tables. Luckily we do not use counts on this table in our application code. But we do with l2_keystones, so if l2_keystones ever got into a similar state then counts would take too long.

Find out why counts are taking so long on pop_basis is it size? etc.

Expected behaviour

Counts on pop_basis should seem instant.

Environment

Version: Operating System: Architecture:

Additional Information

Here are the query plans for one of our replicas

bfg=# explain select count(*) from pop_basis;
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=770916.44..770916.45 rows=1 width=8)
   ->  Gather  (cost=770916.22..770916.43 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=769916.22..769916.23 rows=1 width=8)
               ->  Parallel Seq Scan on pop_basis  (cost=0.00..764483.58 rows=2173058 width=0)
 JIT:
   Functions: 4
   Options: Inlining true, Optimization true, Expressions true, Deforming true
(8 rows)

bfg=# select count(*) from btc_blocks;
 count 
-------
 58786
(1 row)

bfg=# explain select count(*) from btc_blocks;
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Aggregate  (cost=1964.94..1964.95 rows=1 width=8)
   ->  Seq Scan on btc_blocks  (cost=0.00..1820.15 rows=57915 width=0)
(2 rows)

bfg=# explain select count(*) from l2_keystones;
                               QUERY PLAN                               
------------------------------------------------------------------------
 Aggregate  (cost=829.31..829.32 rows=1 width=8)
   ->  Seq Scan on l2_keystones  (cost=0.00..779.85 rows=19785 width=0)
(2 rows)

this could be due to parallel aggregation https://www.postgresql.org/docs/current/parallel-plans.html#PARALLEL-AGGREGATION

[ClaytonNorthey92]

actually L2KeystonesCount isn't even used, probably safe to delete