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".
----------------------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
