Re: AWS RDS PostgreSQL CPU Spiking to 100%

Hi,

We have an application where one of the APIs calling queries(attached) is spiking the CPU to 100% during load testing.

However, queries are making use of indexes(Bitmap Index and Bitmap Heap scan though). When run separately on DB queries hardly take less than 200 ms. Is CPU spiking due to Bitmap Heap Scan?

These queries are being called thousands of times. Application team says they have handled connection pooling from the Application side. So there is no connection pooling here from DB side. Current db instance size is "db.m4.4xlarge" 64 GB RAM 16 vCPU".  

The Application dev team has primary keys and foreign keys on tables so they are unable to partition the tables as well due to limitations of postgres partitioning. Columns in WHERE clauses are not constant in all queries to decide partition keys.

1. Does DB need more CPU considering this kind of load? 

2. Can the query be tuned further? It is already using indexes(Bitmap though).

3. Will connection pooling resolve the CPU Spike issues?

Also pasting Query and plans below.

----------------------exampleCount 1. Without internalexamplecode-----------------------

lmp_examples=> explain analyze with exampleCount as ( select examplestatuscode from example j where 1=1 and j.countrycode = 'AD'   and j.facilitycode in ('ABCD') and j.internalexamplecode in ('005','006','007','005') and ((j.examplestartdatetime  between '2020-05-18 00:00:00' and '2020-08-19 00:00:00' ) or j.examplestartdatetime IS NULL )  group by j.examplestatuscode)

lmp_examples-> select js.examplestatuscode,COALESCE(count(*),0) stat_count from exampleCount jc right outer join examplestatus js on jc.examplestatuscode=js.examplestatuscode group by js.examplestatuscode ;

                                                                                                                                                                                 QUERY PLAN                                                                                                                                                     

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 HashAggregate  (cost=79353.80..79353.89 rows=9 width=12) (actual time=88.847..88.850 rows=9 loops=1)

   Group Key: js.examplestatuscode

   CTE examplecount

     ->  HashAggregate  (cost=79352.42..79352.46 rows=4 width=4) (actual time=88.803..88.805 rows=5 loops=1)

           Group Key: j.examplestatuscode

           ->  Bitmap Heap Scan on example j  (cost=1547.81..79251.08 rows=40538 width=4) (actual time=18.424..69.658 rows=62851 loops=1)

                 Recheck Cond: ((((countrycode)::text = 'AD'::text) AND ((facilitycode)::text = 'ABCD'::text) AND ((internalexamplecode)::text = ANY ('{005,006,007,005}'::text[])) AND (examplestartdatetime >= '2020-05-18 00:00:00'::timestamp without time zone) AND (examplestartdatetime <= '2020-08-19 00:00:00'::timestamp without time zone)) OR (examplestartdatetime IS NULL))

                 Filter: (((countrycode)::text = 'AD'::text) AND ((facilitycode)::text = 'ABCD'::text) AND ((internalexamplecode)::text = ANY ('{005,006,007,005}'::text[])))

                 Rows Removed by Filter: 3

                 Heap Blocks: exact=18307

                 ->  BitmapOr  (cost=1547.81..1547.81 rows=40538 width=0) (actual time=15.707..15.707 rows=0 loops=1)

                       ->  Bitmap Index Scan on example_list9_idx  (cost=0.00..1523.10 rows=40538 width=0) (actual time=15.702..15.702 rows=62851 loops=1)

                             Index Cond: (((countrycode)::text = 'AD'::text) AND ((facilitycode)::text = 'ABCD'::text) AND ((internalexamplecode)::text = ANY ('{005,006,007,005}'::text[])) AND (examplestartdatetime >= '2020-05-18 00:00:00'::timestamp without time zone) AND (examplestartdatetime <= '2020-08-19 00:00:00'::timestamp without time zone))

                       ->  Bitmap Index Scan on example_list10_idx  (cost=0.00..4.44 rows=1 width=0) (actual time=0.004..0.004 rows=3 loops=1)

                             Index Cond: (examplestartdatetime IS NULL)

   ->  Hash Left Join  (cost=0.13..1.29 rows=9 width=4) (actual time=88.831..88.840 rows=9 loops=1)

         Hash Cond: ((js.examplestatuscode)::text = (jc.examplestatuscode)::text)

         ->  Seq Scan on examplestatus js  (cost=0.00..1.09 rows=9 width=4) (actual time=0.004..0.007 rows=9 loops=1)

         ->  Hash  (cost=0.08..0.08 rows=4 width=16) (actual time=88.817..88.817 rows=5 loops=1)

               Buckets: 1024  Batches: 1  Memory Usage: 9kB

               ->  CTE Scan on examplecount jc  (cost=0.00..0.08 rows=4 width=16) (actual time=88.807..88.812 rows=5 loops=1)

 Planning Time: 0.979 ms

 Execution Time: 89.036 ms

(23 rows)

----------------exampleCount 2. With internalexamplecode---------------------------------

lmp_examples=> explain analyze with exampleCount as ( select examplestatuscode,count(1) stat_count from example j where 1=1 and j.countrycode = 'AD'   and j.facilitycode in ('ABCD') and j.internalexamplecode in ('005','006','007','005') and ((j.examplestartdatetime  between '2020-05-18 00:00:00' and '2020-08-19 00:00:00' ) or j.examplestartdatetime IS NULL )  group by j.examplestatuscode)

lmp_examples-> select js.examplestatuscode,COALESCE(stat_count,0) stat_count from exampleCount jc right outer join examplestatus js on jc.examplestatuscode=js.examplestatuscode;

                                                                                                                                                                                 QUERY PLAN                                                                                                                                                     

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 Hash Left Join  (cost=79453.94..79455.10 rows=9 width=12) (actual time=89.660..89.669 rows=9 loops=1)

   Hash Cond: ((js.examplestatuscode)::text = (jc.examplestatuscode)::text)

   CTE examplecount

     ->  HashAggregate  (cost=79453.77..79453.81 rows=4 width=12) (actual time=89.638..89.640 rows=5 loops=1)

           Group Key: j.examplestatuscode

           ->  Bitmap Heap Scan on example j  (cost=1547.81..79251.08 rows=40538 width=4) (actual time=18.193..69.710 rows=62851 loops=1)

                 Recheck Cond: ((((countrycode)::text = 'AD'::text) AND ((facilitycode)::text = 'ABCD'::text) AND ((internalexamplecode)::text = ANY ('{005,006,007,005}'::text[])) AND (examplestartdatetime >= '2020-05-18 00:00:00'::timestamp without time zone) AND (examplestartdatetime <= '2020-08-19 00:00:00'::timestamp without time zone)) OR (examplestartdatetime IS NULL))

                 Filter: (((countrycode)::text = 'AD'::text) AND ((facilitycode)::text = 'ABCD'::text) AND ((internalexamplecode)::text = ANY ('{005,006,007,005}'::text[])))

                 Rows Removed by Filter: 3

                 Heap Blocks: exact=18307

                 ->  BitmapOr  (cost=1547.81..1547.81 rows=40538 width=0) (actual time=15.483..15.483 rows=0 loops=1)

                       ->  Bitmap Index Scan on example_list9_idx  (cost=0.00..1523.10 rows=40538 width=0) (actual time=15.477..15.478 rows=62851 loops=1)

                             Index Cond: (((countrycode)::text = 'AD'::text) AND ((facilitycode)::text = 'ABCD'::text) AND ((internalexamplecode)::text = ANY ('{005,006,007,005}'::text[])) AND (examplestartdatetime >= '2020-05-18 00:00:00'::timestamp without time zone) AND (examplestartdatetime <= '2020-08-19 00:00:00'::timestamp without time zone))

                       ->  Bitmap Index Scan on example_list10_idx  (cost=0.00..4.44 rows=1 width=0) (actual time=0.004..0.004 rows=3 loops=1)

                             Index Cond: (examplestartdatetime IS NULL)

   ->  Seq Scan on examplestatus js  (cost=0.00..1.09 rows=9 width=4) (actual time=0.003..0.005 rows=9 loops=1)

   ->  Hash  (cost=0.08..0.08 rows=4 width=24) (actual time=89.650..89.651 rows=5 loops=1)

         Buckets: 1024  Batches: 1  Memory Usage: 9kB

         ->  CTE Scan on examplecount jc  (cost=0.00..0.08 rows=4 width=24) (actual time=89.641..89.647 rows=5 loops=1)

 Planning Time: 0.470 ms

 Execution Time: 89.737 ms

------------------------exampleSelect-----------------------------------

lmp_examples=> explain analyze select j.id from example j where 1=1  and j.countrycode = 'AD'  and j.facilitycode in ('ABCD') and j.examplestatuscode in ('101') and j.internalexamplecode in ('005','006','007','005')  and ((j.examplestartdatetime between '2020-05-18 00:00:00' and '2020-08-19 00:00:00') or j.examplestartdatetime IS NULL)  ORDER BY createddate DESC limit 10;

                                                                                                          QUERY PLAN                                                    

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 Limit  (cost=71286.65..71286.68 rows=10 width=12) (actual time=47.351..47.359 rows=10 loops=1)

   ->  Sort  (cost=71286.65..71335.31 rows=19462 width=12) (actual time=47.349..47.352 rows=10 loops=1)

         Sort Key: createddate DESC

         Sort Method: top-N heapsort  Memory: 25kB

         ->  Bitmap Heap Scan on example j  (cost=1176.77..70866.09 rows=19462 width=12) (actual time=15.133..46.555 rows=2530 loops=1)

               Recheck Cond: (((countrycode)::text = 'AD'::text) AND ((facilitycode)::text = 'ABCD'::text) AND ((examplestatuscode)::text = '101'::text) AND ((internalexamplecode)::text = ANY ('{005,006,007,005}'::text[])))

               Filter: (((examplestartdatetime >= '2020-05-18 00:00:00'::timestamp without time zone) AND (examplestartdatetime <= '2020-08-19 00:00:00'::timestamp without time zone)) OR (examplestartdatetime IS NULL))

               Rows Removed by Filter: 38724

               Heap Blocks: exact=20923

               ->  Bitmap Index Scan on example_list1_idx  (cost=0.00..1171.90 rows=33211 width=0) (actual time=9.938..9.939 rows=41254 loops=1)

                     Index Cond: (((countrycode)::text = 'AD'::text) AND ((facilitycode)::text = 'ABCD'::text) AND ((examplestatuscode)::text = '101'::text) AND ((internalexamplecode)::text = ANY ('{005,006,007,005}'::text[])))

 Planning Time: 0.398 ms

 Execution Time: 47.416 ms

Regards,

Aditya.