Hi,
I am running postgresql db server 9.4.14 on AWS of C4.2xlarge instance type with 500 GB volume of volume type io1 with 25000 IOPS and I am seeing performance issues. The sql query response takes around 127713.413 ms time. Is there a way to find out the bottleneck?
The select sql query are as below :-
# SELECT serorgid,appname,sum(message_count) AS mtrc0,date_trunc('day', client_received_start_timestamp at time zone '+5:30:0')::timestamp without time zone AS time_unit FROM analytics."test.prod.fact" WHERE client_received_start_timestamp >= '2018-3-28 18:30:0' AND client_received_start_timestamp < '2018-4-11 18:30:0' AND ((apiproxy in ('test-service' ) ) and (exchangeinstance != '(not set)' ) and (devemail != 'test@xxxxxxxxxxx' ) and (devemail != 'srk@xxxxxxxxxxx' ) and (devemail != 'abc@xxxxxxxxxxx' ) and (devemail != 'xyz@xxxxxxxxxxx' ) and (apistatus = 'Success' ) and (apiaction not in ('LRN','finder','ManuallySelect' ) ) and (appname not in ('Mobile Connect Developer Portal (Int(', 'MinskHBM', 'LondonHBM', 'SeoulHBM', 'MumbaiHBM', 'NVirginiaHBM','SPauloHBM', 'Mobile Connect HeartBeat Monitor', 'PDMAOpenSDKTest1', 'PDMAOpenSDKTest2', 'PDMASDKTest', 'APIHealth', 'A1qaDemoApp','test', 'dublin o2o test tool', 'Test from John do not provision' ) ) and (serorgid = 'aircel' )) GROUP BY serorgid,appname,time_unit ORDER BY time_unit DESC LIMIT 14400 OFFSET 0;
Time: 127713.413 ms
Any help will be highly appreciable. I look forward to hearing from you.
Best Regards,
Kaushal
