explain analyze
SELECT t.id as "task_id",
q.user_id as "user_id",
coalesce(sum(q.russian),0) as "russian_count",
coalesce(sum(q.foreign),0) as "foreign_count",
coalesce(sum(q.counter),0) as "total_count",
coalesce(sum(q.russian_duration),0) as "russian_duration",
coalesce(sum(q.foreign_duration),0) as "foreign_duration",
coalesce(sum(q.duration),0) as "total_duration",
coalesce(sum(q.notepad),0) as "notepad_count",
coalesce(sum(q.notepad_pages),0) as "notepad_pages",
coalesce(sum(q.summary),0) as "summary_count",
coalesce(sum(q.summary_pages),0) as "summary_pages"
FROM task t
JOIN (
SELECT tpm.task_id as "task_id",
u.id as "user_id",
sum(CASE WHEN n48p.processed_by_sys_user_id is not null and n48p.processed_by_sys_user_id = u.id THEN 1 ELSE 0 END) as "counter",
sum(CASE WHEN n48p.notepad_created_by_sys_user_id is null or n48p.notepad_created_by_sys_user_id != u.id THEN 0 ELSE 1 END) as "notepad",
sum(CASE WHEN n48p.summary_created_by_sys_user_id is null or n48p.summary_created_by_sys_user_id != u.id THEN 0 ELSE 1 END) as "summary",
sum(CASE WHEN n48p.notepad_created_by_sys_user_id is null or n48p.notepad_created_by_sys_user_id != u.id THEN 0 ELSE n48p.notepad_pages END) as "notepad_pages",
sum(CASE WHEN n48p.summary_created_by_sys_user_id is null or n48p.summary_created_by_sys_user_id != u.id THEN 0 ELSE n48p.summary_pages END) as "summary_pages",
sum(CASE WHEN n48p.processed_by_sys_user_id is not null and n48p.processed_by_sys_user_id = u.id and (n48p.lookup_language_instance_id is null or n48p.lookup_language_instance_id=37) THEN 1 ELSE 0 END) as "russian",
sum(CASE WHEN n48p.processed_by_sys_user_id is not null and n48p.processed_by_sys_user_id = u.id and (n48p.lookup_language_instance_id is not null and n48p.lookup_language_instance_id!=37) THEN 1 ELSE 0 END) as "foreign",
sum(CASE WHEN n48p.processed_by_sys_user_id is not null and n48p.processed_by_sys_user_id = u.id THEN EXTRACT(EPOCH FROM cmi.duration) ELSE 0 END) as "duration",
sum(CASE WHEN n48p.processed_by_sys_user_id is not null and n48p.processed_by_sys_user_id = u.id and (n48p.lookup_language_instance_id is null or n48p.lookup_language_instance_id=37) THEN EXTRACT(EPOCH FROM cmi.duration) ELSE 0 END) as "russian_duration",
sum(CASE WHEN n48p.processed_by_sys_user_id is not null and n48p.processed_by_sys_user_id = u.id and (n48p.lookup_language_instance_id is not null and n48p.lookup_language_instance_id!=37) THEN EXTRACT(EPOCH FROM cmi.duration) ELSE 0 END) as "foreign_duration"
FROM task_process_map tpm
JOIN process_tank_map ptm on ptm.process_id=tpm.process_id
JOIN process pr on pr.id=tpm.process_id
JOIN n48_task n48t on n48t.task_id=tpm.task_id and n48t.status='A'
JOIN v_n48_packet_meta n48p on n48p.n48_task_id=n48t.id
JOIN tank_stream_packet_map tspm on tspm.stream_packet_id=n48p.stream_packet_id and tspm.tank_id=ptm.tank_id and tspm.status='A' and tspm.condition='VISIBLE'
JOIN stream_packet_meta_item_map psmi ON psmi.stream_packet_id = n48p.stream_packet_id AND psmi.meta_item_type = 'call_meta_item'
JOIN call_meta_item cmi ON cmi.meta_item_id = psmi.meta_item_id
LEFT JOIN sys_user u on u.id = n48p.notepad_created_by_sys_user_id or u.id = n48p.summary_created_by_sys_user_id or u.id = n48p.processed_by_sys_user_id
WHERE pr.type='n48_process' and u is not null and u.status='A'
AND tspm.condition='VISIBLE'
GROUP BY tpm.task_id, u.id
) as q on q.task_id=t.id
JOIN sys_user_sys_group_map ugm on ugm.sys_user_id = q.user_id and ugm.status='A'
JOIN sys_group g on g.id=ugm.sys_group_id and g.status='A'
WHERE t.is_private=false AND t.status='A'
AND g.type='SIMPLE'
AND t.id in (12037,12054,19613,110710,118803,8980823,15221109,15363353,15384072,15394611,15398486,15413724,20214099,20371626,20925262,22863147,22870914,22879288,22886660,22899828,22912416,22925004,23824754,46076357,47440226,47890942,166390891,166391968,166555652,166605165,168992530,169073613,169102534,175738876,175774864,195274646,195986282,198500112,210996444,211002625,211012097,211016534,211027299,272229190,307790346,307798443,307799674,307803022,307805958,307807508,307808325,307809491,307812266,307813480,307815336,312650328,312653296,312671907,312684973,312687133,312697219,312703916,312707194,312719806,312719863,312727188,312743257,312747181,312751296,312765359,312770179,312771521,312774952,312778125,312792402,314069445,314206576,314539876,315576713,315886760,334126622,334128018,416649798,417125986,421746339,421800747,421800959,422364647,422370592,422389094,422396517,422397971,422399313,422431571,422439530,422439600,422440308,422440542,423359518,423371352,423371386,423371392,423382990,423398479,496606023,496766182,538566055,538567252,538568492,538571194,538572327,538574863,538575802,538577532,538617261,539372501,539423797,539725935,539731191,550101956,565157121,642139495,652917008,652970167,652970949,652973412,652974001,652993497,653000704,653002326,653073944,653075821,653079352,653079955,653084724,653092686,653093996,653103030,653103173,653105399,653108508,653112870,656576507,656578269,656590017,660911985,663213695,706059307,706064966,733704971,733706831,733707811,733708663,734091160,734092755,734097495,734098610,734099296,734127608,734127863,734130521,734131369,734132677,734133857,734138706,734138910,746067108,746067796,746070883,746438182,762071812)
GROUP BY t.id, q.user_id