Re: Performance issues while running select sql query

On Sun, Apr 29, 2018 at 10:10 AM, Justin Pryzby <pryzby(at)telsasoft(dot)com>
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(at)example(dot)com' ) and (devemail != '
> srk(at)example(dot)com' ) and (devemail != 'abc(at)example(dot)com' ) and (devemail
> != 'xyz(at)example(dot)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;

I look forward to hearing from you.

Best Regards,