2018-09-12 10:27:33 MSK [47704]: [21-1] db=lsd,appname=192.168.5.13,user=rep_slave,client=192.168.5.14 LOG:  duration: 146012.051 ms  execute <unnamed>: with prm(cln,d1,d2) as (select $1::integer,to_date($2,'dd.mm.yyyy'), to_date($3,'dd.mm.yyyy'), $4::text include_mdk)
        --with prm(cln,d1,d2) as (select 89::integer,to_date('01.03.2017','dd.mm.yyyy'), to_date('31.03.2017','dd.mm.yyyy'), 'true'::text include_mdk)

        , goal as (select id, e_code
        from mc_case_init_goal
        join prm on true
        where e_code in ('9','13') or (e_code in ('30','4') and prm.include_mdk='true')
        )

        , dset2 as (
        select mc.id,
               mc.patient_id,
               mc.open_date,
               mc.soc_group_id,
               mc.clinic_id,
               goal.e_code as goal,
               i.nationality_id
          from prm
          join goal on true
          join mc_case mc on mc.clinic_id = cln and mc.close_date between d1 and d2 and mc.closing_step_id is not null
           and mc.init_goal_id = goal.id
          join pim_individual I on mc.patient_id = I.id)

        , t3 as (
        select patient_id,
               sG.code in('11','12','13','14','34') g7001,
               sG.code in('22','24','25','26','27','29') or sg.id is null g7002,
               sG.code in('23','32') g7003,
                (select bool_and(not ae.is_city) -- то есть все действующие адреса - сельские
                   from pim_party_address A
                   join pim_party_addr_to_addr_type P on P.party_address_id = A.id and A.party_id = patient_id
                    and A.is_valid and A.addr_id is not null
                   join pim_address_type T on P.address_type_id = T.id and T.code = 'REGISTER'
                   join address_element_data ae on ae.id = a.addr_id) g7012,
               mnds.code='Д1.02' as fst_srv,
               ssr.id as srv_id,
               agree.denial,
               n.code::integer in (63,132,140,251,250) or n.code::integer between 80 and 112 or n.code::integer between 114 and 122 as g7005
          from dset2
          join disp.md_event_patient mep on coalesce(mep.case_id,mep.case_id_2stage) = dset2.id and not (mep.is_deleted is true)
          left join md_soc_group sG on dset2.soc_group_id = sG.id
          left join pim_nationality n on n.id = nationality_id
          left join disp.md_event_service_patient mesp on mesp.event_patient_id = mep.id
          left join disp.md_event_service mes on mes.id = mesp.service_id
          left join disp.sr_srv_service_document sssd on sssd.service_id = mes.service_id and (sssd.owner_org_id = dset2.clinic_id or sssd.owner_org_id is null)
          left join md_norm_document_service mnds on mnds.id = sssd.document_service_id
          left join md_norm_document mnd on mnd.id = sssd.document_id and mnd.code in('36ан','1006н')
          left join sr_srv_rendered ssr on ssr.md_case_id = dset2.id
          left join disp.md_event_service_patient_agreement agree on agree.service_id = ssr.id)

        , table1 as (
        select count(distinct case when g7001 then patient_id end) as g7001,
               count(distinct case when g7002 then patient_id end) as g7002,
               count(distinct case when g7003 then patient_id end) as g7003,
               count(distinct case when g7005 then patient_id end) as g7005,
               count(distinct case when g7012 then patient_id end) as g7012,
               count(distinct case when denial then srv_id end) as g7009,
               count(distinct case when denial and fst_srv then srv_id end) as g7010
          from t3)

        , t4 as (
        select count(distinct case when b.benefit_type_id=14 then dset2.patient_id end) as g7004_1,
               count(distinct case when b.benefit_type_id=15 then dset2.patient_id end) as g7004_2,
               count(distinct case when b.benefit_type_id=6 then dset2.patient_id end) as g7004_3,
               count(distinct case when b.benefit_type_id=7 then dset2.patient_id end) as g7004_4,
               count(distinct case when b.benefit_type_id=8
                     and exists(select null
                                  from pci_benefit b1
                                 where b1.patient_id = dset2.patient_id and b1.benefit_type_id in(4,19,20,21,23,11,14)
                                   and open_date between coalesce(b1.from_dt,open_date) and coalesce(b1.to_dt,open_date))
                              then dset2.patient_id end) as g7004_5,
               count(distinct case when b.benefit_type_id=9 then dset2.patient_id end) as g7004_6,
               count(distinct case when b.benefit_type_id=10 then dset2.patient_id end) as g7004_7,
               count(distinct case when b.benefit_type_id in(4,11,14,19,20,21,23) then dset2.patient_id end) as g7004_8
          from dset2
          join pci_benefit b on b.patient_id = dset2.patient_id
           and open_date between coalesce(b.from_dt,open_date) and coalesce(b.to_dt,open_date))

         , t5 as (
         select count(distinct case when me.is_mobile_medical_teams then patient_id end) as g7008,
                max(me.count_mmt) as g7007,
                count(distinct sssd.owner_org_id) as g7006
           from dset2
           join disp.md_event_patient mep on coalesce(mep.case_id,mep.case_id_2stage) = dset2.id
           join disp.md_event me on me.id = mep.event_id
           join disp.md_event_service_patient mesp on mesp.event_patient_id = mep.id
           join disp.md_event_service mes on mes.id = mesp.service_id
           join disp.sr_srv_service_document sssd on sssd.service_id = mes.service_id
           join md_norm_document_service mnds on mnds.id = sssd.document_service_id
           join md_norm_document mnd on mnd.id = sssd.document_id and mnd.code in('36ан','1006н'))

        , t6 as (
        select count(distinct dset2.patient_id) as g7011
          from dset2
          join sr_srv_rendered msr on msr.md_case_id = dset2.id and goal='9'
          join md_prescription mp on mp.service_id = msr.id
          join disp.sr_srv_service_document sssd on sssd.service_id = mp.service_type_id and (sssd.owner_org_id = dset2.clinic_id or sssd.owner_org_id is null)
          join md_norm_document_service mnds on mnds.id = sssd.document_service_id
          join md_norm_document mnd on mnd.id = sssd.document_id and mnd.code in('1006н_2','36ан_2')
          left join sr_srv_rendered ssr on ssr.md_patient_prescription_id = mp.id
         where ssr.bdate is not null or ssr.id is null)

        , ttt as (
        select * from table1 full join t4 on true full join t5 on true full join t6 on true)

        select  case when g7001<>0 then g7001 end g7001,
                case when g7002<>0 then g7002 end g7002,
                case when g7003<>0 then g7003 end g7003,
                case when g7004_1<>0 then g7004_1 end g7004_1,
                case when g7004_2<>0 then g7004_2 end g7004_2,
                case when g7004_3<>0 then g7004_3 end g7004_3,
                case when g7004_4<>0 then g7004_4 end g7004_4,
                case when g7004_5<>0 then g7004_5 end g7004_5,
                case when g7004_6<>0 then g7004_6 end g7004_6,
                case when g7004_7<>0 then g7004_7 end g7004_7,
                case when g7004_8<>0 then g7004_8 end g7004_8,
                case when g7005<>0 then g7005 end g7005,
                case when g7006<>0 then g7006 end g7006,
                case when g7007<>0 then g7007 end g7007,
                case when g7009<>0 then g7009 end g7008,
                case when g7009<>0 then g7009 end g7009,
                case when g7010<>0 then g7010 end g7010,
                case when g7011<>0 then g7011 end g7011,
                case when g7012<>0 then g7012 end g7012
        from ttt
2018-09-12 10:27:33 MSK [47704]: [22-1] db=lsd,appname=192.168.5.13,user=rep_slave,client=192.168.5.14 DETAIL:  parameters: $1 = '63', $2 = '01.01.2018 00:00', $3 = '12.09.2018 00:00', $4 = 'false'
2018-09-12 10:27:33 MSK [47704]: [23-1] db=lsd,appname=192.168.5.13,user=rep_slave,client=192.168.5.14 LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp47704.4", size 190644224
2018-09-12 10:27:33 MSK [47704]: [24-1] db=lsd,appname=192.168.5.13,user=rep_slave,client=192.168.5.14 LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp47704.2", size 1073741824
2018-09-12 10:27:33 MSK [47704]: [25-1] db=lsd,appname=192.168.5.13,user=rep_slave,client=192.168.5.14 LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp47704.3", size 266346496
2018-09-12 10:27:33 MSK [47704]: [26-1] db=lsd,appname=192.168.5.13,user=rep_slave,client=192.168.5.14 LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp47704.5", size 121233408