pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance issues while running select sql query
7+ messages / 3 participants
[nested] [flat]
* Performance issues while running select sql query
@ 2018-04-29 04:35 Kaushal Shriyan <kaushalshriyan@gmail.com>
0 siblings, 3 replies; 7+ messages in thread
From: Kaushal Shriyan @ 2018-04-29 04:35 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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@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;
*Time: 127713.413 ms*
Any help will be highly appreciable. I look forward to hearing from you.
Best Regards,
Kaushal
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance issues while running select sql query
@ 2018-04-29 04:40 Justin Pryzby <pryzby@telsasoft.com>
parent: Kaushal Shriyan <kaushalshriyan@gmail.com>
2 siblings, 1 reply; 7+ messages in thread
From: Justin Pryzby @ 2018-04-29 04:40 UTC (permalink / raw)
To: Kaushal Shriyan <kaushalshriyan@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance issues while running select sql query
@ 2018-04-29 05:03 Kaushal Shriyan <kaushalshriyan@gmail.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 7+ messages in thread
From: Kaushal Shriyan @ 2018-04-29 05:03 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance@lists.postgresql.org
On Sun, Apr 29, 2018 at 10:10 AM, Justin Pryzby <pryzby@telsasoft.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@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;
I look forward to hearing from you.
Best Regards,
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance issues while running select sql query
@ 2018-04-29 05:18 Kaushal Shriyan <kaushalshriyan@gmail.com>
parent: Kaushal Shriyan <kaushalshriyan@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Kaushal Shriyan @ 2018-04-29 05:18 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance@lists.postgresql.org
On Sun, Apr 29, 2018 at 10:33 AM, Kaushal Shriyan <kaushalshriyan@gmail.com>
wrote:
>
>
> On Sun, Apr 29, 2018 at 10:10 AM, Justin Pryzby <pryzby@telsasoft.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@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;
>
>
> I look forward to hearing from you.
>
> Best Regards,
>
>
Hi Justin,
Please find the below details and let me know if you need any additional
information.
> QUERY PLAN
>
>
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> -------------------------------------------------------------------------------------------------------
> Limit (cost=2568002.26..2568038.26 rows=14400 width=35) (actual
> time=127357.296..127357.543 rows=231 loops=1)
> Buffers: shared hit=28019 read=1954681
> -> Sort (cost=2568002.26..2568389.38 rows=154849 width=35) (actual
> time=127357.294..127357.383 rows=231 loops=1)
> Sort Key: ((date_trunc('day'::text, timezone('+5:30:0'::text,
> "test.prod.fact".client_received_start_timestamp)))::timestamp without time
> zone)
> Sort Method: quicksort Memory: 45kB
> Buffers: shared hit=28019 read=1954681
> -> HashAggregate (cost=2553822.90..2556532.76 rows=154849
> width=35) (actual time=127356.707..127357.103 rows=231 loops=1)
> Group Key: (date_trunc('day'::text,
> timezone('+5:30:0'::text,
> "test.prod.fact".client_received_start_timestamp)))::timestamp without time
> zone, "test.prod.fact".serorgid, "excha
> nge-p.prod.fact".appname
> Buffers: shared hit=28016 read=1954681
> -> Result (cost=0.43..2551252.21 rows=257069 width=35)
> (actual time=2.399..126960.471 rows=311015 loops=1)
> Buffers: shared hit=28016 read=1954681
> -> Append (cost=0.43..2549324.20 rows=257069
> width=35) (actual time=2.294..126163.689 rows=311015 loops=1)
> Buffers: shared hit=28016 read=1954681
> -> Index Scan using
> "exchange-pprodfactclrecsts" on "test.prod.fact" (cost=0.43..6644.45
> rows=64 width=33) (actual time=2.292..3.887 rows=2 loops=1)
> Index Cond:
> ((client_received_start_timestamp >= '2018-03-28 18:30:00'::timestamp
> without time zone) AND (client_received_start_timestamp < '2018-04-11
> 18:30:00'::timestam
> p without time zone))
> Filter: ((exchangeinstance <> '(not
> set)'::text) AND (devemail <> 'test@example.com'::text) AND (devemail <> '
> srk@example.com'::text) AND (devemail <> 'abc@example.com'::
> text) AND (devemail <> 'xyz@example.com'::text) AND (apiproxy =
> 'test-service'::text) AND (apistatus = 'Success'::text) AND (serorgid =
> 'aircel'::text) AND (apiaction <> ALL ('{LRN,Pathfinder
> ,ManuallySelect}'::text[])) AND (appname <> ALL ('{"Mobile Connect
> Developer Portal
> (Int(",MinskHBM,LondonHBM,SeoulHBM,MumbaiHBM,NVirginiaHBM,SPauloHBM,"Mobile
> Connect HeartBeat Monitor",PDMAOpenSDKTest1,
> PDMAOpenSDKTest2,PDMASDKTest,APIHealth,A1qaDemoApp,test,"india e2e test
> tool","Test from John do not provision"}'::text[])))
> Rows Removed by Filter: 61
> Buffers: shared hit=25 read=6
> -> Index Scan using
> "test.prod.fact_624_client_received_start_timestamp_idx" on
> "test.prod.fact_624" (cost=0.42..10948.27 rows=1002 width=34) (actual
> time=3.034..2
> 78.320 rows=1231 loops=1)
> Index Cond:
> ((client_received_start_timestamp >= '2018-03-28 18:30:00'::timestamp
> without time zone) AND (client_received_start_timestamp < '2018-04-11
> 18:30:00'::timestam
> p without time zone))
> Filter: ((exchangeinstance <> '(not
> set)'::text) AND (devemail <> 'test@example.com'::text) AND (devemail <> '
> srk@example.com'::text) AND (devemail <> 'abc@example.com'::
> text) AND (devemail <> 'xyz@example.com'::text) AND (apiproxy =
> 'test-service'::text) AND (apistatus = 'Success'::text) AND (serorgid =
> 'aircel'::text) AND (apiaction <> ALL ('{LRN,Pathfinder
> ,ManuallySelect}'::text[])) AND (appname <> ALL ('{"Mobile Connect
> Developer Portal
> (Int(",MinskHBM,LondonHBM,SeoulHBM,MumbaiHBM,NVirginiaHBM,SPauloHBM,"Mobile
> Connect HeartBeat Monitor",PDMAOpenSDKTest1,
> PDMAOpenSDKTest2,PDMASDKTest,APIHealth,A1qaDemoApp,test,"india e2e test
> tool","Test from John do not provision"}'::text[])))
> Rows Removed by Filter: 42629
> Buffers: shared hit=27966 read=498
> -> Seq Scan on "test.prod.fact_631"
> (cost=0.00..171447.63 rows=16464 width=34) (actual time=0.070..7565.812
> rows=20609 loops=1)
> Filter: ((client_received_start_timestamp
> >= '2018-03-28 18:30:00'::timestamp without time zone) AND
> (client_received_start_timestamp < '2018-04-11 18:30:00'::timestamp wi
> thout time zone) AND (exchangeinstance <> '(not set)'::text) AND (devemail
> <> 'test@example.com'::text) AND (devemail <> 'srk@example.com'::text)
> AND (devemail <> 'abc@example.com'::text) AND (devemail <
> > 'xyz@example.com'::text) AND (apiproxy = 'test-service'::text) AND
> (apistatus = 'Success'::text) AND (serorgid = 'aircel'::text) AND
> (apiaction <> ALL ('{LRN,Pathfinder,ManuallySelect}'::te
> xt[])) AND (appname <> ALL ('{"Mobile Connect Developer Portal
> (Int(",MinskHBM,LondonHBM,SeoulHBM,MumbaiHBM,NVirginiaHBM,SPauloHBM,"Mobile
> Connect HeartBeat Monitor",PDMAOpenSDKTest1,PDMAOpenSDKTest2,PDMA
> SDKTest,APIHealth,A1qaDemoApp,test,"india e2e test tool","Test from John
> do not provision"}'::text[])))
> Rows Removed by Filter: 645406
> Buffers: shared hit=2 read=132279
> -> Seq Scan on "test.prod.fact_640"
> (cost=0.00..147539.09 rows=16739 width=34) (actual time=2.976..7356.452
> rows=20407 loops=1)
> Filter: ((client_received_start_timestamp
> >= '2018-03-28 18:30:00'::timestamp without time zone) AND
> (client_received_start_timestamp < '2018-04-11 18:30:00'::timestamp wi
> thout time zone) AND (exchangeinstance <> '(not set)'::text) AND (devemail
> <> 'test@example.com'::text) AND (devemail <> 'srk@example.com'::text)
> AND (devemail <> 'abc@example.com'::text) AND (devemail <
> > 'xyz@example.com'::text) AND (apiproxy = 'test-service'::text) AND
> (apistatus = 'Success'::text) AND (serorgid = 'aircel'::text) AND
> (apiaction <> ALL ('{LRN,Pathfinder,ManuallySelect}'::te
> xt[])) AND (appname <> ALL ('{"Mobile Connect Developer Portal
> (Int(",MinskHBM,LondonHBM,SeoulHBM,MumbaiHBM,NVirginiaHBM,SPauloHBM,"Mobile
> Connect HeartBeat Monitor",PDMAOpenSDKTest1,PDMAOpenSDKTest2,PDMA
> SDKTest,APIHealth,A1qaDemoApp,test,"india e2e test tool","Test from John
> do not provision"}'::text[])))
> Rows Removed by Filter: 553930
> Buffers: shared hit=2 read=113768
> -> Seq Scan on "test.prod.fact_647"
> (cost=0.00..148973.30 rows=16365 width=34) (actual time=2.274..7433.607
> rows=19296 loops=1)
> Filter: ((client_received_start_timestamp
> >= '2018-03-28 18:30:00'::timestamp without time zone) AND
> (client_received_start_timestamp < '2018-04-11 18:30:00'::timestamp wi
> thout time zone) AND (exchangeinstance <> '(not set)'::text) AND (devemail
> <> 'test@example.com'::text) AND (devemail <> 'srk@example.com'::text)
> AND (devemail <> 'abc@example.com'::text) AND (devemail <
> > 'xyz@example.com'::text) AND (apiproxy = 'test-service'::text) AND
> (apistatus = 'Success'::text) AND (serorgid = 'aircel'::text) AND
> (apiaction <> ALL ('{LRN,Pathfinder,ManuallySelect}'::te
> xt[])) AND (appname <> ALL ('{"Mobile Connect Developer Portal
> (Int(",MinskHBM,LondonHBM,SeoulHBM,MumbaiHBM,NVirginiaHBM,SPauloHBM,"Mobile
> Connect HeartBeat Monitor",PDMAOpenSDKTest1,PDMAOpenSDKTest2,PDMA
> SDKTest,APIHealth,A1qaDemoApp,test,"india e2e test tool","Test from John
> do not provision"}'::text[])))
> Rows Removed by Filter: 560618
> Buffers: shared hit=2 read=114852
> -> Seq Scan on "test.prod.fact_652"
> (cost=0.00..148086.43 rows=14102 width=34) (actual time=2.165..7423.880
> rows=16735 loops=1)
> Filter: ((client_received_start_timestamp
> >= '2018-03-28 18:30:00'::timestamp without time zone) AND
> (client_received_start_timestamp < '2018-04-11 18:30:00'::timestamp wi
> thout time zone) AND (exchangeinstance <> '(not set)'::text) AND (devemail
> <> 'test@example.com'::text) AND (devemail <> 'srk@example.com'::text)
> AND (devemail <> 'abc@example.com'::text) AND (devemail <
> > 'xyz@example.com'::text) AND (apiproxy = 'test-service'::text) AND
> (apistatus = 'Success'::text) AND (serorgid = 'aircel'::text) AND
> (apiaction <> ALL ('{LRN,Pathfinder,ManuallySelect}'::te
> xt[])) AND (appname <> ALL ('{"Mobile Connect Developer Portal
> (Int(",MinskHBM,LondonHBM,SeoulHBM,MumbaiHBM,NVirginiaHBM,SPauloHBM,"Mobile
> Connect HeartBeat Monitor",PDMAOpenSDKTest1,PDMAOpenSDKTest2,PDMA
> SDKTest,APIHealth,A1qaDemoApp,test,"india e2e test tool","Test from John
> do not provision"}'::text[])))
> Rows Removed by Filter: 557229
> Buffers: shared hit=1 read=114353
> -> Seq Scan on "test.prod.fact_661"
> (cost=0.00..172116.37 rows=15973 width=35) (actual time=0.091..8616.119
> rows=17820 loops=1)
> Filter: ((client_received_start_timestamp
> >= '2018-03-28 18:30:00'::timestamp without time zone) AND
> (client_received_start_timestamp < '2018-04-11 18:30:00'::timestamp wi
> thout time zone) AND (exchangeinstance <> '(not set)'::text) AND (devemail
> <> 'test@example.com'::text) AND (devemail <> 'srk@example.com'::text)
> AND (devemail <> 'abc@example.com'::text) AND (devemail <
> > 'xyz@example.com'::text) AND (apiproxy = 'test-service'::text) AND
> (apistatus = 'Success'::text) AND (serorgid = 'aircel'::text) AND
> (apiaction <> ALL ('{LRN,Pathfinder,ManuallySelect}'::te
> xt[])) AND (appname <> ALL ('{"Mobile Connect Developer Portal
> (Int(",MinskHBM,LondonHBM,SeoulHBM,MumbaiHBM,NVirginiaHBM,SPauloHBM,"Mobile
> Connect HeartBeat Monitor",PDMAOpenSDKTest1,PDMAOpenSDKTest2,PDMA
> SDKTest,APIHealth,A1qaDemoApp,test,"india e2e test tool","Test from John
> do not provision"}'::text[])))
> Rows Removed by Filter: 649730
> Buffers: shared hit=2 read=132886
> -> Seq Scan on "test.prod.fact_668"
> (cost=0.00..174813.25 rows=15675 width=35) (actual time=1.537..8751.908
> rows=16881 loops=1)
> Filter: ((client_received_start_timestamp
> >= '2018-03-28 18:30:00'::timestamp without time zone) AND
> (client_received_start_timestamp < '2018-04-11 18:30:00'::timestamp wi
> thout time zone) AND (exchangeinstance <> '(not set)'::text) AND (devemail
> <> 'test@example.com'::text) AND (devemail <> 'srk@example.com'::text)
> AND (devemail <> 'abc@example.com'::text) AND (devemail <
> > 'xyz@example.com'::text) AND (apiproxy = 'test-service'::text) AND
> (apistatus = 'Success'::text) AND (serorgid = 'aircel'::text) AND
> (apiaction <> ALL ('{LRN,Pathfinder,ManuallySelect}'::te
> xt[])) AND (appname <> ALL ('{"Mobile Connect Developer Portal
> (Int(",MinskHBM,LondonHBM,SeoulHBM,MumbaiHBM,NVirginiaHBM,SPauloHBM,"Mobile
> Connect HeartBeat Monitor",PDMAOpenSDKTest1,PDMAOpenSDKTest2,PDMA
> SDKTest,APIHealth,A1qaDemoApp,test,"india e2e test tool","Test from John
> do not provision"}'::text[])))
> Rows Removed by Filter: 661068
> Buffers: shared hit=2 read=134969
> -> Seq Scan on "test.prod.fact_674"
> (cost=0.00..199633.65 rows=22840 width=34) (actual time=0.017..9936.557
> rows=30245 loops=1)
> Filter: ((client_received_start_timestamp
> >= '2018-03-28 18:30:00'::timestamp without time zone) AND
> (client_received_start_timestamp < '2018-04-11 18:30:00'::timestamp wi
> thout time zone) AND (exchangeinstance <> '(not set)'::text) AND (devemail
> <> 'test@example.com'::text) AND (devemail <> 'srk@example.com'::text)
> AND (devemail <> 'abc@example.com'::text) AND (devemail <
> > 'xyz@example.com'::text) AND (apiproxy = 'test-service'::text) AND
> (apistatus = 'Success'::text) AND (serorgid = 'aircel'::text) AND
> (apiaction <> ALL ('{LRN,Pathfinder,ManuallySelect}'::te
> xt[])) AND (appname <> ALL ('{"Mobile Connect Developer Portal
> (Int(",MinskHBM,LondonHBM,SeoulHBM,MumbaiHBM,NVirginiaHBM,SPauloHBM,"Mobile
> Connect HeartBeat Monitor",PDMAOpenSDKTest1,PDMAOpenSDKTest2,PDMA
> SDKTest,APIHealth,A1qaDemoApp,test,"india e2e test tool","Test from John
> do not provision"}'::text[])))
> Rows Removed by Filter: 745118
> Buffers: shared hit=2 read=154045
> -> Seq Scan on "test.prod.fact_682"
> (cost=0.00..253714.68 rows=26677 width=35) (actual time=0.693..12670.194
> rows=33679 loops=1)
> Filter: ((client_received_start_timestamp
> >= '2018-03-28 18:30:00'::timestamp without time zone) AND
> (client_received_start_timestamp < '2018-04-11 18:30:00'::timestamp wi
> thout time zone) AND (exchangeinstance <> '(not set)'::text) AND (devemail
> <> 'test@example.com'::text) AND (devemail <> 'srk@example.com'::text)
> AND (devemail <> 'abc@example.com'::text) AND (devemail <
> > 'xyz@example.com'::text) AND (apiproxy = 'test-service'::text) AND
> (apistatus = 'Success'::text) AND (serorgid = 'aircel'::text) AND
> (apiaction <> ALL ('{LRN,Pathfinder,ManuallySelect}'::te
> xt[])) AND (appname <> ALL ('{"Mobile Connect Developer Portal
> (Int(",MinskHBM,LondonHBM,SeoulHBM,MumbaiHBM,NVirginiaHBM,SPauloHBM,"Mobile
> Connect HeartBeat Monitor",PDMAOpenSDKTest1,PDMAOpenSDKTest2,PDMA
> SDKTest,APIHealth,A1qaDemoApp,test,"india e2e test tool","Test from John
> do not provision"}'::text[])))
> Rows Removed by Filter: 950037
> Buffers: shared hit=2 read=195927
> -> Seq Scan on "test.prod.fact_688"
> (cost=0.00..239629.23 rows=26485 width=33) (actual time=0.627..11931.789
> rows=36929 loops=1)
> Filter: ((client_received_start_timestamp
> >= '2018-03-28 18:30:00'::timestamp without time zone) AND
> (client_received_start_timestamp < '2018-04-11 18:30:00'::timestamp wi
> thout time zone) AND (exchangeinstance <> '(not set)'::text) AND (devemail
> <> 'test@example.com'::text) AND (devemail <> 'srk@example.com'::text)
> AND (devemail <> 'abc@example.com'::text) AND (devemail <
> > 'xyz@example.com'::text) AND (apiproxy = 'test-service'::text) AND
> (apistatus = 'Success'::text) AND (serorgid = 'aircel'::text) AND
> (apiaction <> ALL ('{LRN,Pathfinder,ManuallySelect}'::te
> xt[])) AND (appname <> ALL ('{"Mobile Connect Developer Portal
> (Int(",MinskHBM,LondonHBM,SeoulHBM,MumbaiHBM,NVirginiaHBM,SPauloHBM,"Mobile
> Connect HeartBeat Monitor",PDMAOpenSDKTest1,PDMAOpenSDKTest2,PDMA
> SDKTest,APIHealth,A1qaDemoApp,test,"india e2e test tool","Test from John
> do not provision"}'::text[])))
> Rows Removed by Filter: 893363
> Buffers: shared hit=2 read=184963
> -> Seq Scan on "test.prod.fact_696"
> (cost=0.00..233816.76 rows=25627 width=34) (actual time=0.809..11647.744
> rows=36409 loops=1)
> Filter: ((client_received_start_timestamp
> >= '2018-03-28 18:30:00'::timestamp without time zone) AND
> (client_received_start_timestamp < '2018-04-11 18:30:00'::timestamp wi
> thout time zone) AND (exchangeinstance <> '(not set)'::text) AND (devemail
> <> 'test@example.com'::text) AND (devemail <> 'srk@example.com'::text)
> AND (devemail <> 'abc@example.com'::text) AND (devemail <
> > 'xyz@example.com'::text) AND (apiproxy = 'test-service'::text) AND
> (apistatus = 'Success'::text) AND (serorgid = 'aircel'::text) AND
> (apiaction <> ALL ('{LRN,Pathfinder,ManuallySelect}'::te
> xt[])) AND (appname <> ALL ('{"Mobile Connect Developer Portal
> (Int(",MinskHBM,LondonHBM,SeoulHBM,MumbaiHBM,NVirginiaHBM,SPauloHBM,"Mobile
> Connect HeartBeat Monitor",PDMAOpenSDKTest1,PDMAOpenSDKTest2,PDMA
> SDKTest,APIHealth,A1qaDemoApp,test,"india e2e test tool","Test from John
> do not provision"}'::text[])))
> Rows Removed by Filter: 871346
> Buffers: shared hit=2 read=180422
> -> Seq Scan on "test.prod.fact_701"
> (cost=0.00..177624.27 rows=15959 width=36) (actual time=1.174..8911.760
> rows=16227 loops=1)
> Filter: ((client_received_start_timestamp
> >= '2018-03-28 18:30:00'::timestamp without time zone) AND
> (client_received_start_timestamp < '2018-04-11 18:30:00'::timestamp wi
> thout time zone) AND (exchangeinstance <> '(not set)'::text) AND (devemail
> <> 'test@example.com'::text) AND (devemail <> 'srk@example.com'::text)
> AND (devemail <> 'abc@example.com'::text) AND (devemail <
> > 'xyz@example.com'::text) AND (apiproxy = 'test-service'::text) AND
> (apistatus = 'Success'::text) AND (serorgid = 'aircel'::text) AND
> (apiaction <> ALL ('{LRN,Pathfinder,ManuallySelect}'::te
> xt[])) AND (appname <> ALL ('{"Mobile Connect Developer Portal
> (Int(",MinskHBM,LondonHBM,SeoulHBM,MumbaiHBM,NVirginiaHBM,SPauloHBM,"Mobile
> Connect HeartBeat Monitor",PDMAOpenSDKTest1,PDMAOpenSDKTest2,PDMA
> SDKTest,APIHealth,A1qaDemoApp,test,"india e2e test tool","Test from John
> do not provision"}'::text[])))
> Rows Removed by Filter: 671146
> Buffers: shared hit=2 read=137231
> -> Seq Scan on "test.prod.fact_709"
> (cost=0.00..181100.86 rows=14987 width=36) (actual time=2.614..9080.548
> rows=15270 loops=1)
> Filter: ((client_received_start_timestamp
> >= '2018-03-28 18:30:00'::timestamp without time zone) AND
> (client_received_start_timestamp < '2018-04-11 18:30:00'::timestamp wi
> thout time zone) AND (exchangeinstance <> '(not set)'::text) AND (devemail
> <> 'test@example.com'::text) AND (devemail <> 'srk@example.com'::text)
> AND (devemail <> 'abc@example.com'::text) AND (devemail <
> > 'xyz@example.com'::text) AND (apiproxy = 'test-service'::text) AND
> (apistatus = 'Success'::text) AND (serorgid = 'aircel'::text) AND
> (apiaction <> ALL ('{LRN,Pathfinder,ManuallySelect}'::te
> xt[])) AND (appname <> ALL ('{"Mobile Connect Developer Portal
> (Int(",MinskHBM,LondonHBM,SeoulHBM,MumbaiHBM,NVirginiaHBM,SPauloHBM,"Mobile
> Connect HeartBeat Monitor",PDMAOpenSDKTest1,PDMAOpenSDKTest2,PDMA
> SDKTest,APIHealth,A1qaDemoApp,test,"india e2e test tool","Test from John
> do not provision"}'::text[])))
> Rows Removed by Filter: 686447
> Buffers: shared hit=2 read=139861
> -> Seq Scan on "test.prod.fact_716"
> (cost=0.00..155888.30 rows=13752 width=36) (actual time=2.874..7810.737
> rows=14500 loops=1)
> Filter: ((client_received_start_timestamp
> >= '2018-03-28 18:30:00'::timestamp without time zone) AND
> (client_received_start_timestamp < '2018-04-11 18:30:00'::timestamp wi
> thout time zone) AND (exchangeinstance <> '(not set)'::text) AND (devemail
> <> 'test@example.com'::text) AND (devemail <> 'srk@example.com'::text)
> AND (devemail <> 'abc@example.com'::text) AND (devemail <
> > 'xyz@example.com'::text) AND (apiproxy = 'test-service'::text) AND
> (apistatus = 'Success'::text) AND (serorgid = 'aircel'::text) AND
> (apiaction <> ALL ('{LRN,Pathfinder,ManuallySelect}'::te
> xt[])) AND (appname <> ALL ('{"Mobile Connect Developer Portal
> (Int(",MinskHBM,LondonHBM,SeoulHBM,MumbaiHBM,NVirginiaHBM,SPauloHBM,"Mobile
> Connect HeartBeat Monitor",PDMAOpenSDKTest1,PDMAOpenSDKTest2,PDMA
> SDKTest,APIHealth,A1qaDemoApp,test,"india e2e test tool","Test from John
> do not provision"}'::text[])))
> Rows Removed by Filter: 589910
> Buffers: shared hit=1 read=120362
> -> Seq Scan on "test.prod.fact_723"
> (cost=0.00..127347.65 rows=14358 width=36) (actual time=2.279..6364.821
> rows=14775 loops=1)
> Filter: ((client_received_start_timestamp
> >= '2018-03-28 18:30:00'::timestamp without time zone) AND
> (client_received_start_timestamp < '2018-04-11 18:30:00'::timestamp wi
> thout time zone) AND (exchangeinstance <> '(not set)'::text) AND (devemail
> <> 'test@example.com'::text) AND (devemail <> 'srk@example.com'::text)
> AND (devemail <> 'abc@example.com'::text) AND (devemail <
> > 'xyz@example.com'::text) AND (apiproxy = 'test-service'::text) AND
> (apistatus = 'Success'::text) AND (serorgid = 'aircel'::text) AND
> (apiaction <> ALL ('{LRN,Pathfinder,ManuallySelect}'::te
> xt[])) AND (appname <> ALL ('{"Mobile Connect Developer Portal
> (Int(",MinskHBM,LondonHBM,SeoulHBM,MumbaiHBM,NVirginiaHBM,SPauloHBM,"Mobile
> Connect HeartBeat Monitor",PDMAOpenSDKTest1,PDMAOpenSDKTest2,PDMA
> SDKTest,APIHealth,A1qaDemoApp,test,"india e2e test tool","Test from John
> do not provision"}'::text[])))
> Rows Removed by Filter: 480327
> Buffers: shared hit=1 read=98259
> Planning time: 395.624 ms
> Execution time: 127362.763 ms
> (81 rows)
Thanks in Advance. I look forward to hearing from you.
Best Regards,
^ permalink raw reply [nested|flat] 7+ messages in thread
* Performance issues while running select sql query
@ 2018-04-29 06:10 David G. Johnston <david.g.johnston@gmail.com>
parent: Kaushal Shriyan <kaushalshriyan@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: David G. Johnston @ 2018-04-29 06:10 UTC (permalink / raw)
To: Kaushal Shriyan <kaushalshriyan@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On Saturday, April 28, 2018, Kaushal Shriyan <kaushalshriyan@gmail.com>
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?
>
I would suggest reading the following and providing some additional
details, in particular your table and/or view definitions. Specifically
I'd be looking for indexes on "serorgid", your apparent partitioning setup,
and your use of indexes in general.
https://wiki.postgresql.org/wiki/Slow_Query_Questions
You may also wish to attach the explain output as a text file.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance issues while running select sql query
@ 2018-04-29 14:18 Justin Pryzby <pryzby@telsasoft.com>
parent: Kaushal Shriyan <kaushalshriyan@gmail.com>
2 siblings, 1 reply; 7+ messages in thread
From: Justin Pryzby @ 2018-04-29 14:18 UTC (permalink / raw)
To: Kaushal Shriyan <kaushalshriyan@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Sun, Apr 29, 2018 at 10:05:23AM +0530, Kaushal Shriyan wrote:
> # 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;
This table has inheritence children. Do they have constraints? On what
column? Is constraint_exclusion enabled and working for that?
It looks like test.prod.fact_624 is being read using index in under 1sec, and
the rest using seq scan, taking 5-10sec.
So what are the table+index definitions of the parent and childs (say fact_624
and 631).
Have the child tables been recently ANALYZE ?
Also, have you manually ANALYZE the parent table?
On Sun, Apr 29, 2018 at 10:48:48AM +0530, Kaushal Shriyan wrote:
> > QUERY PLAN
> > Limit (cost=2568002.26..2568038.26 rows=14400 width=35) (actual time=127357.296..127357.543 rows=231 loops=1)
> > Buffers: shared hit=28019 read=1954681
...
> > -> Index Scan using "test.prod.fact_624_client_received_start_timestamp_idx" on "test.prod.fact_624" (cost=0.42..10948.27 rows=1002 width=34) (actual time=3.034..278.320 rows=1231 loops=1)
> > Index Cond: ((client_received_start_timestamp >= '2018-03-28 18:30:00'::timestamp without time zone) AND (client_received_start_timestamp < '2018-04-11 18:30:00'::timestamp without time zone))
> > Rows Removed by Filter: 42629
> > Buffers: shared hit=27966 read=498
> > -> Seq Scan on "test.prod.fact_631" (cost=0.00..171447.63 rows=16464 width=34) (actual time=0.070..7565.812 rows=20609 loops=1)
> > Rows Removed by Filter: 645406
> > Buffers: shared hit=2 read=132279
...
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance issues while running select sql query
@ 2018-04-29 18:10 Kaushal Shriyan <kaushalshriyan@gmail.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 0 replies; 7+ messages in thread
From: Kaushal Shriyan @ 2018-04-29 18:10 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance@lists.postgresql.org
On Sun, Apr 29, 2018 at 7:48 PM, Justin Pryzby <pryzby@telsasoft.com> wrote:
> On Sun, Apr 29, 2018 at 10:05:23AM +0530, Kaushal Shriyan wrote:
> > # 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;
>
> This table has inheritence children. Do they have constraints? On what
> column? Is constraint_exclusion enabled and working for that?
>
> It looks like test.prod.fact_624 is being read using index in under 1sec,
> and
> the rest using seq scan, taking 5-10sec.
>
> So what are the table+index definitions of the parent and childs (say
> fact_624
> and 631).
>
> Have the child tables been recently ANALYZE ?
> Also, have you manually ANALYZE the parent table?
>
Hi Justin,
This table has inheritence children. Do they have constraints? On what
column? Is constraint_exclusion enabled and working for that?
Answer :- Is there a way to find out?
So what are the table+index definitions of the parent and childs (say
fact_624
and 631).
Answer :- Is there a way to find out?
Have the child tables been recently ANALYZE ?
Answer :- I have not done anything and is there a way to find out.
Also, have you manually ANALYZE the parent table?
Answer :- Nope
Any help will be highly appreciable. I look forward to hearing from you.
Best Regards,
Kaushal
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2018-04-29 18:10 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-04-29 04:35 Performance issues while running select sql query Kaushal Shriyan <kaushalshriyan@gmail.com>
2018-04-29 04:40 ` Justin Pryzby <pryzby@telsasoft.com>
2018-04-29 05:03 ` Kaushal Shriyan <kaushalshriyan@gmail.com>
2018-04-29 05:18 ` Kaushal Shriyan <kaushalshriyan@gmail.com>
2018-04-29 06:10 ` David G. Johnston <david.g.johnston@gmail.com>
2018-04-29 14:18 ` Justin Pryzby <pryzby@telsasoft.com>
2018-04-29 18:10 ` Kaushal Shriyan <kaushalshriyan@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox