explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 23HH

Settings
# exclusive inclusive rows x rows loops node
1. 323.162 42,850.836 ↓ 37.9 212,485 1

Unique (cost=5,017,371.37..5,018,562.43 rows=5,605 width=2,824) (actual time=42,227.450..42,850.836 rows=212,485 loops=1)

  • Output: (((d.d39 + '1 day'::interval))::date), ((to_char((d.d39 + '1 day'::interval), 'DD.MM.YYYY'::text))::character varying(10)), d.d1, d.d2, ((to_char((d.d2)::timestamp with time zone, 'DD.MM.YYYY'::text))::character varying(10)), (COALESCE(ld.sk_loan_standard_id, '0'::bigint)), (COALESCE(ld.standard_name, 'Не определено'::text)), d.d8, d.d22, (COALESCE(d.d9, '1900-01-01'::date)), d.d18, (COALESCE(d.d17, "substring"(d.d18, 1, 7))), ("substring"(d.d18, 3, 1)), ("substring"(d.d18, 5, 3)), ("substring"(d.d18, 9, 17)), ((to_char((d.d19)::timestamp with time zone, 'DD.MM.YYYY'::text))::character varying(10)), ((to_char((d.d34)::timestamp with time zone, 'dd.mm.yyyy'::text))::character varying(10)), (((btrim(d.d35))::numeric)::integer), (CASE WHEN (d.d55 ~~ '%ДОМ.РФ%'::text) THEN 2 ELSE 0 END), d.d55, (COALESCE(b.sk_borrower_id, xb.sk_borrower_id)), (COALESCE(b.borrower, (xb.borrower_fio)::text)), (COALESCE((to_char((d.d37)::timestamp with time zone, 'DD.MM.YYYY'::text))::character varying(10), '01.01.1900'::character varying(10))), (CASE WHEN (d.d63 IS NULL) THEN '0'::text ELSE md5(d.d63) END), (COALESCE(d.d63, 'Не определено'::text)), (CASE WHEN ((d.d68 IS NULL) OR (r.region_id = '0x000000000000006A'::text)) THEN 0 ELSE (r.federal_district)::integer END), (CASE WHEN ((d.d68 IS NULL) OR (r.region_id = '0x000000000000006A'::text)) THEN 'Центральный федеральный округ'::text ELSE CASE (r.federal_district)::integer WHEN 0 THEN 'Центральный федеральный округ'::text WHEN 1 THEN 'Северо-Западный федеральный округ'::text WHEN 2 THEN 'Южный федеральный округ'::text WHEN 3 THEN 'Приволжский федеральный округ'::text WHEN 4 THEN 'Уральский федеральный округ'::text WHEN 5 THEN 'Сибирский федеральный округ'::text WHEN 6 THEN 'Дальневосточный федеральный округ'::text WHEN 7 THEN 'Северо-Кавказский федеральный округ'::text WHEN 8 THEN 'Крымский федеральный округ'::text ELSE NULL::text END END), (COALESCE(r.sk_region_id, '349'::bigint)), (COALESCE(r.region_id, '0x000000000000006A'::text)), (COALESCE(r.region_name, 'Не определено'::text)), ((COALESCE(r.region_code, '0'::numeric))::integer), ((to_char((d.d98)::timestamp with time zone, 'DD.MM.YYYY'::text))::character varying(10)), 0, d.d40, d.d41, d.d42, ((to_char((d.d42)::timestamp with time zone, 'DD.MM.YYYY'::text))::character varying(10)), ((to_char((COALESCE(d.d43, '1900-01-01'::date))::timestamp with time zone, 'DD.MM.YYYY'::text))::character varying(10)), 0, (COALESCE(d.d45, 'Не определено'::text)), d.d93, 0, (COALESCE(d.d46, 'Не определено'::text)), d.d92, 0, (COALESCE(d.d50, 'Не определено'::text)), (CASE WHEN (d.d48 IS NULL) THEN '0'::text ELSE md5(d.d48) END), d.d48, (CASE WHEN (d.d49 IS NULL) THEN '0'::text ELSE md5(d.d49) END), d.d49, (CASE WHEN (d.d58 IS NULL) THEN '0'::text ELSE md5(d.d58) END), d.d58, (CASE WHEN (d.d47 IS NULL) THEN '0'::text ELSE md5(d.d47) END), d.d47, (COALESCE(d.d107, '6'::text)), (CASE COALESCE(d.d107, '6'::text) WHEN '0'::text THEN 'Активная'::text WHEN '1'::text THEN 'Погашена'::text WHEN '2'::text THEN 'Продана'::text WHEN '3'::text THEN 'Продана и погашена'::text WHEN '4'::text THEN 'Реализована'::text WHEN '5'::text THEN 'Реализована и погашена'::text WHEN '6'::text THEN 'Не определено'::text ELSE NULL::text END), 0, 0, 'Нет'::text, 0, 0, 'Нет'::text, (CASE WHEN (d.d101 IS NULL) THEN '0'::text ELSE md5(d.d101) END), d.d101, (CASE WHEN (d.d36 IS NULL) THEN '0'::text ELSE md5(d.d36) END), d.d36, ((COALESCE(d.d6, '8'::text))::integer), '0'::text, 'Не определено'::text, (COALESCE(((d.d106)::numeric)::integer, CASE WHEN (d.t1 = ANY ('{C1,C2,C3}'::text[])) THEN 0 WHEN (d.t1 = 'CI1'::text) THEN 1 ELSE '-1'::integer END)), (CASE WHEN (d.t1 = ANY ('{C1,C2,C3}'::text[])) THEN 'Баланс'::text WHEN (d.t1 = 'CI1'::text) THEN 'Инвесторы'::text ELSE 'Не определено'::text END), 3, 'Excel'::text, 7, (COALESCE(d.i15, '0'::numeric)), (COALESCE(d.i16, '0'::numeric)), (COALESCE(d.i17, '0'::numeric)), (COALESCE(d.i20, '0'::numeric)), (COALESCE(d.i21, '0'::numeric)), (COALESCE(d.i24, 0)), (COALESCE(d.i30, '0'::numeric)), (COALESCE(d.i32, '0'::numeric)), (COALESCE(d.i33, '0'::numeric)), (COALESCE(d.i38, '0'::numeric)), (COALESCE(d.i39, '0'::numeric)), (COALESCE(d.i41, '0'::numeric)), (COALESCE(d.i42, '0'::numeric)), (COALESCE(d.i43, '0'::numeric)), (COALESCE(d.i50, '0'::numeric)), (COALESCE(d.i51, '0'::numeric)), (COALESCE(d.i52, '0'::numeric)), (COALESCE(d.i53, '0'::numeric)), (COALESCE(d.i54, '0'::numeric)), (COALESCE(d.i55, '0'::numeric)), (COALESCE(d.i62, '0'::numeric)), (COALESCE(d.i64, '0'::numeric)), (COALESCE(d.i90, (COALESCE(d.i76, '0'::numeric) + COALESCE(d.i77, '0'::numeric)))), '1900-01-01'::date, 0, '1900-01-01'::date, '1900-01-01'::date, 0, '1900-01-01'::date, '1900-01-01'::date, 0, '1900-01-01'::date, '1900-01-01'::date, 0, '1900-01-01'::date, '1900-01-01'::date, '1900-01-01'::date, '1900-01-01'::date, '0'::double precision, '0'::double precision, ((COALESCE(d.i110, '0'::numeric))::double precision), '0'::double precision, '0'::double precision, '0'::double precision, '0'::double precision, '0'::double precision, ((COALESCE(d.i118, '0'::numeric))::double precision), '0'::double precision, '0'::double precision, '0'::double precision, '0'::double precision, '0'::double precision, 'Не определено'::text, 'Не определено'::text, 'Не определено'::text, '1900-01-01'::date, '1900-01-01'::date, 'Не определено'::text, 'Не определено'::text, 0.000000000::numeric(25,9), 0.000000000::numeric(25,9), 0.000000000::numeric(25,9)
  • Buffers: shared hit=7,941,279 read=1,114, temp read=34,352 written=34,356
  • I/O Timings: read=372.190
2. 3,502.355 42,527.674 ↓ 40.9 229,371 1

Sort (cost=5,017,371.37..5,017,385.38 rows=5,605 width=2,824) (actual time=42,227.446..42,527.674 rows=229,371 loops=1)

  • Output: (((d.d39 + '1 day'::interval))::date), ((to_char((d.d39 + '1 day'::interval), 'DD.MM.YYYY'::text))::character varying(10)), d.d1, d.d2, ((to_char((d.d2)::timestamp with time zone, 'DD.MM.YYYY'::text))::character varying(10)), (COALESCE(ld.sk_loan_standard_id, '0'::bigint)), (COALESCE(ld.standard_name, 'Не определено'::text)), d.d8, d.d22, (COALESCE(d.d9, '1900-01-01'::date)), d.d18, (COALESCE(d.d17, "substring"(d.d18, 1, 7))), ("substring"(d.d18, 3, 1)), ("substring"(d.d18, 5, 3)), ("substring"(d.d18, 9, 17)), ((to_char((d.d19)::timestamp with time zone, 'DD.MM.YYYY'::text))::character varying(10)), ((to_char((d.d34)::timestamp with time zone, 'dd.mm.yyyy'::text))::character varying(10)), (((btrim(d.d35))::numeric)::integer), (CASE WHEN (d.d55 ~~ '%ДОМ.РФ%'::text) THEN 2 ELSE 0 END), d.d55, (COALESCE(b.sk_borrower_id, xb.sk_borrower_id)), (COALESCE(b.borrower, (xb.borrower_fio)::text)), (COALESCE((to_char((d.d37)::timestamp with time zone, 'DD.MM.YYYY'::text))::character varying(10), '01.01.1900'::character varying(10))), (CASE WHEN (d.d63 IS NULL) THEN '0'::text ELSE md5(d.d63) END), (COALESCE(d.d63, 'Не определено'::text)), (CASE WHEN ((d.d68 IS NULL) OR (r.region_id = '0x000000000000006A'::text)) THEN 0 ELSE (r.federal_district)::integer END), (CASE WHEN ((d.d68 IS NULL) OR (r.region_id = '0x000000000000006A'::text)) THEN 'Центральный федеральный округ'::text ELSE CASE (r.federal_district)::integer WHEN 0 THEN 'Центральный федеральный округ'::text WHEN 1 THEN 'Северо-Западный федеральный округ'::text WHEN 2 THEN 'Южный федеральный округ'::text WHEN 3 THEN 'Приволжский федеральный округ'::text WHEN 4 THEN 'Уральский федеральный округ'::text WHEN 5 THEN 'Сибирский федеральный округ'::text WHEN 6 THEN 'Дальневосточный федеральный округ'::text WHEN 7 THEN 'Северо-Кавказский федеральный округ'::text WHEN 8 THEN 'Крымский федеральный округ'::text ELSE NULL::text END END), (COALESCE(r.sk_region_id, '349'::bigint)), (COALESCE(r.region_id, '0x000000000000006A'::text)), (COALESCE(r.region_name, 'Не определено'::text)), ((COALESCE(r.region_code, '0'::numeric))::integer), ((to_char((d.d98)::timestamp with time zone, 'DD.MM.YYYY'::text))::character varying(10)), 0, d.d40, d.d41, d.d42, ((to_char((d.d42)::timestamp with time zone, 'DD.MM.YYYY'::text))::character varying(10)), ((to_char((COALESCE(d.d43, '1900-01-01'::date))::timestamp with time zone, 'DD.MM.YYYY'::text))::character varying(10)), 0, (COALESCE(d.d45, 'Не определено'::text)), d.d93, 0, (COALESCE(d.d46, 'Не определено'::text)), d.d92, 0, (COALESCE(d.d50, 'Не определено'::text)), (CASE WHEN (d.d48 IS NULL) THEN '0'::text ELSE md5(d.d48) END), d.d48, (CASE WHEN (d.d49 IS NULL) THEN '0'::text ELSE md5(d.d49) END), d.d49, (CASE WHEN (d.d58 IS NULL) THEN '0'::text ELSE md5(d.d58) END), d.d58, (CASE WHEN (d.d47 IS NULL) THEN '0'::text ELSE md5(d.d47) END), d.d47, (COALESCE(d.d107, '6'::text)), (CASE COALESCE(d.d107, '6'::text) WHEN '0'::text THEN 'Активная'::text WHEN '1'::text THEN 'Погашена'::text WHEN '2'::text THEN 'Продана'::text WHEN '3'::text THEN 'Продана и погашена'::text WHEN '4'::text THEN 'Реализована'::text WHEN '5'::text THEN 'Реализована и погашена'::text WHEN '6'::text THEN 'Не определено'::text ELSE NULL::text END), 0, 0, 'Нет'::text, 0, 0, 'Нет'::text, (CASE WHEN (d.d101 IS NULL) THEN '0'::text ELSE md5(d.d101) END), d.d101, (CASE WHEN (d.d36 IS NULL) THEN '0'::text ELSE md5(d.d36) END), d.d36, ((COALESCE(d.d6, '8'::text))::integer), '0'::text, 'Не определено'::text, (COALESCE(((d.d106)::numeric)::integer, CASE WHEN (d.t1 = ANY ('{C1,C2,C3}'::text[])) THEN 0 WHEN (d.t1 = 'CI1'::text) THEN 1 ELSE '-1'::integer END)), (CASE WHEN (d.t1 = ANY ('{C1,C2,C3}'::text[])) THEN 'Баланс'::text WHEN (d.t1 = 'CI1'::text) THEN 'Инвесторы'::text ELSE 'Не определено'::text END), 3, 'Excel'::text, 7, (COALESCE(d.i15, '0'::numeric)), (COALESCE(d.i16, '0'::numeric)), (COALESCE(d.i17, '0'::numeric)), (COALESCE(d.i20, '0'::numeric)), (COALESCE(d.i21, '0'::numeric)), (COALESCE(d.i24, 0)), (COALESCE(d.i30, '0'::numeric)), (COALESCE(d.i32, '0'::numeric)), (COALESCE(d.i33, '0'::numeric)), (COALESCE(d.i38, '0'::numeric)), (COALESCE(d.i39, '0'::numeric)), (COALESCE(d.i41, '0'::numeric)), (COALESCE(d.i42, '0'::numeric)), (COALESCE(d.i43, '0'::numeric)), (COALESCE(d.i50, '0'::numeric)), (COALESCE(d.i51, '0'::numeric)), (COALESCE(d.i52, '0'::numeric)), (COALESCE(d.i53, '0'::numeric)), (COALESCE(d.i54, '0'::numeric)), (COALESCE(d.i55, '0'::numeric)), (COALESCE(d.i62, '0'::numeric)), (COALESCE(d.i64, '0'::numeric)), (COALESCE(d.i90, (COALESCE(d.i76, '0'::numeric) + COALESCE(d.i77, '0'::numeric)))), '1900-01-01'::date, 0, '1900-01-01'::date, '1900-01-01'::date, 0, '1900-01-01'::date, '1900-01-01'::date, 0, '1900-01-01'::date, '1900-01-01'::date, 0, '1900-01-01'::date, '1900-01-01'::date, '1900-01-01'::date, '1900-01-01'::date, '0'::double precision, '0'::double precision, ((COALESCE(d.i110, '0'::numeric))::double precision), '0'::double precision, '0'::double precision, '0'::double precision, '0'::double precision, '0'::double precision, ((COALESCE(d.i118, '0'::numeric))::double precision), '0'::double precision, '0'::double precision, '0'::double precision, '0'::double precision, '0'::double precision, 'Не определено'::text, 'Не определено'::text, 'Не определено'::text, '1900-01-01'::date, '1900-01-01'::date, 'Не определено'::text, 'Не определено'::text, 0.000000000::numeric(25,9), 0.000000000::numeric(25,9), 0.000000000::numeric(25,9)
  • Sort Key: (((d.d39 + '1 day'::interval))::date), ((to_char((d.d39 + '1 day'::interval), 'DD.MM.YYYY'::text))::character varying(10)), d.d1, d.d2, ((to_char((d.d2)::timestamp with time zone, 'DD.MM.YYYY'::text))::character varying(10)), (COALESCE(ld.sk_loan_standard_id, '0'::bigint)), (COALESCE(ld.standard_name, 'Не определено'::text)), d.d8, d.d22, (COALESCE(d.d9, '1900-01-01'::date)), d.d18, (COALESCE(d.d17, "substring"(d.d18, 1, 7))), ("substring"(d.d18, 3, 1)), ("substring"(d.d18, 5, 3)), ("substring"(d.d18, 9, 17)), ((to_char((d.d19)::timestamp with time zone, 'DD.MM.YYYY'::text))::character varying(10)), ((to_char((d.d34)::timestamp with time zone, 'dd.mm.yyyy'::text))::character varying(10)), (((btrim(d.d35))::numeric)::integer), (CASE WHEN (d.d55 ~~ '%ДОМ.РФ%'::text) THEN 2 ELSE 0 END), d.d55, (COALESCE(b.sk_borrower_id, xb.sk_borrower_id)), (COALESCE(b.borrower, (xb.borrower_fio)::text)), (COALESCE((to_char((d.d37)::timestamp with time zone, 'DD.MM.YYYY'::text))::character varying(10), '01.01.1900'::character varying(10))), (CASE WHEN (d.d63 IS NULL) THEN '0'::text ELSE md5(d.d63) END), (COALESCE(d.d63, 'Не определено'::text)), (CASE WHEN ((d.d68 IS NULL) OR (r.region_id = '0x000000000000006A'::text)) THEN 0 ELSE (r.federal_district)::integer END), (CASE WHEN ((d.d68 IS NULL) OR (r.region_id = '0x000000000000006A'::text)) THEN 'Центральный федеральный округ'::text ELSE CASE (r.federal_district)::integer WHEN 0 THEN 'Центральный федеральный округ'::text WHEN 1 THEN 'Северо-Западный федеральный округ'::text WHEN 2 THEN 'Южный федеральный округ'::text WHEN 3 THEN 'Приволжский федеральный округ'::text WHEN 4 THEN 'Уральский федеральный округ'::text WHEN 5 THEN 'Сибирский федеральный округ'::text WHEN 6 THEN 'Дальневосточный федеральный округ'::text WHEN 7 THEN 'Северо-Кавказский федеральный округ'::text WHEN 8 THEN 'Крымский федеральный округ'::text ELSE NULL::text END END), (COALESCE(r.sk_region_id, '349'::bigint)), (COALESCE(r.region_id, '0x000000000000006A'::text)), (COALESCE(r.region_name, 'Не определено'::text)), ((COALESCE(r.region_code, '0'::numeric))::integer), ((to_char((d.d98)::timestamp with time zone, 'DD.MM.YYYY'::text))::character varying(10)), d.d40, d.d41, d.d42, ((to_char((d.d42)::timestamp with time zone, 'DD.MM.YYYY'::text))::character varying(10)), ((to_char((COALESCE(d.d43, '1900-01-01'::date))::timestamp with time zone, 'DD.MM.YYYY'::text))::character varying(10)), (COALESCE(d.d45, 'Не определено'::text)), d.d93, (COALESCE(d.d46, 'Не определено'::text)), d.d92, (COALESCE(d.d50, 'Не определено'::text)), (CASE WHEN (d.d48 IS NULL) THEN '0'::text ELSE md5(d.d48) END), d.d48, (CASE WHEN (d.d49 IS NULL) THEN '0'::text ELSE md5(d.d49) END), d.d49, (CASE WHEN (d.d58 IS NULL) THEN '0'::text ELSE md5(d.d58) END), d.d58, (CASE WHEN (d.d47 IS NULL) THEN '0'::text ELSE md5(d.d47) END), d.d47, (COALESCE(d.d107, '6'::text)), (CASE COALESCE(d.d107, '6'::text) WHEN '0'::text THEN 'Активная'::text WHEN '1'::text THEN 'Погашена'::text WHEN '2'::text THEN 'Продана'::text WHEN '3'::text THEN 'Продана и погашена'::text WHEN '4'::text THEN 'Реализована'::text WHEN '5'::text THEN 'Реализована и погашена'::text WHEN '6'::text THEN 'Не определено'::text ELSE NULL::text END), (CASE WHEN (d.d101 IS NULL) THEN '0'::text ELSE md5(d.d101) END), d.d101, (CASE WHEN (d.d36 IS NULL) THEN '0'::text ELSE md5(d.d36) END), d.d36, ((COALESCE(d.d6, '8'::text))::integer), (COALESCE(((d.d106)::numeric)::integer, CASE WHEN (d.t1 = ANY ('{C1,C2,C3}'::text[])) THEN 0 WHEN (d.t1 = 'CI1'::text) THEN 1 ELSE '-1'::integer END)), (CASE WHEN (d.t1 = ANY ('{C1,C2,C3}'::text[])) THEN 'Баланс'::text WHEN (d.t1 = 'CI1'::text) THEN 'Инвесторы'::text ELSE 'Не определено'::text END), (COALESCE(d.i15, '0'::numeric)), (COALESCE(d.i16, '0'::numeric)), (COALESCE(d.i17, '0'::numeric)), (COALESCE(d.i20, '0'::numeric)), (COALESCE(d.i21, '0'::numeric)), (COALESCE(d.i24, 0)), (COALESCE(d.i30, '0'::numeric)), (COALESCE(d.i32, '0'::numeric)), (COALESCE(d.i33, '0'::numeric)), (COALESCE(d.i38, '0'::numeric)), (COALESCE(d.i39, '0'::numeric)), (COALESCE(d.i41, '0'::numeric)), (COALESCE(d.i42, '0'::numeric)), (COALESCE(d.i43, '0'::numeric)), (COALESCE(d.i50, '0'::numeric)), (COALESCE(d.i51, '0'::numeric)), (COALESCE(d.i52, '0'::numeric)), (COALESCE(d.i53, '0'::numeric)), (COALESCE(d.i54, '0'::numeric)), (COALESCE(d.i55, '0'::numeric)), (COALESCE(d.i62, '0'::numeric)), (COALESCE(d.i64, '0'::numeric)), (COALESCE(d.i90, (COALESCE(d.i76, '0'::numeric) + COALESCE(d.i77, '0'::numeric)))), ((COALESCE(d.i110, '0'::numeric))::double precision), ((COALESCE(d.i118, '0'::numeric))::double precision)
  • Sort Method: external merge Disk: 274,816kB
  • Buffers: shared hit=7,941,279 read=1,114, temp read=34,352 written=34,356
  • I/O Timings: read=372.190
3. 2,327.929 39,025.319 ↓ 40.9 229,371 1

Hash Left Join (cost=3,712,771.18..5,017,022.39 rows=5,605 width=2,824) (actual time=21,008.460..39,025.319 rows=229,371 loops=1)

  • Output: ((d.d39 + '1 day'::interval))::date, (to_char((d.d39 + '1 day'::interval), 'DD.MM.YYYY'::text))::character varying(10), d.d1, d.d2, (to_char((d.d2)::timestamp with time zone, 'DD.MM.YYYY'::text))::character varying(10), COALESCE(ld.sk_loan_standard_id, '0'::bigint), COALESCE(ld.standard_name, 'Не определено'::text), d.d8, d.d22, COALESCE(d.d9, '1900-01-01'::date), d.d18, COALESCE(d.d17, "substring"(d.d18, 1, 7)), "substring"(d.d18, 3, 1), "substring"(d.d18, 5, 3), "substring"(d.d18, 9, 17), (to_char((d.d19)::timestamp with time zone, 'DD.MM.YYYY'::text))::character varying(10), (to_char((d.d34)::timestamp with time zone, 'dd.mm.yyyy'::text))::character varying(10), ((btrim(d.d35))::numeric)::integer, CASE WHEN (d.d55 ~~ '%ДОМ.РФ%'::text) THEN 2 ELSE 0 END, d.d55, COALESCE(b.sk_borrower_id, xb.sk_borrower_id), COALESCE(b.borrower, (xb.borrower_fio)::text), COALESCE((to_char((d.d37)::timestamp with time zone, 'DD.MM.YYYY'::text))::character varying(10), '01.01.1900'::character varying(10)), CASE WHEN (d.d63 IS NULL) THEN '0'::text ELSE md5(d.d63) END, COALESCE(d.d63, 'Не определено'::text), CASE WHEN ((d.d68 IS NULL) OR (r.region_id = '0x000000000000006A'::text)) THEN 0 ELSE (r.federal_district)::integer END, CASE WHEN ((d.d68 IS NULL) OR (r.region_id = '0x000000000000006A'::text)) THEN 'Центральный федеральный округ'::text ELSE CASE (r.federal_district)::integer WHEN 0 THEN 'Центральный федеральный округ'::text WHEN 1 THEN 'Северо-Западный федеральный округ'::text WHEN 2 THEN 'Южный федеральный округ'::text WHEN 3 THEN 'Приволжский федеральный округ'::text WHEN 4 THEN 'Уральский федеральный округ'::text WHEN 5 THEN 'Сибирский федеральный округ'::text WHEN 6 THEN 'Дальневосточный федеральный округ'::text WHEN 7 THEN 'Северо-Кавказский федеральный округ'::text WHEN 8 THEN 'Крымский федеральный округ'::text ELSE NULL::text END END, COALESCE(r.sk_region_id, '349'::bigint), COALESCE(r.region_id, '0x000000000000006A'::text), COALESCE(r.region_name, 'Не определено'::text), (COALESCE(r.region_code, '0'::numeric))::integer, (to_char((d.d98)::timestamp with time zone, 'DD.MM.YYYY'::text))::character varying(10), 0, d.d40, d.d41, d.d42, (to_char((d.d42)::timestamp with time zone, 'DD.MM.YYYY'::text))::character varying(10), (to_char((COALESCE(d.d43, '1900-01-01'::date))::timestamp with time zone, 'DD.MM.YYYY'::text))::character varying(10), 0, COALESCE(d.d45, 'Не определено'::text), d.d93, 0, COALESCE(d.d46, 'Не определено'::text), d.d92, 0, COALESCE(d.d50, 'Не определено'::text), CASE WHEN (d.d48 IS NULL) THEN '0'::text ELSE md5(d.d48) END, d.d48, CASE WHEN (d.d49 IS NULL) THEN '0'::text ELSE md5(d.d49) END, d.d49, CASE WHEN (d.d58 IS NULL) THEN '0'::text ELSE md5(d.d58) END, d.d58, CASE WHEN (d.d47 IS NULL) THEN '0'::text ELSE md5(d.d47) END, d.d47, COALESCE(d.d107, '6'::text), CASE COALESCE(d.d107, '6'::text) WHEN '0'::text THEN 'Активная'::text WHEN '1'::text THEN 'Погашена'::text WHEN '2'::text THEN 'Продана'::text WHEN '3'::text THEN 'Продана и погашена'::text WHEN '4'::text THEN 'Реализована'::text WHEN '5'::text THEN 'Реализована и погашена'::text WHEN '6'::text THEN 'Не определено'::text ELSE NULL::text END, 0, 0, 'Нет'::text, 0, 0, 'Нет'::text, CASE WHEN (d.d101 IS NULL) THEN '0'::text ELSE md5(d.d101) END, d.d101, CASE WHEN (d.d36 IS NULL) THEN '0'::text ELSE md5(d.d36) END, d.d36, (COALESCE(d.d6, '8'::text))::integer, '0'::text, 'Не определено'::text, COALESCE(((d.d106)::numeric)::integer, CASE WHEN (d.t1 = ANY ('{C1,C2,C3}'::text[])) THEN 0 WHEN (d.t1 = 'CI1'::text) THEN 1 ELSE '-1'::integer END), CASE WHEN (d.t1 = ANY ('{C1,C2,C3}'::text[])) THEN 'Баланс'::text WHEN (d.t1 = 'CI1'::text) THEN 'Инвесторы'::text ELSE 'Не определено'::text END, 3, 'Excel'::text, 7, COALESCE(d.i15, '0'::numeric), COALESCE(d.i16, '0'::numeric), COALESCE(d.i17, '0'::numeric), COALESCE(d.i20, '0'::numeric), COALESCE(d.i21, '0'::numeric), COALESCE(d.i24, 0), COALESCE(d.i30, '0'::numeric), COALESCE(d.i32, '0'::numeric), COALESCE(d.i33, '0'::numeric), COALESCE(d.i38, '0'::numeric), COALESCE(d.i39, '0'::numeric), COALESCE(d.i41, '0'::numeric), COALESCE(d.i42, '0'::numeric), COALESCE(d.i43, '0'::numeric), COALESCE(d.i50, '0'::numeric), COALESCE(d.i51, '0'::numeric), COALESCE(d.i52, '0'::numeric), COALESCE(d.i53, '0'::numeric), COALESCE(d.i54, '0'::numeric), COALESCE(d.i55, '0'::numeric), COALESCE(d.i62, '0'::numeric), COALESCE(d.i64, '0'::numeric), COALESCE(d.i90, (COALESCE(d.i76, '0'::numeric) + COALESCE(d.i77, '0'::numeric))), '1900-01-01'::date, 0, '1900-01-01'::date, '1900-01-01'::date, 0, '1900-01-01'::date, '1900-01-01'::date, 0, '1900-01-01'::date, '1900-01-01'::date, 0, '1900-01-01'::date, '1900-01-01'::date, '1900-01-01'::date, '1900-01-01'::date, '0'::double precision, '0'::double precision, (COALESCE(d.i110, '0'::numeric))::double precision, '0'::double precision, '0'::double precision, '0'::double precision, '0'::double precision, '0'::double precision, (COALESCE(d.i118, '0'::numeric))::double precision, '0'::double precision, '0'::double precision, '0'::double precision, '0'::double precision, '0'::double precision, 'Не определено'::text, 'Не определено'::text, 'Не определено'::text, '1900-01-01'::date, '1900-01-01'::date, 'Не определено'::text, 'Не определено'::text, 0.000000000::numeric(25,9), 0.000000000::numeric(25,9), 0.000000000::numeric(25,9)
  • Hash Cond: (d.d1 = (l.mortgage_no)::text)
  • Buffers: shared hit=7,941,261 read=1,114
  • I/O Timings: read=372.190
4. 151.931 35,719.718 ↓ 80.3 229,371 1

Hash Left Join (cost=3,473,567.82..4,776,598.07 rows=2,856 width=1,089) (actual time=20,027.506..35,719.718 rows=229,371 loops=1)

  • Output: d.d39, d.d1, d.d2, d.d8, d.d22, d.d9, d.d18, d.d17, d.d19, d.d34, d.d35, d.d55, d.d37, d.d63, d.d68, d.d98, d.d40, d.d41, d.d42, d.d43, d.d45, d.d93, d.d46, d.d92, d.d50, d.d48, d.d49, d.d58, d.d47, d.d107, d.d101, d.d36, d.d6, d.d106, d.t1, d.i15, d.i16, d.i17, d.i20, d.i21, d.i24, d.i30, d.i32, d.i33, d.i38, d.i39, d.i41, d.i42, d.i43, d.i50, d.i51, d.i52, d.i53, d.i54, d.i55, d.i62, d.i64, d.i90, d.i76, d.i77, d.i110, d.i118, ld.sk_loan_standard_id, ld.standard_name, b.sk_borrower_id, b.borrower, xb.sk_borrower_id, xb.borrower_fio, r.region_id, r.federal_district, r.sk_region_id, r.region_name, r.region_code
  • Hash Cond: (d.d68 = r.region_name)
  • Buffers: shared hit=7,655,040 read=1,114
  • I/O Timings: read=372.190
5. 196.198 35,567.702 ↓ 80.3 229,371 1

Hash Left Join (cost=3,473,560.88..4,776,570.16 rows=2,856 width=1,019) (actual time=20,027.399..35,567.702 rows=229,371 loops=1)

  • Output: d.d39, d.d1, d.d2, d.d8, d.d22, d.d9, d.d18, d.d17, d.d19, d.d34, d.d35, d.d55, d.d37, d.d63, d.d68, d.d98, d.d40, d.d41, d.d42, d.d43, d.d45, d.d93, d.d46, d.d92, d.d50, d.d48, d.d49, d.d58, d.d47, d.d107, d.d101, d.d36, d.d6, d.d106, d.t1, d.i15, d.i16, d.i17, d.i20, d.i21, d.i24, d.i30, d.i32, d.i33, d.i38, d.i39, d.i41, d.i42, d.i43, d.i50, d.i51, d.i52, d.i53, d.i54, d.i55, d.i62, d.i64, d.i90, d.i76, d.i77, d.i110, d.i118, ld.sk_loan_standard_id, ld.standard_name, b.sk_borrower_id, b.borrower, xb.sk_borrower_id, xb.borrower_fio
  • Hash Cond: ((d.d1 = (xb.mortgage_no)::text) AND (d.d59 = (xb.borrower_fio)::text))
  • Buffers: shared hit=7,655,036 read=1,114
  • I/O Timings: read=372.190
6. 259.554 35,044.922 ↓ 80.3 229,371 1

Hash Left Join (cost=3,448,166.17..4,751,075.48 rows=2,856 width=1,007) (actual time=19,698.484..35,044.922 rows=229,371 loops=1)

  • Output: d.d39, d.d1, d.d2, d.d8, d.d22, d.d9, d.d18, d.d17, d.d19, d.d34, d.d35, d.d55, d.d37, d.d63, d.d68, d.d98, d.d40, d.d41, d.d42, d.d43, d.d45, d.d93, d.d46, d.d92, d.d50, d.d48, d.d49, d.d58, d.d47, d.d107, d.d101, d.d36, d.d6, d.d106, d.t1, d.i15, d.i16, d.i17, d.i20, d.i21, d.i24, d.i30, d.i32, d.i33, d.i38, d.i39, d.i41, d.i42, d.i43, d.i50, d.i51, d.i52, d.i53, d.i54, d.i55, d.i62, d.i64, d.i90, d.i76, d.i77, d.i110, d.i118, d.d59, ld.sk_loan_standard_id, ld.standard_name, b.sk_borrower_id, b.borrower
  • Hash Cond: ((d.d1 = b.mortgage_no) AND (d.d59 = b.borrower))
  • Buffers: shared hit=7,645,134 read=1,114
  • I/O Timings: read=372.190
7. 169.538 16,114.063 ↓ 74.4 212,485 1

Hash Left Join (cost=914,830.76..2,217,718.64 rows=2,856 width=960) (actual time=1,027.159..16,114.063 rows=212,485 loops=1)

  • Output: d.d39, d.d1, d.d2, d.d8, d.d22, d.d9, d.d18, d.d17, d.d19, d.d34, d.d35, d.d55, d.d37, d.d63, d.d68, d.d98, d.d40, d.d41, d.d42, d.d43, d.d45, d.d93, d.d46, d.d92, d.d50, d.d48, d.d49, d.d58, d.d47, d.d107, d.d101, d.d36, d.d6, d.d106, d.t1, d.i15, d.i16, d.i17, d.i20, d.i21, d.i24, d.i30, d.i32, d.i33, d.i38, d.i39, d.i41, d.i42, d.i43, d.i50, d.i51, d.i52, d.i53, d.i54, d.i55, d.i62, d.i64, d.i90, d.i76, d.i77, d.i110, d.i118, d.d59, ld.sk_loan_standard_id, ld.standard_name
  • Hash Cond: (CASE d.d3 WHEN 'Стандарт 21-20 (коп)'::text THEN 'Нестандарт 21-20/1-20 (коп)'::text WHEN '1-31'::text THEN 'Стандарт 1-31 (коп)'::text ELSE d.d3 END = ld.standard_name)
  • Buffers: shared hit=1,098,364
8. 184.173 15,944.475 ↓ 74.4 212,485 1

Hash Left Join (cost=914,825.18..2,217,676.07 rows=2,856 width=934) (actual time=1,027.081..15,944.475 rows=212,485 loops=1)

  • Output: d.d39, d.d1, d.d2, d.d8, d.d22, d.d9, d.d18, d.d17, d.d19, d.d34, d.d35, d.d55, d.d37, d.d63, d.d68, d.d98, d.d40, d.d41, d.d42, d.d43, d.d45, d.d93, d.d46, d.d92, d.d50, d.d48, d.d49, d.d58, d.d47, d.d107, d.d101, d.d36, d.d6, d.d106, d.t1, d.i15, d.i16, d.i17, d.i20, d.i21, d.i24, d.i30, d.i32, d.i33, d.i38, d.i39, d.i41, d.i42, d.i43, d.i50, d.i51, d.i52, d.i53, d.i54, d.i55, d.i62, d.i64, d.i90, d.i76, d.i77, d.i110, d.i118, d.d3, d.d59
  • Hash Cond: ((d.d1 = (xl.mortgage_no)::text) AND (d.t1 = (xl.source)::text))
  • Buffers: shared hit=1,098,360
9. 1,658.269 15,356.294 ↓ 74.4 212,485 1

Hash Join (cost=886,115.04..2,188,872.05 rows=2,856 width=934) (actual time=617.219..15,356.294 rows=212,485 loops=1)

  • Output: d.d39, d.d1, d.d2, d.d8, d.d22, d.d9, d.d18, d.d17, d.d19, d.d34, d.d35, d.d55, d.d37, d.d63, d.d68, d.d98, d.d40, d.d41, d.d42, d.d43, d.d45, d.d93, d.d46, d.d92, d.d50, d.d48, d.d49, d.d58, d.d47, d.d107, d.d101, d.d36, d.d6, d.d106, d.t1, d.i15, d.i16, d.i17, d.i20, d.i21, d.i24, d.i30, d.i32, d.i33, d.i38, d.i39, d.i41, d.i42, d.i43, d.i50, d.i51, d.i52, d.i53, d.i54, d.i55, d.i62, d.i64, d.i90, d.i76, d.i77, d.i110, d.i118, d.d3, d.d59
  • Inner Unique: true
  • Hash Cond: ((d.t1 = d_1.t1) AND (d.d39 = d_1.d39) AND (d.load_session_id = (max(d_1.load_session_id))))
  • Buffers: shared hit=1,088,811
10. 13,311.054 13,311.054 ↑ 1.0 15,868,542 1

Seq Scan on dwh_stage.xls_data d (cost=0.00..1,177,719.18 rows=15,877,818 width=942) (actual time=0.011..13,311.054 rows=15,868,542 loops=1)

  • Output: d.d39, d.d1, d.d2, d.d8, d.d22, d.d9, d.d18, d.d17, d.d19, d.d34, d.d35, d.d55, d.d37, d.d63, d.d68, d.d98, d.d40, d.d41, d.d42, d.d43, d.d45, d.d93, d.d46, d.d92, d.d50, d.d48, d.d49, d.d58, d.d47, d.d107, d.d101, d.d36, d.d6, d.d106, d.t1, d.i15, d.i16, d.i17, d.i20, d.i21, d.i24, d.i30, d.i32, d.i33, d.i38, d.i39, d.i41, d.i42, d.i43, d.i50, d.i51, d.i52, d.i53, d.i54, d.i55, d.i62, d.i64, d.i90, d.i76, d.i77, d.i110, d.i118, d.load_session_id, d.d3, d.d59
  • Buffers: shared hit=1,018,941
11. 0.017 386.971 ↑ 56.8 44 1

Hash (cost=886,071.27..886,071.27 rows=2,501 width=20) (actual time=386.971..386.971 rows=44 loops=1)

  • Output: d_1.t1, d_1.d39, (max(d_1.load_session_id))
  • Buckets: 4,096 Batches: 1 Memory Usage: 35kB
  • Buffers: shared hit=69,870
12. 0.019 386.954 ↑ 56.8 44 1

Sort (cost=886,040.01..886,046.26 rows=2,501 width=20) (actual time=386.951..386.954 rows=44 loops=1)

  • Output: d_1.t1, d_1.d39, (max(d_1.load_session_id))
  • Sort Key: d_1.t1, d_1.d39, (max(d_1.load_session_id)) DESC NULLS LAST
  • Sort Method: quicksort Memory: 27kB
  • Buffers: shared hit=69,870
13. 0.045 386.935 ↑ 56.8 44 1

Finalize GroupAggregate (cost=885,723.78..885,898.85 rows=2,501 width=20) (actual time=386.885..386.935 rows=44 loops=1)

  • Output: d_1.t1, d_1.d39, max(d_1.load_session_id)
  • Group Key: d_1.t1, d_1.d39
  • Buffers: shared hit=69,870
14. 0.000 386.890 ↑ 82.0 183 1

Sort (cost=885,723.78..885,761.30 rows=15,006 width=20) (actual time=386.878..386.890 rows=183 loops=1)

  • Output: d_1.t1, d_1.d39, (PARTIAL max(d_1.load_session_id))
  • Sort Key: d_1.t1, d_1.d39
  • Sort Method: quicksort Memory: 33kB
  • Buffers: shared hit=69,870
15. 27.349 388.608 ↑ 82.0 183 1

Gather (cost=883,157.26..884,682.87 rows=15,006 width=20) (actual time=385.469..388.608 rows=183 loops=1)

  • Output: d_1.t1, d_1.d39, (PARTIAL max(d_1.load_session_id))
  • Workers Planned: 6
  • Workers Launched: 6
  • Buffers: shared hit=69,870
16. 46.109 361.259 ↑ 96.2 26 7 / 7

Partial HashAggregate (cost=882,157.26..882,182.27 rows=2,501 width=20) (actual time=361.228..361.259 rows=26 loops=7)

  • Output: d_1.t1, d_1.d39, PARTIAL max(d_1.load_session_id)
  • Group Key: d_1.t1, d_1.d39
  • Buffers: shared hit=69,870
  • Worker 0: actual time=358.622..358.651 rows=26 loops=1
  • Buffers: shared hit=9,193
  • Worker 1: actual time=357.211..357.247 rows=29 loops=1
  • Buffers: shared hit=9,082
  • Worker 2: actual time=357.631..357.666 rows=26 loops=1
  • Buffers: shared hit=8,555
  • Worker 3: actual time=357.352..357.383 rows=23 loops=1
  • Buffers: shared hit=9,620
  • Worker 4: actual time=357.157..357.183 rows=26 loops=1
  • Buffers: shared hit=8,962
  • Worker 5: actual time=357.256..357.282 rows=26 loops=1
  • Buffers: shared hit=8,900
17. 299.239 315.150 ↑ 1.2 139,654 7 / 7

Parallel Bitmap Heap Scan on dwh_stage.xls_data d_1 (cost=17,229.09..880,904.94 rows=166,976 width=20) (actual time=114.604..315.150 rows=139,654 loops=7)

  • Output: d_1.t1, d_1.d39, d_1.load_session_id
  • Recheck Cond: (d_1.t1 = ANY ('{C1,C2,C3,CI1}'::text[]))
  • Heap Blocks: exact=12,870
  • Buffers: shared hit=69,870
  • Worker 0: actual time=112.443..316.794 rows=134,122 loops=1
  • Buffers: shared hit=9,193
  • Worker 1: actual time=111.070..313.611 rows=131,926 loops=1
  • Buffers: shared hit=9,082
  • Worker 2: actual time=111.440..311.184 rows=124,386 loops=1
  • Buffers: shared hit=8,555
  • Worker 3: actual time=111.215..312.503 rows=140,066 loops=1
  • Buffers: shared hit=9,620
  • Worker 4: actual time=111.112..312.405 rows=130,231 loops=1
  • Buffers: shared hit=8,962
  • Worker 5: actual time=111.177..309.439 rows=129,414 loops=1
  • Buffers: shared hit=8,900
18. 15.911 15.911 ↑ 1.0 977,577 1 / 7

Bitmap Index Scan on xls_data_t1_idx (cost=0.00..16,978.63 rows=1,001,853 width=0) (actual time=111.375..111.375 rows=977,577 loops=1)

  • Index Cond: (d_1.t1 = ANY ('{C1,C2,C3,CI1}'::text[]))
  • Buffers: shared hit=2,688
19. 162.757 404.008 ↓ 1.0 527,336 1

Hash (cost=20,801.18..20,801.18 rows=527,264 width=23) (actual time=404.008..404.008 rows=527,336 loops=1)

  • Output: xl.mortgage_no, xl.source
  • Buckets: 1,048,576 Batches: 1 Memory Usage: 36,938kB
  • Buffers: shared hit=9,549
20. 241.251 241.251 ↓ 1.0 527,336 1

Seq Scan on dwh_dl.sat_xls_loan xl (cost=0.00..20,801.18 rows=527,264 width=23) (actual time=0.024..241.251 rows=527,336 loops=1)

  • Output: xl.mortgage_no, xl.source
  • Filter: ((now() >= xl.sat_valid_from) AND (now() <= xl.sat_valid_to))
  • Rows Removed by Filter: 35,273
  • Buffers: shared hit=9,549
21. 0.016 0.050 ↑ 1.2 59 1

Hash (cost=4.70..4.70 rows=70 width=49) (actual time=0.050..0.050 rows=59 loops=1)

  • Output: ld.sk_loan_standard_id, ld.standard_name
  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
  • Buffers: shared hit=4
22. 0.034 0.034 ↑ 1.0 70 1

Seq Scan on dwh_dl.sat_eis_loan_standard ld (cost=0.00..4.70 rows=70 width=49) (actual time=0.009..0.034 rows=70 loops=1)

  • Output: ld.sk_loan_standard_id, ld.standard_name
  • Buffers: shared hit=4
23. 78.342 18,671.305 ↓ 11,166.5 212,164 1

Hash (cost=2,533,335.13..2,533,335.13 rows=19 width=61) (actual time=18,671.305..18,671.305 rows=212,164 loops=1)

  • Output: b.sk_borrower_id, b.borrower, b.mortgage_no
  • Buckets: 262,144 (originally 1024) Batches: 1 (originally 1) Memory Usage: 23,957kB
  • Buffers: shared hit=6,546,770 read=1,114
  • I/O Timings: read=372.190
24. 37.048 18,592.963 ↓ 11,166.5 212,164 1

Subquery Scan on b (cost=2,533,334.89..2,533,335.13 rows=19 width=61) (actual time=18,522.529..18,592.963 rows=212,164 loops=1)

  • Output: b.sk_borrower_id, b.borrower, b.mortgage_no
  • Buffers: shared hit=6,546,770 read=1,114
  • I/O Timings: read=372.190
25. 1,180.539 18,555.915 ↓ 11,166.5 212,164 1

Sort (cost=2,533,334.89..2,533,334.94 rows=19 width=119) (actual time=18,522.526..18,555.915 rows=212,164 loops=1)

  • Output: p.sk_person_id, NULL::character varying(20), d_2.d1, d_2.d59
  • Sort Key: d_2.d59
  • Sort Method: quicksort Memory: 35,979kB
  • Buffers: shared hit=6,546,770 read=1,114
  • I/O Timings: read=372.190
26. 663.190 17,375.376 ↓ 11,166.5 212,164 1

Nested Loop (cost=1,125,320.95..2,533,334.48 rows=19 width=119) (actual time=1,559.412..17,375.376 rows=212,164 loops=1)

  • Output: p.sk_person_id, NULL::character varying(20), d_2.d1, d_2.d59
  • Join Filter: (lower(btrim(d_2.d59)) = lower(btrim(((((p.last_name || ' '::text) || p.first_name) || ' '::text) || p.middle_name))))
  • Rows Removed by Join Filter: 240
  • Buffers: shared hit=6,546,770 read=1,114
  • I/O Timings: read=372.190
27. 64.029 15,862.566 ↓ 60.1 212,405 1

Nested Loop (cost=1,125,320.52..2,518,563.91 rows=3,534 width=61) (actual time=1,559.348..15,862.566 rows=212,405 loops=1)

  • Output: d_2.d1, d_2.d59, lbr.sk_person_id
  • Buffers: shared hit=5,682,641 read=1,114
  • I/O Timings: read=372.190
28. 139.553 14,948.917 ↓ 60.1 212,405 1

Nested Loop (cost=1,125,320.10..2,503,934.74 rows=3,534 width=69) (actual time=1,559.322..14,948.917 rows=212,405 loops=1)

  • Output: d_2.d1, d_2.d59, lbr.sk_lnk_borrower_person_id, lbr.sk_person_id
  • Buffers: shared hit=4,831,851 read=1,114
  • I/O Timings: read=372.190
29. 0.000 14,172.149 ↓ 60.1 212,405 1

Nested Loop (cost=1,125,319.67..2,489,338.18 rows=3,534 width=69) (actual time=1,559.298..14,172.149 rows=212,405 loops=1)

  • Output: d_2.d1, d_2.d59, lmb.sk_main_borrower_id, b_1.sk_borrower_id
  • Buffers: shared hit=3,981,084 read=1,114
  • I/O Timings: read=372.190
30. 2.026 13,337.034 ↓ 60.1 212,508 1

Nested Loop (cost=1,125,319.25..2,474,703.16 rows=3,538 width=77) (actual time=1,559.275..13,337.034 rows=212,508 loops=1)

  • Output: d_2.d1, d_2.d59, lmb.sk_lnk_loan_main_borrower_id, lmb.sk_main_borrower_id, b_1.sk_borrower_id
  • Buffers: shared hit=3,129,834 read=1,114
  • I/O Timings: read=372.190
31. 71.384 12,272.213 ↓ 37.9 212,559 1

Nested Loop (cost=1,125,318.82..2,451,355.18 rows=5,613 width=69) (actual time=1,559.246..12,272.213 rows=212,559 loops=1)

  • Output: d_2.d1, d_2.d59, lmb.sk_lnk_loan_main_borrower_id, lmb.sk_main_borrower_id
  • Buffers: shared hit=2,224,868 read=1,114
  • I/O Timings: read=372.190
32. 174.874 11,138.804 ↓ 37.9 212,405 1

Hash Join (cost=1,125,318.40..2,428,231.42 rows=5,605 width=61) (actual time=1,555.123..11,138.804 rows=212,405 loops=1)

  • Output: d_2.d1, d_2.d59, sl.sk_loan_id
  • Hash Cond: (d_2.d1 = (sl.mortgage_no)::text)
  • Buffers: shared hit=1,375,035
33. 1,697.242 9,796.000 ↓ 74.4 212,485 1

Hash Join (cost=886,115.04..2,188,872.05 rows=2,856 width=53) (actual time=385.223..9,796.000 rows=212,485 loops=1)

  • Output: d_2.d1, d_2.d59
  • Inner Unique: true
  • Hash Cond: ((d_2.t1 = d_3.t1) AND (d_2.d39 = d_3.d39) AND (d_2.load_session_id = (max(d_3.load_session_id))))
  • Buffers: shared hit=1,088,811
34. 7,804.957 7,804.957 ↑ 1.0 15,868,542 1

Seq Scan on dwh_stage.xls_data d_2 (cost=0.00..1,177,719.18 rows=15,877,818 width=73) (actual time=0.011..7,804.957 rows=15,868,542 loops=1)

  • Output: d_2.d1, d_2.d59, d_2.t1, d_2.d39, d_2.load_session_id
  • Buffers: shared hit=1,018,941
35. 0.019 293.801 ↑ 56.8 44 1

Hash (cost=886,071.27..886,071.27 rows=2,501 width=20) (actual time=293.801..293.801 rows=44 loops=1)

  • Output: d_3.t1, d_3.d39, (max(d_3.load_session_id))
  • Buckets: 4,096 Batches: 1 Memory Usage: 35kB
  • Buffers: shared hit=69,870
36. 0.027 293.782 ↑ 56.8 44 1

Sort (cost=886,040.01..886,046.26 rows=2,501 width=20) (actual time=293.780..293.782 rows=44 loops=1)

  • Output: d_3.t1, d_3.d39, (max(d_3.load_session_id))
  • Sort Key: d_3.t1, d_3.d39, (max(d_3.load_session_id)) DESC NULLS LAST
  • Sort Method: quicksort Memory: 27kB
  • Buffers: shared hit=69,870
37. 0.036 293.755 ↑ 56.8 44 1

Finalize GroupAggregate (cost=885,723.78..885,898.85 rows=2,501 width=20) (actual time=293.716..293.755 rows=44 loops=1)

  • Output: d_3.t1, d_3.d39, max(d_3.load_session_id)
  • Group Key: d_3.t1, d_3.d39
  • Buffers: shared hit=69,870
38. 0.000 293.719 ↑ 84.3 178 1

Sort (cost=885,723.78..885,761.30 rows=15,006 width=20) (actual time=293.710..293.719 rows=178 loops=1)

  • Output: d_3.t1, d_3.d39, (PARTIAL max(d_3.load_session_id))
  • Sort Key: d_3.t1, d_3.d39
  • Sort Method: quicksort Memory: 33kB
  • Buffers: shared hit=69,870
39. 18.747 295.664 ↑ 84.3 178 1

Gather (cost=883,157.26..884,682.87 rows=15,006 width=20) (actual time=292.313..295.664 rows=178 loops=1)

  • Output: d_3.t1, d_3.d39, (PARTIAL max(d_3.load_session_id))
  • Workers Planned: 6
  • Workers Launched: 6
  • Buffers: shared hit=69,870
40. 38.615 276.917 ↑ 100.0 25 7 / 7

Partial HashAggregate (cost=882,157.26..882,182.27 rows=2,501 width=20) (actual time=276.883..276.917 rows=25 loops=7)

  • Output: d_3.t1, d_3.d39, PARTIAL max(d_3.load_session_id)
  • Group Key: d_3.t1, d_3.d39
  • Buffers: shared hit=69,870
  • Worker 0: actual time=273.079..273.120 rows=24 loops=1
  • Buffers: shared hit=8,937
  • Worker 1: actual time=276.057..276.104 rows=24 loops=1
  • Buffers: shared hit=8,826
  • Worker 2: actual time=275.157..275.191 rows=23 loops=1
  • Buffers: shared hit=8,641
  • Worker 3: actual time=276.051..276.084 rows=24 loops=1
  • Buffers: shared hit=9,223
  • Worker 4: actual time=273.162..273.192 rows=26 loops=1
  • Buffers: shared hit=8,547
  • Worker 5: actual time=273.919..273.950 rows=24 loops=1
  • Buffers: shared hit=8,572
41. 229.357 238.302 ↑ 1.2 139,654 7 / 7

Parallel Bitmap Heap Scan on dwh_stage.xls_data d_3 (cost=17,229.09..880,904.94 rows=166,976 width=20) (actual time=63.517..238.302 rows=139,654 loops=7)

  • Output: d_3.t1, d_3.d39, d_3.load_session_id
  • Recheck Cond: (d_3.t1 = ANY ('{C1,C2,C3,CI1}'::text[]))
  • Heap Blocks: exact=14,436
  • Buffers: shared hit=69,870
  • Worker 0: actual time=60.063..233.786 rows=130,165 loops=1
  • Buffers: shared hit=8,937
  • Worker 1: actual time=63.013..239.251 rows=128,733 loops=1
  • Buffers: shared hit=8,826
  • Worker 2: actual time=62.129..237.212 rows=125,759 loops=1
  • Buffers: shared hit=8,641
  • Worker 3: actual time=63.035..240.612 rows=134,073 loops=1
  • Buffers: shared hit=9,223
  • Worker 4: actual time=60.139..232.873 rows=124,284 loops=1
  • Buffers: shared hit=8,547
  • Worker 5: actual time=60.888..235.105 rows=124,589 loops=1
  • Buffers: shared hit=8,572
42. 8.945 8.945 ↑ 1.0 977,577 1 / 7

Bitmap Index Scan on xls_data_t1_idx (cost=0.00..16,978.63 rows=1,001,853 width=0) (actual time=62.615..62.615 rows=977,577 loops=1)

  • Index Cond: (d_3.t1 = ANY ('{C1,C2,C3,CI1}'::text[]))
  • Buffers: shared hit=2,688
43. 136.180 1,167.930 ↓ 1.0 496,663 1

Hash (cost=233,086.22..233,086.22 rows=489,371 width=22) (actual time=1,167.930..1,167.930 rows=496,663 loops=1)

  • Output: sl.mortgage_no, sl.sk_loan_id
  • Buckets: 524,288 Batches: 1 Memory Usage: 31,258kB
  • Buffers: shared hit=286,224
44. 39.204 1,031.750 ↓ 1.0 496,907 1

Append (cost=0.56..233,086.22 rows=489,371 width=22) (actual time=0.067..1,031.750 rows=496,907 loops=1)

  • Buffers: shared hit=286,224
45. 992.520 992.520 ↓ 1.0 496,907 1

Index Scan using sat_eis_loan_sat_valid_to on dwh_dl.sat_eis_loan sl (cost=0.56..233,083.26 rows=489,370 width=22) (actual time=0.066..992.520 rows=496,907 loops=1)

  • Output: sl.mortgage_no, sl.sk_loan_id
  • Index Cond: (now() <= sl.sat_valid_to)
  • Filter: (now() >= sl.sat_valid_from)
  • Buffers: shared hit=286,221
46. 0.026 0.026 ↓ 0.0 0 1

Index Scan using sat_eis_loan_h_sat_valid_to on dwh_dl.sat_eis_loan_h sl_1 (cost=0.43..2.96 rows=1 width=22) (actual time=0.026..0.026 rows=0 loops=1)

  • Output: sl_1.mortgage_no, sl_1.sk_loan_id
  • Index Cond: (now() <= sl_1.sat_valid_to)
  • Filter: (now() >= sl_1.sat_valid_from)
  • Buffers: shared hit=3
47. 1,062.025 1,062.025 ↑ 1.0 1 212,405

Index Scan using fki_lnk_loan_main_borrower_left_idx on dwh_dl.lnk_loan_main_borrower lmb (cost=0.42..4.12 rows=1 width=24) (actual time=0.005..0.005 rows=1 loops=212,405)

  • Output: lmb.sk_lnk_loan_main_borrower_id, lmb.sk_loan_id, lmb.sk_main_borrower_id, lmb.load_session_id, lmb.l_rsrc
  • Index Cond: (lmb.sk_loan_id = sl.sk_loan_id)
  • Buffers: shared hit=849,833 read=1,114
  • I/O Timings: read=372.190
48. 1,062.795 1,062.795 ↑ 1.0 1 212,559

Index Scan using fki_sat_eis_borrower_fk on dwh_dl.sat_eis_borrower b_1 (cost=0.43..4.15 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=212,559)

  • Output: b_1.sk_sat_eis_borrower_id, b_1.sk_borrower_id, b_1.s_rsrc, b_1.load_session_id, b_1.sat_valid_from, b_1.sat_valid_to, b_1.row_hash, b_1.credit_person_id, b_1.rn_descriptor, b_1.rn_create_date, b_1.rn_create_user, b_1.rn_edit_date, b_1.rn_edit_user, b_1.credit_id, b_1.role_main_borrower, b_1.role_coborrower, b_1.role_pledger, b_1.person_id, b_1.role_dependant, b_1.roles_desc, b_1.registration_status, b_1.child_count, b_1.child_count_coliving, b_1.child_count_underage, b_1.under_care, b_1.incomings, b_1.expenses, b_1.role_maternity_capital_manager, b_1.credit_change_id, b_1.role_representative, b_1.role_family_member, b_1.bankruptcy_initiated, b_1.incomings_confirmation_doc, b_1.contact_info_actuality_date, b_1.passport_id, b_1.registration_address_id, b_1.residence_address_id, b_1.mailing_address_id, b_1.email, b_1.contact_info_absent, b_1.cft_dirty, b_1.cft_send_date, b_1.cft_send_error, b_1.incomings_by_official_doc, b_1.incomings_by_unofficial_doc, b_1.imported_from_aus, b_1.credit_bureau_consent, b_1.zhrs_person_id, b_1.bankruptcy_initiated_date
  • Index Cond: (b_1.sk_borrower_id = lmb.sk_main_borrower_id)
  • Filter: ((b_1.role_main_borrower = 1) AND (now() >= b_1.sat_valid_from) AND (now() <= b_1.sat_valid_to))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=904,966
49. 850.032 850.032 ↑ 1.0 1 212,508

Index Scan using fki_sat_lnk_loan_main_borrower_lnk_idx on dwh_dl.sat_lnk_loan_main_borrower slmb (cost=0.42..4.13 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=212,508)

  • Output: slmb.sk_sat_lnk_loan_main_borrower_id, slmb.sk_lnk_loan_main_borrower_id, slmb.load_session_id, slmb.sl_rsrc, slmb.lnk_valid_from, slmb.lnk_valid_to
  • Index Cond: (slmb.sk_lnk_loan_main_borrower_id = lmb.sk_lnk_loan_main_borrower_id)
  • Filter: ((now() >= slmb.lnk_valid_from) AND (now() <= slmb.lnk_valid_to))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=851,250
50. 637.215 637.215 ↑ 1.0 1 212,405

Index Scan using fki_lnk_borrower_person_left_idx on dwh_dl.lnk_borrower_person lbr (cost=0.42..4.12 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=212,405)

  • Output: lbr.sk_lnk_borrower_person_id, lbr.sk_borrower_id, lbr.sk_person_id, lbr.load_session_id, lbr.l_rsrc
  • Index Cond: (lbr.sk_borrower_id = lmb.sk_main_borrower_id)
  • Buffers: shared hit=850,767
51. 849.620 849.620 ↑ 1.0 1 212,405

Index Scan using fki_sat_lnk_borrower_person_lnk_idx on dwh_dl.sat_lnk_borrower_person slbr (cost=0.42..4.13 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=212,405)

  • Output: slbr.sk_sat_lnk_borrower_person_id, slbr.sk_lnk_borrower_person_id, slbr.load_session_id, slbr.sl_rsrc, slbr.lnk_valid_from, slbr.lnk_valid_to
  • Index Cond: (slbr.sk_lnk_borrower_person_id = lbr.sk_lnk_borrower_person_id)
  • Filter: ((now() >= slbr.lnk_valid_from) AND (now() <= slbr.lnk_valid_to))
  • Buffers: shared hit=850,790
52. 849.620 849.620 ↑ 1.0 1 212,405

Index Scan using sat_eis_person_hub_person_fk on dwh_dl.sat_eis_person p (cost=0.43..4.15 rows=1 width=58) (actual time=0.004..0.004 rows=1 loops=212,405)

  • Output: p.sk_sat_eis_person_id, p.sk_person_id, p.s_rsrc, p.load_session_id, p.sat_valid_from, p.sat_valid_to, p.row_hash, p.person_id, p.rn_descriptor, p.rn_create_date, p.rn_create_user, p.rn_edit_date, p.rn_edit_user, p.last_name, p.first_name, p.middle_name, p.sex, p.birth_date, p.married, p.last_name_gen, p.first_name_gen, p.middle_name_gen, p.education_level, p.passport_id, p.inn, p.owner_id, p.last_name_dat, p.first_name_dat, p.middle_name_dat, p.employment_type, p.job_position, p.last_name_ins, p.first_name_ins, p.middle_name_ins, p.last_name_acc, p.first_name_acc, p.middle_name_acc, p.employment_kind, p.passport_descriptor, p.birth_place, p.snils
  • Index Cond: (p.sk_person_id = lbr.sk_person_id)
  • Filter: ((now() >= p.sat_valid_from) AND (now() <= p.sat_valid_to) AND (p.sk_person_id <> ALL ('{3482704,3325425,3264305,3126060,3048175,3269576}'::bigint[])))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=864,129
53. 144.445 326.582 ↑ 1.0 442,644 1

Hash (cost=18,755.04..18,755.04 rows=442,645 width=67) (actual time=326.582..326.582 rows=442,644 loops=1)

  • Output: xb.sk_borrower_id, xb.borrower_fio, xb.mortgage_no
  • Buckets: 524,288 Batches: 1 Memory Usage: 47,223kB
  • Buffers: shared hit=9,902
54. 182.137 182.137 ↑ 1.0 442,644 1

Seq Scan on dwh_dl.sat_xls_borrower xb (cost=0.00..18,755.04 rows=442,645 width=67) (actual time=0.033..182.137 rows=442,644 loops=1)

  • Output: xb.sk_borrower_id, xb.borrower_fio, xb.mortgage_no
  • Filter: ((now() >= xb.sat_valid_from) AND (now() <= xb.sat_valid_to))
  • Rows Removed by Filter: 10
  • Buffers: shared hit=9,902
55. 0.031 0.085 ↑ 1.0 88 1

Hash (cost=5.84..5.84 rows=88 width=70) (actual time=0.085..0.085 rows=88 loops=1)

  • Output: r.region_id, r.federal_district, r.sk_region_id, r.region_name, r.region_code
  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
  • Buffers: shared hit=4
56. 0.054 0.054 ↑ 1.0 88 1

Seq Scan on dwh_dl.sat_eis_region r (cost=0.00..5.84 rows=88 width=70) (actual time=0.012..0.054 rows=88 loops=1)

  • Output: r.region_id, r.federal_district, r.sk_region_id, r.region_name, r.region_code
  • Filter: ((now() >= r.sat_valid_from) AND (now() <= r.sat_valid_to))
  • Rows Removed by Filter: 4
  • Buffers: shared hit=4
57. 121.763 977.672 ↓ 1.0 496,663 1

Hash (cost=233,086.22..233,086.22 rows=489,371 width=14) (actual time=977.672..977.672 rows=496,663 loops=1)

  • Output: l.mortgage_no
  • Buckets: 524,288 Batches: 1 Memory Usage: 26,408kB
  • Buffers: shared hit=286,221
58. 35.102 855.909 ↓ 1.0 496,907 1

Append (cost=0.56..233,086.22 rows=489,371 width=14) (actual time=0.038..855.909 rows=496,907 loops=1)

  • Buffers: shared hit=286,221
59. 820.782 820.782 ↓ 1.0 496,907 1

Index Scan using sat_eis_loan_sat_valid_to on dwh_dl.sat_eis_loan l (cost=0.56..233,083.26 rows=489,370 width=14) (actual time=0.038..820.782 rows=496,907 loops=1)

  • Output: l.mortgage_no
  • Index Cond: (now() <= l.sat_valid_to)
  • Filter: (now() >= l.sat_valid_from)
  • Buffers: shared hit=286,218
60. 0.025 0.025 ↓ 0.0 0 1

Index Scan using sat_eis_loan_h_sat_valid_to on dwh_dl.sat_eis_loan_h l_1 (cost=0.43..2.96 rows=1 width=14) (actual time=0.025..0.025 rows=0 loops=1)

  • Output: l_1.mortgage_no
  • Index Cond: (now() <= l_1.sat_valid_to)
  • Filter: (now() >= l_1.sat_valid_from)
  • Buffers: shared hit=3
Planning time : 116.276 ms
Execution time : 42,905.540 ms