Обсуждение: AWS RDS PostgreSQL CPU Spiking to 100%

Поиск
Список
Период
Сортировка

AWS RDS PostgreSQL CPU Spiking to 100%

От
aditya desai
Дата:
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.

Re: AWS RDS PostgreSQL CPU Spiking to 100%

От
Pavel Stehule
Дата:


út 8. 9. 2020 v 15:33 odesílatel aditya desai <admad123@gmail.com> napsal:
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.


if you have a lot of connection/disconnection per sec (more than ten or twenty), then connection pooling can be a significant win.

One symptom of this issue can be high cpu.

Regards

Pavel

 
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.

Re: AWS RDS PostgreSQL CPU Spiking to 100%

От
Jeff Janes
Дата:
On Tue, Sep 8, 2020 at 9:33 AM aditya desai <admad123@gmail.com> wrote:
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).

The CPU is there to be used.  Anything will use 100% of the CPU unless it runs into some other bottleneck first.

These queries are being called thousands of times.

Over what time period?  At what concurrency level?

 
Application team says they have handled connection pooling from the Application side.

Did they do it correctly?  Are you seeing a lot of connections churning through?


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

Is it currently running fast enough, or does it need to be faster?
 
2. Can the query be tuned further?

The query you show can't possibly generate the plan you show, so there is no way to know that.
 
3. Will connection pooling resolve the CPU Spike issues?

Not if the app-side pooling was done correctly.
 

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))

Note that the parenthesization of the OR condition is different between the recheck, and the query itself.  So I think that either the query or the plan has not been presented accurately.  Please double check them.

Also, what version of PostgreSQL are you using?  In v12, the CTE gets optimized away entirely.

Cheers,

Jeff

Re: AWS RDS PostgreSQL CPU Spiking to 100%

От
aditya desai
Дата:

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.

Re: AWS RDS PostgreSQL CPU Spiking to 100%

От
Prince Pathria
Дата:
We faced a similar issue, adding RDS proxy in front of RDS Postgres can help.
In our situation, there were a lot of connects/disconnects from Lambda functions although concurrency of Lambda was 100 only.
And adding connection pooler(RDS proxy) helped us to reduce the CPU load from 100% to 30%

Happy to help :)
Prince Pathria Systems Engineer | Certified Kubernetes Administrator | AWS Certified Solutions Architect Evive +91 9478670472 goevive.com


On Mon, Sep 28, 2020 at 9:21 PM aditya desai <admad123@gmail.com> wrote:

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.

Re: AWS RDS PostgreSQL CPU Spiking to 100%

От
aditya desai
Дата:
Thanks, I'll check it out. 

On Mon, Sep 28, 2020 at 9:40 PM Prince Pathria <prince.pathria@goevive.com> wrote:
We faced a similar issue, adding RDS proxy in front of RDS Postgres can help.
In our situation, there were a lot of connects/disconnects from Lambda functions although concurrency of Lambda was 100 only.
And adding connection pooler(RDS proxy) helped us to reduce the CPU load from 100% to 30%

Happy to help :)
Prince Pathria Systems Engineer | Certified Kubernetes Administrator | AWS Certified Solutions Architect Evive +91 9478670472 goevive.com


On Mon, Sep 28, 2020 at 9:21 PM aditya desai <admad123@gmail.com> wrote:

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.