On Sun, Apr 29, 2018 at 10:10 AM, Justin Pryzby <pryzby@xxxxxxxxxxxxx> wrote:
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@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;
I look forward to hearing from you.
Best Regards,
