explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9rmy

Settings
# exclusive inclusive rows x rows loops node
1. 667.465 10,537,433.489 ↓ 37.9 212,485 1

Unique (cost=13,731,193.29..13,732,383.93 rows=5,603 width=2,824) (actual time=10,535,694.377..10,537,433.489 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=6,786,124 read=239,218 dirtied=1,239, temp read=186,652 written=788,819
  • I/O Timings: read=279,359.668
2.          

CTE load_sessions

3. 0.023 413.057 ↑ 56.8 44 1

Sort (cost=885,885.21..885,891.47 rows=2,501 width=20) (actual time=413.050..413.057 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
4. 0.046 413.034 ↑ 56.8 44 1

Finalize GroupAggregate (cost=885,568.98..885,744.05 rows=2,501 width=20) (actual time=412.984..413.034 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
5. 0.000 412.988 ↑ 82.9 181 1

Sort (cost=885,568.98..885,606.50 rows=15,006 width=20) (actual time=412.977..412.988 rows=181 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
6. 32.076 415.618 ↑ 82.9 181 1

Gather (cost=883,002.46..884,528.07 rows=15,006 width=20) (actual time=409.791..415.618 rows=181 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
7. 53.062 383.542 ↑ 96.2 26 7 / 7

Partial HashAggregate (cost=882,002.46..882,027.47 rows=2,501 width=20) (actual time=383.512..383.542 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=381.003..381.047 rows=24 loops=1
  • Buffers: shared hit=9,090
  • Worker 1: actual time=380.575..380.606 rows=26 loops=1
  • Buffers: shared hit=9,067
  • Worker 2: actual time=376.471..376.497 rows=26 loops=1
  • Buffers: shared hit=9,031
  • Worker 3: actual time=376.907..376.940 rows=26 loops=1
  • Buffers: shared hit=8,663
  • Worker 4: actual time=380.513..380.538 rows=27 loops=1
  • Buffers: shared hit=8,899
  • Worker 5: actual time=381.440..381.465 rows=23 loops=1
  • Buffers: shared hit=8,754
8. 315.637 330.480 ↑ 1.2 139,654 7 / 7

Parallel Bitmap Heap Scan on dwh_stage.xls_data d_1 (cost=17,227.73..880,750.37 rows=166,946 width=20) (actual time=107.374..330.480 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=13,678
  • Buffers: shared hit=69,870
  • Worker 0: actual time=105.494..330.327 rows=132,482 loops=1
  • Buffers: shared hit=9,090
  • Worker 1: actual time=105.089..330.450 rows=131,806 loops=1
  • Buffers: shared hit=9,067
  • Worker 2: actual time=100.972..322.193 rows=131,594 loops=1
  • Buffers: shared hit=9,031
  • Worker 3: actual time=101.420..325.188 rows=125,906 loops=1
  • Buffers: shared hit=8,663
  • Worker 4: actual time=105.010..327.336 rows=129,577 loops=1
  • Buffers: shared hit=8,899
  • Worker 5: actual time=105.966..331.822 rows=127,272 loops=1
  • Buffers: shared hit=8,754
9. 14.843 14.843 ↑ 1.0 977,577 1 / 7

Bitmap Index Scan on xls_data_t1_idx (cost=0.00..16,977.31 rows=1,001,676 width=0) (actual time=103.900..103.900 rows=977,577 loops=1)

  • Index Cond: (d_1.t1 = ANY ('{C1,C2,C3,CI1}'::text[]))
  • Buffers: shared hit=2,688
10.          

CTE sat_eis_borrower

11. 2,012.167 323,844.555 ↓ 11,166.5 212,164 1

Sort (cost=4,121,844.10..4,121,844.15 rows=19 width=75) (actual time=323,794.465..323,844.555 rows=212,164 loops=1)

  • Output: p.sk_person_id, sl.mortgage_no, d_2.d1, d_2.d59
  • Sort Key: d_2.d59
  • Sort Method: quicksort Memory: 35,982kB
  • Buffers: shared hit=5,411,272 read=219,760 dirtied=1,236, temp read=35,687 written=123,997
  • I/O Timings: read=278,579.739
12. 1,698.782 321,832.388 ↓ 11,166.5 212,164 1

Nested Loop (cost=3,804,799.59..4,121,843.69 rows=19 width=75) (actual time=27,141.493..321,832.388 rows=212,164 loops=1)

  • Output: p.sk_person_id, sl.mortgage_no, 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=5,411,272 read=219,760 dirtied=1,236, temp read=35,687 written=123,997
  • I/O Timings: read=278,579.739
13. 264.781 306,114.876 ↓ 60.1 212,405 1

Nested Loop (cost=3,804,799.16..4,107,077.30 rows=3,533 width=75) (actual time=27,141.452..306,114.876 rows=212,405 loops=1)

  • Output: d_2.d1, d_2.d59, sl.mortgage_no, lbr.sk_person_id
  • Buffers: shared hit=4,560,488 read=206,415 dirtied=162, temp read=35,687 written=123,997
  • I/O Timings: read=266,499.303
14. 298.346 304,150.855 ↓ 60.1 212,405 1

Nested Loop (cost=3,804,798.74..4,092,452.27 rows=3,533 width=83) (actual time=27,141.438..304,150.855 rows=212,405 loops=1)

  • Output: d_2.d1, d_2.d59, sl.mortgage_no, lbr.sk_lnk_borrower_person_id, lbr.sk_person_id
  • Buffers: shared hit=3,709,750 read=206,363 dirtied=161, temp read=35,687 written=123,997
  • I/O Timings: read=266,358.835
15. 242.474 302,578.079 ↓ 60.1 212,405 1

Nested Loop (cost=3,804,798.31..4,077,859.84 rows=3,533 width=83) (actual time=27,141.425..302,578.079 rows=212,405 loops=1)

  • Output: d_2.d1, d_2.d59, sl.mortgage_no, lmb.sk_main_borrower_id, b_1.sk_borrower_id
  • Buffers: shared hit=2,859,046 read=206,300 dirtied=160, temp read=35,687 written=123,997
  • I/O Timings: read=266,284.692
16. 404.332 300,423.033 ↓ 60.1 212,508 1

Nested Loop (cost=3,804,797.89..4,063,233.09 rows=3,536 width=91) (actual time=27,141.411..300,423.033 rows=212,508 loops=1)

  • Output: d_2.d1, d_2.d59, sl.mortgage_no, lmb.sk_lnk_loan_main_borrower_id, lmb.sk_main_borrower_id, b_1.sk_borrower_id
  • Buffers: shared hit=2,007,880 read=206,216 dirtied=160, temp read=35,687 written=123,997
  • I/O Timings: read=266,042.457
17. 391.128 288,965.633 ↓ 37.9 212,559 1

Hash Join (cost=3,804,797.46..4,039,893.43 rows=5,611 width=83) (actual time=27,141.379..288,965.633 rows=212,559 loops=1)

  • Output: d_2.d1, d_2.d59, sl.mortgage_no, lmb.sk_lnk_loan_main_borrower_id, lmb.sk_main_borrower_id
  • Hash Cond: (sl.sk_loan_id = lmb.sk_loan_id)
  • Buffers: shared hit=1,113,015 read=196,115 dirtied=133, temp read=35,687 written=123,997
  • I/O Timings: read=257,080.050
18. 829.977 288,311.994 ↓ 37.9 212,405 1

Hash Join (cost=3,789,446.59..4,024,423.42 rows=5,603 width=75) (actual time=26,877.857..288,311.994 rows=212,405 loops=1)

  • Output: d_2.d1, d_2.d59, sl.mortgage_no, sl.sk_loan_id
  • Hash Cond: ((sl.mortgage_no)::text = d_2.d1)
  • Buffers: shared hit=1,108,866 read=196,115 dirtied=133, temp read=35,687 written=123,997
  • I/O Timings: read=257,080.050
19. 162.800 260,604.241 ↓ 1.0 496,907 1

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

  • Buffers: shared hit=90,106 read=196,115 dirtied=133
  • I/O Timings: read=257,080.050
20. 260,441.403 260,441.403 ↓ 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.055..260,441.403 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=90,103 read=196,115 dirtied=133
  • I/O Timings: read=257,080.050
21. 0.038 0.038 ↓ 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.038..0.038 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
22. 80.718 26,877.776 ↓ 74.4 212,485 1

Hash (cost=3,789,410.34..3,789,410.34 rows=2,855 width=53) (actual time=26,877.776..26,877.776 rows=212,485 loops=1)

  • Output: d_2.d1, d_2.d59
  • Buckets: 262,144 (originally 4096) Batches: 1 (originally 1) Memory Usage: 22,331kB
  • Buffers: shared hit=1,018,760, temp read=35,687 written=123,997
23. 894.733 26,797.058 ↓ 74.4 212,485 1

Merge Join (cost=3,630,603.26..3,789,410.34 rows=2,855 width=53) (actual time=26,455.983..26,797.058 rows=212,485 loops=1)

  • Output: d_2.d1, d_2.d59
  • Merge Cond: ((d_2.t1 = ls_1.t1) AND (d_2.d39 = ls_1.d39) AND (d_2.load_session_id = ls_1.ls))
  • Buffers: shared hit=1,018,760, temp read=35,687 written=123,997
24. 16,155.129 25,884.059 ↑ 4.6 3,421,666 1

Sort (cost=3,630,412.08..3,670,099.57 rows=15,874,998 width=73) (actual time=24,239.747..25,884.059 rows=3,421,666 loops=1)

  • Output: d_2.d1, d_2.d59, d_2.t1, d_2.d39, d_2.load_session_id
  • Sort Key: d_2.t1, d_2.d39, d_2.load_session_id
  • Sort Method: external merge Disk: 991,800kB
  • Buffers: shared hit=1,018,760, temp read=35,687 written=123,997
25. 9,728.930 9,728.930 ↑ 1.0 15,866,808 1

Seq Scan on dwh_stage.xls_data d_2 (cost=0.00..1,177,509.98 rows=15,874,998 width=73) (actual time=0.012..9,728.930 rows=15,866,808 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,760
26. 18.256 18.266 ↓ 49.1 122,889 1

Sort (cost=191.18..197.43 rows=2,501 width=44) (actual time=0.033..18.266 rows=122,889 loops=1)

  • Output: ls_1.t1, ls_1.d39, ls_1.ls
  • Sort Key: ls_1.t1, ls_1.d39, ls_1.ls
  • Sort Method: quicksort Memory: 27kB
27. 0.010 0.010 ↑ 56.8 44 1

CTE Scan on load_sessions ls_1 (cost=0.00..50.02 rows=2,501 width=44) (actual time=0.003..0.010 rows=44 loops=1)

  • Output: ls_1.t1, ls_1.d39, ls_1.ls
28. 141.045 262.511 ↑ 1.0 497,861 1

Hash (cost=9,127.61..9,127.61 rows=497,861 width=24) (actual time=262.511..262.511 rows=497,861 loops=1)

  • Output: lmb.sk_loan_id, lmb.sk_lnk_loan_main_borrower_id, lmb.sk_main_borrower_id
  • Buckets: 524,288 Batches: 1 Memory Usage: 31,323kB
  • Buffers: shared hit=4,149
29. 121.466 121.466 ↑ 1.0 497,861 1

Seq Scan on dwh_dl.lnk_loan_main_borrower lmb (cost=0.00..9,127.61 rows=497,861 width=24) (actual time=0.020..121.466 rows=497,861 loops=1)

  • Output: lmb.sk_loan_id, lmb.sk_lnk_loan_main_borrower_id, lmb.sk_main_borrower_id
  • Buffers: shared hit=4,149
30. 11,053.068 11,053.068 ↑ 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.050..0.052 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=894,865 read=10,101 dirtied=27
  • I/O Timings: read=8,962.406
31. 1,912.572 1,912.572 ↑ 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.009..0.009 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,166 read=84
  • I/O Timings: read=242.235
32. 1,274.430 1,274.430 ↑ 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.006..0.006 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,704 read=63 dirtied=1
  • I/O Timings: read=74.143
33. 1,699.240 1,699.240 ↑ 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.008..0.008 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,738 read=52 dirtied=1
  • I/O Timings: read=140.468
34. 14,018.730 14,018.730 ↑ 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.065..0.066 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=850,784 read=13,345 dirtied=1,074
  • I/O Timings: read=12,080.436
35. 6,410.899 10,536,766.024 ↓ 40.9 229,371 1

Sort (cost=8,723,457.68..8,723,471.69 rows=5,603 width=2,824) (actual time=10,535,694.373..10,536,766.024 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,824kB
  • Buffers: shared hit=6,786,124 read=239,218 dirtied=1,239, temp read=186,652 written=788,819
  • I/O Timings: read=279,359.668
36. 7,053.538 10,530,355.125 ↓ 40.9 229,371 1

Hash Left Join (cost=3,624,491.23..8,723,108.84 rows=5,603 width=2,824) (actual time=361,504.056..10,530,355.125 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=6,786,124 read=239,218 dirtied=1,239, temp read=152,299 written=754,462
  • I/O Timings: read=279,359.668
37. 559.750 10,522,089.076 ↓ 80.3 229,371 1

Hash Left Join (cost=3,385,287.88..8,482,684.96 rows=2,855 width=1,082) (actual time=360,288.355..10,522,089.076 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.d1 = (xb.mortgage_no)::text) AND (d.d59 = (xb.borrower_fio)::text))
  • Buffers: shared hit=6,499,903 read=239,218 dirtied=1,239, temp read=152,299 written=754,462
  • I/O Timings: read=279,359.668
38. 667.157 10,520,637.853 ↓ 80.3 229,371 1

Hash Left Join (cost=3,359,893.16..8,457,190.31 rows=2,855 width=1,070) (actual time=359,394.876..10,520,637.853 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, r.region_id, r.federal_district, r.sk_region_id, r.region_name, r.region_code
  • 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=6,499,902 read=229,317 dirtied=1,237, temp read=152,299 written=754,462
  • I/O Timings: read=278,948.020
39. 641.386 10,519,965.383 ↓ 80.3 229,371 1

Hash Left Join (cost=3,359,887.59..8,457,147.76 rows=2,855 width=1,044) (actual time=359,389.536..10,519,965.383 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.d3, d.d59, b.sk_borrower_id, b.borrower, r.region_id, r.federal_district, r.sk_region_id, r.region_name, r.region_code
  • Hash Cond: ((d.d1 = (xl.mortgage_no)::text) AND (d.t1 = (xl.source)::text))
  • Buffers: shared hit=6,499,902 read=229,313 dirtied=1,237, temp read=152,299 written=754,462
  • I/O Timings: read=278,942.765
40. 2,826.688 10,518,480.031 ↓ 80.3 229,371 1

Nested Loop Left Join (cost=3,331,177.45..8,428,343.77 rows=2,855 width=1,044) (actual time=358,541.145..10,518,480.031 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.d3, d.d59, b.sk_borrower_id, b.borrower, r.region_id, r.federal_district, r.sk_region_id, r.region_name, r.region_code
  • Join Filter: (d.d68 = r.region_name)
  • Rows Removed by Join Filter: 19,955,279
  • Buffers: shared hit=6,499,902 read=219,764 dirtied=1,236, temp read=152,299 written=754,462
  • I/O Timings: read=278,582.376
41. 5,271,846.505 10,514,506.488 ↓ 80.3 229,371 1

Nested Loop Left Join (cost=3,331,177.45..8,424,569.11 rows=2,855 width=974) (actual time=358,538.382..10,514,506.488 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.d3, d.d59, b.sk_borrower_id, b.borrower
  • Join Filter: ((d.d1 = b.mortgage_no) AND (d.d59 = b.borrower))
  • Rows Removed by Join Filter: 45,081,438,490
  • Buffers: shared hit=6,499,902 read=219,760 dirtied=1,236, temp read=152,299 written=754,462
  • I/O Timings: read=278,579.739
42. 5,446.076 39,964.758 ↓ 74.4 212,485 1

Hash Join (cost=3,331,177.45..8,423,212.80 rows=2,855 width=934) (actual time=34,663.685..39,964.758 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: ((ls.t1 = d.t1) AND (ls.d39 = d.d39) AND (ls.ls = d.load_session_id))
  • Buffers: shared hit=1,088,630, temp read=116,612 written=630,465
43. 413.083 413.083 ↑ 56.8 44 1

CTE Scan on load_sessions ls (cost=0.00..50.02 rows=2,501 width=44) (actual time=413.054..413.083 rows=44 loops=1)

  • Output: ls.t1, ls.d39, ls.ls
  • Buffers: shared hit=69,870
44. 14,645.345 34,105.599 ↑ 1.0 15,786,471 1

Hash (cost=1,177,509.98..1,177,509.98 rows=15,874,998 width=942) (actual time=34,105.599..34,105.599 rows=15,786,471 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
  • Buckets: 131,072 Batches: 256 Memory Usage: 67,826kB
  • Buffers: shared hit=1,018,760, temp written=630,172
45. 19,460.254 19,460.254 ↑ 1.0 15,866,808 1

Seq Scan on dwh_stage.xls_data d (cost=0.00..1,177,509.98 rows=15,874,998 width=942) (actual time=0.017..19,460.254 rows=15,866,808 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,760
46. 5,202,695.225 5,202,695.225 ↓ 11,166.5 212,164 212,485

CTE Scan on sat_eis_borrower b (cost=0.00..0.38 rows=19 width=72) (actual time=1.524..24.485 rows=212,164 loops=212,485)

  • Output: b.sk_borrower_id, b.mortgage_no_eis, b.mortgage_no, b.borrower
  • Buffers: shared hit=5,411,272 read=219,760 dirtied=1,236, temp read=35,687 written=123,997
  • I/O Timings: read=278,579.739
47. 1,144.123 1,146.855 ↑ 1.0 88 229,371

Materialize (cost=0.00..6.28 rows=88 width=70) (actual time=0.000..0.005 rows=88 loops=229,371)

  • Output: r.region_id, r.federal_district, r.sk_region_id, r.region_name, r.region_code
  • Buffers: shared read=4
  • I/O Timings: read=2.637
48. 2.732 2.732 ↑ 1.0 88 1

Seq Scan on dwh_dl.sat_eis_region r (cost=0.00..5.84 rows=88 width=70) (actual time=1.946..2.732 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 read=4
  • I/O Timings: read=2.637
49. 204.127 843.966 ↓ 1.0 527,336 1

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

  • Output: xl.mortgage_no, xl.source
  • Buckets: 1,048,576 Batches: 1 Memory Usage: 36,938kB
  • Buffers: shared read=9,549 dirtied=1
  • I/O Timings: read=360.389
50. 639.839 639.839 ↓ 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.660..639.839 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 read=9,549 dirtied=1
  • I/O Timings: read=360.389
51. 0.023 5.313 ↑ 1.2 59 1

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

  • Output: ld.sk_loan_standard_id, ld.standard_name
  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
  • Buffers: shared read=4
  • I/O Timings: read=5.255
52. 5.290 5.290 ↑ 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=4.516..5.290 rows=70 loops=1)

  • Output: ld.sk_loan_standard_id, ld.standard_name
  • Buffers: shared read=4
  • I/O Timings: read=5.255
53. 222.780 891.473 ↑ 1.0 442,644 1

Hash (cost=18,755.04..18,755.04 rows=442,645 width=67) (actual time=891.473..891.473 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=1 read=9,901 dirtied=2
  • I/O Timings: read=411.649
54. 668.693 668.693 ↑ 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=1.276..668.693 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=1 read=9,901 dirtied=2
  • I/O Timings: read=411.649
55. 165.039 1,212.511 ↓ 1.0 496,663 1

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

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

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

  • Buffers: shared hit=286,221
57. 994.485 994.485 ↓ 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.050..994.485 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
58. 0.028 0.028 ↓ 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.028..0.028 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 : 91.645 ms
Execution time : 10,537,520.938 ms