Re: Performance issues while running select sql query
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
> != '[email protected]' ) and (devemail != '[email protected]' ) and
> (devemail != '[email protected]' ) and (devemail != '[email protected]' ) 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
...
Re: Performance issues while running select sql query
On Sun, Apr 29, 2018 at 7:48 PM, Justin Pryzby 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
> > != '[email protected]' ) and (devemail != '[email protected]' ) and
> > (devemail != '[email protected]' ) and (devemail != '[email protected]' )
> 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
