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.