SELECT cards.surname, cards.name1, cards.name2, cards.birthday, cards.sex, age
                  (2019-extract(year FROM cards.birthday))::integer AS age,
                  MAX(CASE WHEN ProfileRep.Basic=1 THEN ProfileRep.PrevExam ELSE 0 END) AS PrevExam    
            FROM public.cards
              INNER JOIN disp.profilerep ON cards.profile=ProfileRep.id_profile
            WHERE cards.ctrldate>='20190101'::date AND cards.ctrldate<='20190630'::date
              AND cards.datein>='20190101'::date
              AND cards.form_code IN ('ДВ1', 'ДВ3')
              AND cards.case_isrepeat='0'
              AND ProfileRep.PrevExam>=1 AND ProfileRep.PrevExam<=3 
              AND EXISTS (SELECT 1
                          FROM disp.dispensary_observation
                          INNER JOIN disp.dispensary_observation_list ON dispensary_observation.id_list=dispensary_observation_list.id
                          WHERE dispensary_observation_list.year=2019::integer
                            AND dispensary_observation_list.id_type=2 
                            AND dispensary_observation_list.flag=1    
                            AND dispensary_observation.plan=1        
                            AND dispensary_observation.surname=cards.surname
                            AND dispensary_observation.name=cards.name1
                            AND dispensary_observation.patronymic=cards.name2
                            AND dispensary_observation.birthday=cards.birthday
                            )) cards                                                       
            GROUP BY cards.surname, cards.name1, cards.name2, cards.birthday, cards.sex, ProfileRep.PrevExam   ;

SELECT cards.surname, cards.name1, cards.name2, cards.birthday, cards.sex, age
from (
SELECT cards.surname, cards.name1, cards.name2, cards.birthday, cards.sex,
                  (2019-extract(year FROM cards.birthday))::integer AS age
            FROM public.cards
              INNER JOIN disp.profilerep ON cards.profile=ProfileRep.id_profile
            WHERE cards.ctrldate>='20190101'::date AND cards.ctrldate<='20190630'::date
              AND cards.datein>='20190101'::date
              AND cards.form_code IN ('ДВ1', 'ДВ3')
              AND cards.case_isrepeat='0'
              AND ProfileRep.PrevExam>=1 AND ProfileRep.PrevExam<=3 
              AND EXISTS (SELECT 1
                          FROM disp.dispensary_observation
                          INNER JOIN disp.dispensary_observation_list ON dispensary_observation.id_list=dispensary_observation_list.id
                          WHERE dispensary_observation_list.year=2019::integer
                            AND dispensary_observation_list.id_type=2 
                            AND dispensary_observation_list.flag=1   
                            AND dispensary_observation.plan=1        
                            AND dispensary_observation.surname=cards.surname
                            AND dispensary_observation.name=cards.name1
                            AND dispensary_observation.patronymic=cards.name2
                            AND dispensary_observation.birthday=cards.birthday
                            )) cards                                                       
            GROUP BY cards.surname, cards.name1, cards.name2, cards.birthday, cards.sex, age   ;

) cards 

  Group Key: cards.surname, cards.name1, cards.name2, cards.birthday, cards.sex, cards.age
  Buffers: shared hit=8328260 read=12361218 written=25, temp read=3591 written=3606
  ->  Sort  (cost=1241597.42..1241600.18 rows=1106 width=35) (actual time=52829237.525..52829285.634 rows=66018 loops=1)
        Sort Method: external sort  Disk: 3216kB
        Sort Key: cards.surname, cards.name1, cards.name2, cards.birthday, cards.sex, cards.age
        Buffers: shared hit=8328260 read=12361218 written=25, temp read=3591 written=3606
        ->  Hash Join  (cost=143173.81..1241541.50 rows=1106 width=35) (actual time=15949.404..52827638.163 rows=66018 loops=1)
              Hash Cond: ((cards.profile)::text = (profilerep.id_profile)::text)
              Buffers: shared hit=8328251 read=12361218 written=25, temp read=3189 written=3204
              ->  Nested Loop  (cost=143167.95..1241465.08 rows=15868 width=41) (actual time=15949.092..52827082.119 rows=66018 loops=1)
              ->  Hash  (cost=5.07..5.07 rows=63 width=6) (actual time=0.266..0.266 rows=63 loops=1)
                    Buffers: shared hit=8328249 read=12361218 written=25, temp read=3189 written=3204
                    Buffers: shared hit=2
                    Buckets: 1024  Batches: 1  Memory Usage: 11kB
                    ->  Unique  (cost=143167.38..144878.77 rows=136911 width=30) (actual time=15811.272..24867.416 rows=310351 loops=1)
                    ->  Seq Scan on profilerep  (cost=0.00..5.07 rows=63 width=6) (actual time=0.080..0.208 rows=63 loops=1)
                    ->  Index Scan using cards_surname_name1_name2_idx on cards  (cost=0.56..8.00 rows=1 width=41) (actual time=160.106..170.125 rows=0 loops=310351)
                          Rows Removed by Filter: 64
                          Rows Removed by Filter: 142
                          Index Cond: (((surname)::text = (dispensary_observation.surname)::text) AND ((name1)::text = (dispensary_observation.name)::text) AND ((name2)::text = (dispensary_observation.patronymic)::text))
                          Filter: ((prevexam >= '1'::numeric) AND (prevexam <= '3'::numeric))
                          Filter: ((ctrldate >= '2019-01-01'::date) AND (ctrldate <= '2019-06-30'::date) AND (datein >= '2019-01-01'::date) AND ((form_code)::text = ANY ('{ДВ1,ДВ3}'::text[])) AND ((case_isrepeat)::text = '0'::text) AND (dispensary_observation.birthday = birthday))
                          Buffers: shared hit=8325491 read=12332730 written=25
                          Buffers: shared hit=2758 read=28488, temp read=3189 written=3204
                          Buffers: shared hit=2
                          ->  Sort  (cost=143167.38..143509.66 rows=136911 width=30) (actual time=15811.268..23324.306 rows=396425 loops=1)
                                Sort Method: external merge  Disk: 22160kB
                                Sort Key: ((dispensary_observation.surname)::text), ((dispensary_observation.name)::text), ((dispensary_observation.patronymic)::text), dispensary_observation.birthday
                                Buffers: shared hit=2758 read=28488, temp read=3189 written=3204
                                ->  Nested Loop  (cost=7480.45..128210.90 rows=136911 width=30) (actual time=6727.556..13424.552 rows=396425 loops=1)
                                      Buffers: shared hit=2758 read=28488
                                      ->  Seq Scan on dispensary_observation_list  (cost=0.00..1.18 rows=1 width=4) (actual time=0.023..0.026 rows=1 loops=1)
                                      ->  Bitmap Heap Scan on dispensary_observation  (cost=7480.45..126840.62 rows=136911 width=34) (actual time=6727.523..13238.988 rows=396425 loops=1)
                                            Rows Removed by Filter: 9
                                            Rows Removed by Filter: 6695
                                            Recheck Cond: (id_list = dispensary_observation_list.id)
                                            Heap Blocks: exact=27059
                                            Filter: (plan = '1'::numeric)
                                            Filter: ((year = 2019) AND (id_type = 2) AND (flag = 1))
                                            Buffers: shared hit=2757 read=28488
                                            Buffers: shared hit=1
                                            ->  Bitmap Index Scan on dispensary_observation_id_list_idx  (cost=0.00..7446.22 rows=144772 width=0) (actual time=6713.152..6713.153 rows=419359 loops=1)
                                                  Index Cond: (id_list = dispensary_observation_list.id)
                                                  Buffers: shared hit=1 read=4185

"Group  (cost=1244171.76..1244204.43 rows=1188 width=39) (actual time=53045757.593..53045871.977 rows=49544 loops=1)"
"  Group Key: cards.surname, cards.name1, cards.name2, cards.birthday, cards.sex, cards.age"
"  Buffers: shared hit=8331259 read=12358000, temp read=3591 written=3606"
"  ->  Sort  (cost=1244171.76..1244174.73 rows=1188 width=35) (actual time=53045757.502..53045798.280 rows=66018 loops=1)"
"        Sort Key: cards.surname, cards.name1, cards.name2, cards.birthday, cards.sex, cards.age"
"        Sort Method: external sort  Disk: 3216kB"
"        Buffers: shared hit=8331259 read=12358000, temp read=3591 written=3606"
"        ->  Hash Join  (cost=143214.77..1244111.08 rows=1188 width=35) (actual time=23581.012..53044146.825 rows=66018 loops=1)"
"              Hash Cond: ((cards.profile)::text = (profilerep.id_profile)::text)"
"              Buffers: shared hit=8331250 read=12358000, temp read=3189 written=3204"
"              ->  Nested Loop  (cost=143208.90..1244029.42 rows=17047 width=41) (actual time=23580.758..53043651.431 rows=66018 loops=1)"
"                    Buffers: shared hit=8331249 read=12357999, temp read=3189 written=3204"
"                    ->  Unique  (cost=143208.34..144923.64 rows=137224 width=31) (actual time=23377.507..32066.882 rows=310351 loops=1)"
"                          Buffers: shared hit=2 read=31025, temp read=3189 written=3204"
"                          ->  Sort  (cost=143208.34..143551.40 rows=137224 width=31) (actual time=23377.498..30567.713 rows=396425 loops=1)"
"                                Sort Key: ((dispensary_observation.surname)::text), ((dispensary_observation.name)::text), ((dispensary_observation.patronymic)::text), dispensary_observation.birthday"
"                                Sort Method: external merge  Disk: 22160kB"
"                                Buffers: shared hit=2 read=31025, temp read=3189 written=3204"
"                                ->  Nested Loop  (cost=7479.99..128212.40 rows=137224 width=31) (actual time=4914.229..21202.931 rows=396425 loops=1)"
"                                      Buffers: shared hit=2 read=31025"
"                                      ->  Seq Scan on dispensary_observation_list  (cost=0.00..1.18 rows=1 width=4) (actual time=0.015..0.020 rows=1 loops=1)"
"                                            Filter: ((year = 2019) AND (id_type = 2) AND (flag = 1))"
"                                            Rows Removed by Filter: 9"
"                                            Buffers: shared hit=1"
"                                      ->  Bitmap Heap Scan on dispensary_observation  (cost=7479.99..126838.98 rows=137224 width=35) (actual time=4914.205..21003.741 rows=396425 loops=1)"
"                                            Recheck Cond: (id_list = dispensary_observation_list.id)"
"                                            Filter: (plan = '1'::numeric)"
"                                            Rows Removed by Filter: 6695"
"                                            Heap Blocks: exact=27055"
"                                            Buffers: shared hit=1 read=31025"
"                                            ->  Bitmap Index Scan on dispensary_observation_id_list_idx  (cost=0.00..7445.68 rows=144701 width=0) (actual time=4899.410..4899.410 rows=403120 loops=1)"
"                                                  Index Cond: (id_list = dispensary_observation_list.id)"
"                                                  Buffers: shared hit=1 read=3970"
"                    ->  Index Scan using cards_surname_name1_name2_idx on cards  (cost=0.56..8.00 rows=1 width=41) (actual time=160.242..170.800 rows=0 loops=310351)"
"                          Index Cond: (((surname)::text = (dispensary_observation.surname)::text) AND ((name1)::text = (dispensary_observation.name)::text) AND ((name2)::text = (dispensary_observation.patronymic)::text))"
"                          Filter: ((ctrldate >= '2019-01-01'::date) AND (ctrldate <= '2019-06-30'::date) AND (datein >= '2019-01-01'::date) AND ((form_code)::text = ANY ('{ДВ1,ДВ3}'::text[])) AND ((case_isrepeat)::text = '0'::text) AND (dispensary_observation.birthday = birthday))"
"                          Rows Removed by Filter: 64"
"                          Buffers: shared hit=8331247 read=12326974"
"              ->  Hash  (cost=5.07..5.07 rows=63 width=6) (actual time=0.217..0.217 rows=63 loops=1)"
"                    Buckets: 1024  Batches: 1  Memory Usage: 11kB"
"                    Buffers: shared hit=1 read=1"
"                    ->  Seq Scan on profilerep  (cost=0.00..5.07 rows=63 width=6) (actual time=0.080..0.173 rows=63 loops=1)"
"                          Filter: ((prevexam >= '1'::numeric) AND (prevexam <= '3'::numeric))"
"                          Rows Removed by Filter: 142"
"                          Buffers: shared hit=1 read=1"
"Planning time: 627.084 ms"
"Execution time: 53045901.471 ms"

CREATE TABLE disp.dispensary_observation
(
    id bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    code_mo integer,
    code_mo_p integer,
    id_history integer,
    surname character varying(50) COLLATE pg_catalog."default",
    name character varying(50) COLLATE pg_catalog."default",
    patronymic character varying(50) COLLATE pg_catalog."default",
    birthday date,
    dn_prvs_code smallint,
    dn_period smallint,
    dn_diagnosis character varying(10) COLLATE pg_catalog."default",
    dn_begin date,
    lpuid integer,
    address character varying(254) COLLATE pg_catalog."default",
    sex character varying(5) COLLATE pg_catalog."default",
    rupor_phone character varying(100) COLLATE pg_catalog."default",
    phone character varying(255) COLLATE pg_catalog."default",
    mobile_phone character varying(100) COLLATE pg_catalog."default",
    id_list integer,
    dig_group character varying(20) COLLATE pg_catalog."default",
    policy character varying(20) COLLATE pg_catalog."default",
    time_phone character varying(8) COLLATE pg_catalog."default" DEFAULT 0,
    refusing numeric(1,0) DEFAULT 0,
    ok_phone numeric(1,0) DEFAULT 0,
    ok_letter numeric DEFAULT 0,
    month numeric,
    date_inform timestamp without time zone,
    active numeric DEFAULT 0,
    id_upm integer,
    file character varying(60) COLLATE pg_catalog."default",
    plan numeric DEFAULT 1,
    phone_upm character varying(50) COLLATE pg_catalog."default",
    findex character varying(6) COLLATE pg_catalog."default",
    privilege smallint DEFAULT 0,
    CONSTRAINT dispensary_observation_pk PRIMARY KEY (id),
    CONSTRAINT dispensary_observation_dispensary_observation_list_fk FOREIGN KEY (id_list)
        REFERENCES disp.dispensary_observation_list (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE disp.dispensary_observation
    OWNER to postgres;


CREATE INDEX dispensary_observation_id_history_idx
    ON disp.dispensary_observation USING btree
    (id_history)
    TABLESPACE pg_default;

CREATE INDEX dispensary_observation_id_list_idx
    ON disp.dispensary_observation USING btree
    (id_list)
    TABLESPACE pg_default;

CREATE INDEX dispensary_observation_id_upm_idx
    ON disp.dispensary_observation USING btree
    (id_upm)
    TABLESPACE pg_default;

CREATE INDEX dispensary_observation_mobile_phone_idx
    ON disp.dispensary_observation USING btree
    (mobile_phone COLLATE pg_catalog."default")
    TABLESPACE pg_default;

CREATE INDEX dispensary_observation_rupor_phone_idx
    ON disp.dispensary_observation USING btree
    (rupor_phone COLLATE pg_catalog."default")
    TABLESPACE pg_default;


CREATE INDEX dispensary_observation_surname_idx
    ON disp.dispensary_observation USING btree
    (surname COLLATE pg_catalog."default", name COLLATE pg_catalog."default", patronymic COLLATE pg_catalog."default", birthday)
    TABLESPACE pg_default;

CREATE TABLE public.cards
(
    datetr date,
    numtr character varying(16) COLLATE pg_catalog."default",
    whorefuse character varying(20) COLLATE pg_catalog."default",
    subnumtr numeric(1,0),
    reciever character varying(20) COLLATE pg_catalog."default",
    payer character varying(20) COLLATE pg_catalog."default",
    tmo character varying(20) COLLATE pg_catalog."default",
    surname character varying(40) COLLATE pg_catalog."default",
    name1 character varying(30) COLLATE pg_catalog."default",
    name2 character varying(30) COLLATE pg_catalog."default",
    birthday date,
    age numeric(3,0),
    sex character varying(1) COLLATE pg_catalog."default",
    hsnet character varying(1) COLLATE pg_catalog."default",
    polis_s character varying(20) COLLATE pg_catalog."default",
    polis_n character varying(20) COLLATE pg_catalog."default",
    polis_w character varying(20) COLLATE pg_catalog."default",
    pasp_s character varying(20) COLLATE pg_catalog."default",
    pasp_n character varying(20) COLLATE pg_catalog."default",
    street character varying(40) COLLATE pg_catalog."default",
    streetype character varying(15) COLLATE pg_catalog."default",
    area character varying(3) COLLATE pg_catalog."default",
    house character varying(17) COLLATE pg_catalog."default",
    korp character varying(20) COLLATE pg_catalog."default",
    flat numeric(4,0),
    who character varying(4) COLLATE pg_catalog."default",
    "order" character varying(1) COLLATE pg_catalog."default",
    hsobject character varying(20) COLLATE pg_catalog."default",
    depart character varying(20) COLLATE pg_catalog."default",
    profile character varying(8) COLLATE pg_catalog."default",
    profilenet character varying(1) COLLATE pg_catalog."default",
    proftype character varying(1) COLLATE pg_catalog."default",
    complexity character varying(1) COLLATE pg_catalog."default",
    datein date,
    dateout date,
    amount numeric(5,1),
    outcome character varying(1) COLLATE pg_catalog."default",
    diagnprev character varying(17) COLLATE pg_catalog."default",
    diagnosis character varying(17) COLLATE pg_catalog."default",
    history character varying(20) COLLATE pg_catalog."default",
    remark character varying(2000) COLLATE pg_catalog."default",
    tmonth character varying(2) COLLATE pg_catalog."default",
    pin integer,
    typeins character varying(1) COLLATE pg_catalog."default",
    sum numeric(11,2),
    sum_n numeric(11,2),
    reftype character varying(3) COLLATE pg_catalog."default",
    cardflags numeric(4,0),
    gosptype character varying(1) COLLATE pg_catalog."default",
    card_id character varying(8) COLLATE pg_catalog."default",
    acntdate date,
    ctrldate date,
    id_account character varying(20) COLLATE pg_catalog."default",
    id_patient character varying(20) COLLATE pg_catalog."default",
    id_case character varying(20) COLLATE pg_catalog."default",
    id_service character varying(20) COLLATE pg_catalog."default",
    id_srv_pnt character varying(20) COLLATE pg_catalog."default",
    id_refuse numeric(10,0),
    ref_act character varying(16) COLLATE pg_catalog."default",
    act_date date,
    id_ref_obj numeric(10,0),
    obj_type numeric(2,0),
    ref_date date,
    case_payer character varying(20) COLLATE pg_catalog."default",
    is_work numeric(1,0),
    numpay character varying(17) COLLATE pg_catalog."default",
    datepay date,
    address character varying(200) COLLATE pg_catalog."default",
    okato character varying(3) COLLATE pg_catalog."default",
    c_datein date,
    vis_count numeric(10,0),
    id_prvs numeric(10,0),
    ksg_type numeric(1,0),
    id_pat_cat numeric(2,0),
    polis_beg date,
    polis_end date,
    oksm character varying(3) COLLATE pg_catalog."default",
    id_smo_r numeric(5,0),
    street_r character varying(150) COLLATE pg_catalog."default",
    street_typ numeric(2,0),
    kladr character varying(13) COLLATE pg_catalog."default",
    cod_z integer,
    case_vmp numeric(4,0),
    case_ump numeric(4,0),
    case_is_crim integer,
    serv_card_type character varying(5) COLLATE pg_catalog."default",
    vid_vme integer,
    case_form_mp integer,
    case_vid_hmp character varying(50) COLLATE pg_catalog."default",
    case_method_hmp integer,
    case_disp_type character varying(50) COLLATE pg_catalog."default",
    case_disp_result integer,
    case_cast integer,
    case_prvs_direct character varying(50) COLLATE pg_catalog."default",
    case_goal_direct character varying(50) COLLATE pg_catalog."default",
    serv_count numeric(15,2),
    serv_amount integer,
    pat_vnov_d character varying(50) COLLATE pg_catalog."default",
    serv_goal character varying(50) COLLATE pg_catalog."default",
    case_goal character varying(50) COLLATE pg_catalog."default",
    id_forming integer,
    form_code character varying(50) COLLATE pg_catalog."default",
    case_lpu_direct character varying(50) COLLATE pg_catalog."default",
    case_lpu_direct_name character varying(250) COLLATE pg_catalog."default",
    case_qresult79 integer,
    case_isrepeat character varying(50) COLLATE pg_catalog."default",
    id_lpu_out character varying(50) COLLATE pg_catalog."default",
    id_lpu_in character varying(50) COLLATE pg_catalog."default",
    serv_has_refuse character varying(50) COLLATE pg_catalog."default",
    serv_tariff character varying(50) COLLATE pg_catalog."default",
    plat character varying(50) COLLATE pg_catalog."default",
    code_mo character varying(50) COLLATE pg_catalog."default",
    smo_nreestr character varying(50) COLLATE pg_catalog."default",
    serv_isrepeat character varying(50) COLLATE pg_catalog."default",
    serv_id_doctor character varying(50) COLLATE pg_catalog."default",
    serv_lpu_dept character varying(50) COLLATE pg_catalog."default",
    serv_lpu_podr_name character varying(50) COLLATE pg_catalog."default",
    serv_lpu_podr character varying(50) COLLATE pg_catalog."default",
    serv_is_main character varying(50) COLLATE pg_catalog."default",
    serv_prvs79 character varying(50) COLLATE pg_catalog."default",
    serv_idprmp character varying(50) COLLATE pg_catalog."default",
    case_dateend character varying(50) COLLATE pg_catalog."default",
    serv_ischild character varying(50) COLLATE pg_catalog."default",
    case_fin_type character varying(50) COLLATE pg_catalog."default",
    case_id_doctor character varying(50) COLLATE pg_catalog."default",
    case_lpu_dept_name character varying(50) COLLATE pg_catalog."default",
    case_lpu_dept character varying(50) COLLATE pg_catalog."default",
    case_lpu_podr_name character varying(50) COLLATE pg_catalog."default",
    case_lpu_podr character varying(50) COLLATE pg_catalog."default",
    case_ischild character varying(50) COLLATE pg_catalog."default",
    case_sppay character varying(50) COLLATE pg_catalog."default",
    case_prmp79_name character varying(50) COLLATE pg_catalog."default",
    case_prmp79 character varying(50) COLLATE pg_catalog."default",
    case_dss character varying(50) COLLATE pg_catalog."default",
    case_dsmain character varying(50) COLLATE pg_catalog."default",
    case_dsp character varying(50) COLLATE pg_catalog."default",
    case_exitus79 character varying(50) COLLATE pg_catalog."default",
    pat_remark character varying(1000) COLLATE pg_catalog."default",
    case_prvs79 character varying(250) COLLATE pg_catalog."default",
    pat_lgot_cat_list character varying(10) COLLATE pg_catalog."default",
    case_inv smallint,
    case_p_per smallint,
    srv_npl smallint,
    talon_d date,
    talon_p date,
    talon_n character varying(30) COLLATE pg_catalog."default",
    case_vbr smallint,
    case_p_otk smallint,
    ds1_pr smallint,
    pr_d_n smallint,
    ds2_n character varying(50) COLLATE pg_catalog."default",
    id integer NOT NULL DEFAULT nextval('cards_id_seq'::regclass),
    vid character varying(1) COLLATE pg_catalog."default",
    npr_date date,
    kd integer,
    vb_p integer,
    id_bed_profile integer,
    profil_k integer,
    case_id_p_cel integer,
    serv_id_p_cel integer,
    reab integer,
    case_p_cel numeric(15,2),
    serv_p_cel numeric(15,2),
    ds_onk integer,
    isonk_sl integer,
    case_order79 smallint
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

CREATE INDEX c_id_account_idx
    ON public.cards USING btree
    (id_account COLLATE pg_catalog."default")
    TABLESPACE pg_default;


CREATE INDEX cards_acntdate_idx
    ON public.cards USING btree
    (acntdate)
    TABLESPACE pg_default;

CREATE INDEX cards_birthday_idx
    ON public.cards USING btree
    (birthday)
    TABLESPACE pg_default;

CREATE INDEX cards_ctrldate_idx
    ON public.cards USING btree
    (ctrldate)
    TABLESPACE pg_default;

CREATE INDEX cards_id_case_idx
    ON public.cards USING btree
    (id_case COLLATE pg_catalog."default")
    TABLESPACE pg_default;

CREATE INDEX cards_id_idx
    ON public.cards USING btree
    (id)
    TABLESPACE pg_default;

CREATE INDEX cards_id_service2_idx
    ON public.cards USING btree
    (id_service COLLATE pg_catalog."default")
    TABLESPACE pg_default;


CREATE INDEX cards_surname_name1_name2_idx
    ON public.cards USING btree
    (surname COLLATE pg_catalog."default", name1 COLLATE pg_catalog."default", name2 COLLATE pg_catalog."default")
    TABLESPACE pg_default;

Index Scan using cards_surname_name1_name2_idx on cards 

Group  (cost=1252404.38..1252436.17 rows=1156 width=39) (actual time=2351579.317..2351700.645 rows=49544 loops=1)
  Group Key: cards.surname, cards.name1, cards.name2, cards.birthday, cards.sex, cards.age
  Buffers: shared hit=1129769 read=2400772, temp read=3591 written=3606
  ->  Sort  (cost=1252404.38..1252407.27 rows=1156 width=35) (actual time=2351568.373..2351597.280 rows=66018 loops=1)
        Sort Key: cards.surname, cards.name1, cards.name2, cards.birthday, cards.sex, cards.age
        Sort Method: external sort  Disk: 3216kB
        Buffers: shared hit=1129769 read=2400772, temp read=3591 written=3606
        ->  Hash Join  (cost=144175.71..1252345.57 rows=1156 width=35) (actual time=6768.502..2350236.548 rows=66018 loops=1)
              Hash Cond: ((cards.profile)::text = (profilerep.id_profile)::text)
              Buffers: shared hit=1129769 read=2400772, temp read=3189 written=3204
              ->  Nested Loop  (cost=144169.85..1252265.94 rows=16588 width=41) (actual time=6768.251..2349797.582 rows=66018 loops=1)
                    Buffers: shared hit=1129768 read=2400771, temp read=3189 written=3204
                    ->  Unique  (cost=144169.29..145892.22 rows=137835 width=31) (actual time=6745.948..11156.044 rows=310351 loops=1)
                          Buffers: shared hit=2 read=31025, temp read=3189 written=3204
                          ->  Sort  (cost=144169.29..144513.87 rows=137835 width=31) (actual time=6745.920..10302.139 rows=396425 loops=1)
                                Sort Key: ((dispensary_observation.surname)::text), ((dispensary_observation.name)::text), ((dispensary_observation.patronymic)::text), dispensary_observation.birthday
                                Sort Method: external merge  Disk: 22160kB
                                Buffers: shared hit=2 read=31025, temp read=3189 written=3204
                                ->  Nested Loop  (cost=8316.43..129102.79 rows=137835 width=31) (actual time=4907.659..5545.996 rows=396425 loops=1)
                                      Buffers: shared hit=2 read=31025
                                      ->  Seq Scan on dispensary_observation_list  (cost=0.00..1.18 rows=1 width=4) (actual time=0.021..0.025 rows=1 loops=1)
                                            Filter: ((year = 2019) AND (id_type = 2) AND (flag = 1))
                                            Rows Removed by Filter: 9
                                            Buffers: shared hit=1
                                      ->  Bitmap Heap Scan on dispensary_observation  (cost=8316.43..127570.11 rows=153150 width=35) (actual time=4907.627..5452.442 rows=396425 loops=1)
                                            Recheck Cond: (id_list = dispensary_observation_list.id)
                                            Filter: (plan = '1'::numeric)
                                            Rows Removed by Filter: 6695
                                            Heap Blocks: exact=27055
                                            Buffers: shared hit=1 read=31025
                                            ->  Bitmap Index Scan on dispensary_observation_id_list_idx  (cost=0.00..8278.15 rows=161296 width=0) (actual time=4895.196..4895.196 rows=403120 loops=1)
                                                  Index Cond: (id_list = dispensary_observation_list.id)
                                                  Buffers: shared read=3971
                    ->  Index Scan using cards_surname_name1_name2_idx on cards  (cost=0.56..8.02 rows=1 width=41) (actual time=6.083..7.529 rows=0 loops=310351)
                          Index Cond: (((surname)::text = (dispensary_observation.surname)::text) AND ((name1)::text = (dispensary_observation.name)::text) AND ((name2)::text = (dispensary_observation.patronymic)::text) AND (birthday = dispensary_observation.birthday) AND (ctrldate >= '2019-01-01'::date) AND (ctrldate <= '2019-06-30'::date))
                          Filter: ((datein >= '2019-01-01'::date) AND ((form_code)::text = ANY ('{ДВ1,ДВ3}'::text[])) AND ((case_isrepeat)::text = '0'::text))
                          Rows Removed by Filter: 7
                          Buffers: shared hit=1129766 read=2369746
              ->  Hash  (cost=5.07..5.07 rows=63 width=6) (actual time=0.224..0.224 rows=93 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 12kB
                    Buffers: shared hit=1 read=1
                    ->  Seq Scan on profilerep  (cost=0.00..5.07 rows=63 width=6) (actual time=0.063..0.170 rows=93 loops=1)
                          Filter: ((prevexam >= '1'::numeric) AND (prevexam <= '3'::numeric))
                          Rows Removed by Filter: 158
                          Buffers: shared hit=1 read=1
Planning time: 93.237 ms
Execution time: 2351724.840 ms

Group  (cost=3482324.04..3485499.76 rows=28043 width=31) (actual time=126379.406..126762.179 rows=77798 loops=1)
  Group Key: surname, name1, name2, birthday, sex
  Buffers: shared hit=1 read=272623
  ->  Gather Merge  (cost=3482324.04..3485206.63 rows=23450 width=31) (actual time=126379.402..126677.743 rows=92832 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        Buffers: shared hit=37 read=763459
        ->  Group  (cost=3481324.02..3481499.89 rows=11725 width=31) (actual time=126347.127..126396.247 rows=30944 loops=3)
              Group Key: surname, name1, name2, birthday, sex
              Buffers: shared hit=37 read=763459
              ->  Sort  (cost=3481324.02..3481353.33 rows=11725 width=31) (actual time=126347.118..126361.606 rows=34630 loops=3)
                    Sort Key: surname, name1, name2, birthday, sex
                    Sort Method: quicksort  Memory: 3680kB
                    Buffers: shared hit=37 read=763459
                    ->  Parallel Bitmap Heap Scan on cards  (cost=159933.39..3480531.57 rows=11725 width=31) (actual time=18710.704..126014.729 rows=34630 loops=3)
                          Recheck Cond: ((ctrldate >= '2019-01-01'::date) AND (ctrldate <= '2019-06-30'::date))
                          Rows Removed by Index Recheck: 119541
                          Filter: ((datein >= '2019-01-01'::date) AND ((form_code)::text = ANY ('{ДВ1,ДВ3}'::text[])) AND ((case_isrepeat)::text = '0'::text))
                          Rows Removed by Filter: 2715421
                          Heap Blocks: exact=13120 lossy=231152
                          Buffers: shared hit=7 read=763459
                          ->  Bitmap Index Scan on cards_ctrldate_idx  (cost=0.00..159926.36 rows=7399379 width=0) (actual time=17748.707..17748.707 rows=8250155 loops=1)
                                Index Cond: ((ctrldate >= '2019-01-01'::date) AND (ctrldate <= '2019-06-30'::date))
                                Buffers: shared hit=1 read=28351
Planning time: 203.058 ms
Execution time: 126780.224 ms

select * from pg_settings where name = 'shared_preload_libraries';

Group  (cost=3561086.87..3561232.60 rows=1156 width=39) (actual time=32402.488..32529.899 rows=49544 loops=1)
  Group Key: cards.surname, cards.name1, cards.name2, cards.birthday, cards.sex, cards.age
  Buffers: shared hit=111242 read=288972
  I/O Timings: read=20523.304
  ->  Gather Merge  (cost=3561086.87..3561206.58 rows=964 width=35) (actual time=32402.479..32601.142 rows=58911 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        Buffers: shared hit=333878 read=763459
        I/O Timings: read=32541.138
        ->  Group  (cost=3560086.85..3560095.29 rows=482 width=35) (actual time=32365.943..32381.718 rows=19637 loops=3)
              Group Key: cards.surname, cards.name1, cards.name2, cards.birthday, cards.sex, cards.age
              Buffers: shared hit=333878 read=763459
              I/O Timings: read=32541.138
              ->  Sort  (cost=3560086.85..3560088.06 rows=482 width=35) (actual time=32365.938..32369.984 rows=22006 loops=3)
                    Sort Key: cards.surname, cards.name1, cards.name2, cards.birthday, cards.sex, cards.age
                    Sort Method: quicksort  Memory: 2602kB
                    Buffers: shared hit=333878 read=763459
                    I/O Timings: read=32541.138
                    ->  Hash Semi Join  (cost=239228.38..3560065.37 rows=482 width=35) (actual time=19616.814..32267.632 rows=22006 loops=3)
                          Hash Cond: (((cards.surname)::text = (dispensary_observation.surname)::text) AND ((cards.name1)::text = (dispensary_observation.name)::text) AND ((cards.name2)::text = (dispensary_observation.patronymic)::text) AND (cards.birthday = dispensary_observation.birthday))
                          Buffers: shared hit=333834 read=763459
                          I/O Timings: read=32541.138
                          ->  Hash Join  (cost=101935.15..3422585.47 rows=817 width=35) (actual time=18063.736..30636.522 rows=34630 loops=3)
                                Hash Cond: ((cards.profile)::text = (profilerep.id_profile)::text)
                                Buffers: shared hit=67 read=763459
                                I/O Timings: read=32541.138
                                ->  Parallel Bitmap Heap Scan on cards  (cost=101929.29..3422527.47 rows=11725 width=41) (actual time=18063.401..30577.108 rows=34630 loops=3)
                                      Recheck Cond: ((ctrldate >= '2019-01-01'::date) AND (ctrldate <= '2019-06-30'::date))
                                      Filter: ((datein >= '2019-01-01'::date) AND ((form_code)::text = ANY ('{ДВ1,ДВ3}'::text[])) AND ((case_isrepeat)::text = '0'::text))
                                      Rows Removed by Filter: 2715421
                                      Heap Blocks: exact=260621
                                      Buffers: shared hit=7 read=763459
                                      I/O Timings: read=32541.138
                                      ->  Bitmap Index Scan on cards_ctrldate_idx  (cost=0.00..101922.26 rows=7399379 width=0) (actual time=16952.215..16952.215 rows=8250155 loops=1)
                                            Index Cond: ((ctrldate >= '2019-01-01'::date) AND (ctrldate <= '2019-06-30'::date))
                                            Buffers: shared hit=1 read=28351
                                            I/O Timings: read=15130.423
                                ->  Hash  (cost=5.07..5.07 rows=63 width=6) (actual time=0.228..0.228 rows=93 loops=3)
                                      Buckets: 1024  Batches: 1  Memory Usage: 12kB
                                      Buffers: shared hit=6
                                      ->  Seq Scan on profilerep  (cost=0.00..5.07 rows=63 width=6) (actual time=0.071..0.174 rows=93 loops=3)
                                            Filter: ((prevexam >= '1'::numeric) AND (prevexam <= '3'::numeric))
                                            Rows Removed by Filter: 158
                                            Buffers: shared hit=6
                          ->  Hash  (cost=134536.52..134536.52 rows=137835 width=31) (actual time=1551.910..1551.910 rows=310354 loops=3)
                                Buckets: 524288 (originally 262144)  Batches: 1 (originally 1)  Memory Usage: 23720kB
                                Buffers: shared hit=333745
                                ->  Hash Join  (cost=1.19..134536.52 rows=137835 width=31) (actual time=0.156..1316.028 rows=396425 loops=3)
                                      Hash Cond: (dispensary_observation.id_list = dispensary_observation_list.id)
                                      Buffers: shared hit=333745
                                      ->  Seq Scan on dispensary_observation  (cost=0.00..129383.75 rows=1378351 width=35) (actual time=0.033..1025.642 rows=1366060 loops=3)
                                            Filter: (plan = '1'::numeric)
                                            Rows Removed by Filter: 75610
                                            Buffers: shared hit=333714
                                      ->  Hash  (cost=1.18..1.18 rows=1 width=4) (actual time=0.020..0.020 rows=1 loops=3)
                                            Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                            Buffers: shared hit=3
                                            ->  Seq Scan on dispensary_observation_list  (cost=0.00..1.18 rows=1 width=4) (actual time=0.015..0.016 rows=1 loops=3)
                                                  Filter: ((year = 2019) AND (id_type = 2) AND (flag = 1))
                                                  Rows Removed by Filter: 9
                                                  Buffers: shared hit=3
Planning time: 140.122 ms
Execution time: 32647.494 ms