On Sun, Apr 29, 2018 at 10:05:23AM +0530, Kaushal Shriyan wrote: > 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?
Send the output of "explain(analyze,buffers)" for the query? Justin
Hi Justin,
Do i need to run the below sql query? Please comment.
explain(analyze,buffers) 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@example.com' ) and (devemail != 'srk@example.com' ) and (devemail != 'abc@example.com' ) and (devemail != 'xyz@example.com' ) 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;
