Re: Performance issues while running select sql query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Sun, Apr 29, 2018 at 7:48 PM, Justin Pryzby <pryzby@xxxxxxxxxxxxx> 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@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;

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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux