explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nA6C

Settings
# exclusive inclusive rows x rows loops node
1. 175.510 1,245,775.992 ↑ 1.0 1 1

Aggregate (cost=83,051,603.47..83,051,603.48 rows=1 width=8) (actual time=1,245,775.992..1,245,775.992 rows=1 loops=1)

2. 236.713 1,245,600.482 ↓ 3.1 529,127 1

Nested Loop Left Join (cost=318,604.67..83,051,181.74 rows=168,691 width=0) (actual time=1,743.988..1,245,600.482 rows=529,127 loops=1)

3. 141.508 1,239,543.372 ↓ 3.1 529,127 1

Nested Loop Left Join (cost=318,604.24..81,022,536.36 rows=168,691 width=9) (actual time=1,743.968..1,239,543.372 rows=529,127 loops=1)

4. 471.149 1,236,756.229 ↓ 3.1 529,127 1

Nested Loop Left Join (cost=318,599.89..80,284,513.23 rows=168,691 width=13) (actual time=1,743.947..1,236,756.229 rows=529,127 loops=1)

5. 89.712 1,235,226.826 ↓ 3.1 529,127 1

Nested Loop Left Join (cost=318,595.54..79,546,911.84 rows=168,691 width=17) (actual time=1,743.939..1,235,226.826 rows=529,127 loops=1)

6. 869.845 2,800.331 ↓ 3.1 529,127 1

Hash Join (cost=318,590.03..349,763.62 rows=168,691 width=22) (actual time=1,741.348..2,800.331 rows=529,127 loops=1)

  • Hash Cond: (personstate.person_id = person.person_id)
7. 189.406 189.406 ↑ 1.0 628,848 1

Index Only Scan using pk_personstate_id on personstate (cost=0.42..27,128.31 rows=629,072 width=8) (actual time=0.007..189.406 rows=628,848 loops=1)

  • Heap Fetches: 594
8. 108.845 1,741.080 ↓ 3.1 529,127 1

Hash (cost=316,480.96..316,480.96 rows=168,691 width=37) (actual time=1,741.080..1,741.080 rows=529,127 loops=1)

  • Buckets: 524,288 (originally 262144) Batches: 2 (originally 1) Memory Usage: 28,673kB
9. 254.150 1,632.235 ↓ 3.1 529,127 1

Hash Join (cost=173,859.56..316,480.96 rows=168,691 width=37) (actual time=979.965..1,632.235 rows=529,127 loops=1)

  • Hash Cond: ((t."пациент Идентификатор РМИС")::bigint = person.person_id)
10. 564.171 1,126.995 ↓ 3.2 531,718 1

Seq Scan on tmp_for_loading_napr t (cost=147,237.00..284,607.40 rows=168,697 width=29) (actual time=723.597..1,126.995 rows=531,718 loops=1)

  • Filter: (("id талона" IS NOT NULL) AND ("id ячейки РМИС" IS NOT NULL) AND ("DirFailType_id" IS NULL) AND (NOT (hashed SubPlan 1)) AND (isload = 1))
  • Rows Removed by Filter: 1,987,987
11.          

SubPlan (for Seq Scan)

12. 301.847 562.824 ↑ 82.6 4,102 1

GroupAggregate (cost=0.43..146,389.93 rows=338,825 width=9) (actual time=3.067..562.824 rows=4,102 loops=1)

  • Group Key: t_1."id ячейки РМИС
  • Filter: (count(t_1."id ячейки РМИС") > 2)
  • Rows Removed by Filter: 1,528,899
13. 260.977 260.977 ↓ 1.1 2,519,705 1

Index Only Scan using idx_tmp_tmp_for_loading_napr_idrmisycheiki on tmp_for_loading_napr t_1 (cost=0.43..122,246.21 rows=2,287,560 width=9) (actual time=0.029..260.977 rows=2,519,705 loops=1)

  • Heap Fetches: 31,893
14. 115.043 251.090 ↓ 1.0 629,154 1

Hash (cost=16,300.41..16,300.41 rows=629,132 width=8) (actual time=251.089..251.090 rows=629,154 loops=1)

  • Buckets: 1,048,576 (originally 1048576) Batches: 2 (originally 1) Memory Usage: 24,577kB
15. 136.047 136.047 ↓ 1.0 629,154 1

Seq Scan on person (cost=0.00..16,300.41 rows=629,132 width=8) (actual time=0.010..136.047 rows=629,154 loops=1)

  • Filter: (person_deleted = 1)
  • Rows Removed by Filter: 51
16. 529.127 1,232,336.783 ↓ 0.0 0 529,127

Limit (cost=5.52..469.46 rows=1 width=8) (actual time=2.329..2.329 rows=0 loops=529,127)

17. 0.000 1,231,807.656 ↓ 0.0 0 529,127

Subquery Scan on d (cost=5.52..469.46 rows=1 width=8) (actual time=2.328..2.328 rows=0 loops=529,127)

18. 0.000 1,231,807.656 ↓ 0.0 0 529,127

Nested Loop Left Join (cost=5.52..469.45 rows=1 width=740) (actual time=2.328..2.328 rows=0 loops=529,127)

19.          

CTE reg

20. 0.002 0.022 ↑ 1.0 2 1

Unique (cost=0.31..0.32 rows=2 width=4) (actual time=0.020..0.022 rows=2 loops=1)

21. 0.007 0.020 ↑ 1.0 2 1

Sort (cost=0.31..0.32 rows=2 width=4) (actual time=0.019..0.020 rows=2 loops=1)

  • Sort Key: (getregion())
  • Sort Method: quicksort Memory: 25kB
22. 0.001 0.013 ↑ 1.0 2 1

Append (cost=0.00..0.30 rows=2 width=4) (actual time=0.012..0.013 rows=2 loops=1)

23. 0.011 0.011 ↑ 1.0 1 1

Result (cost=0.00..0.26 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=1)

24. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=1)

25. 1,587.381 1,231,807.656 ↓ 0.0 0 529,127

Nested Loop (cost=4.92..468.81 rows=1 width=8) (actual time=2.328..2.328 rows=0 loops=529,127)

26. 529.127 529.127 ↑ 1.0 2 529,127

CTE Scan on reg (cost=0.00..0.04 rows=2 width=4) (actual time=0.000..0.001 rows=2 loops=529,127)

27. 1,020,156.856 1,229,691.148 ↓ 0.0 0 1,058,254

Bitmap Heap Scan on diag (cost=4.92..234.38 rows=1 width=26) (actual time=1.162..1.162 rows=0 loops=1,058,254)

  • Recheck Cond: (COALESCE(region_id, '0'::bigint) = reg.reg)
  • Filter: (((CASE WHEN ((reg.reg = 59) OR (reg.reg = 0)) THEN diag_code ELSE diag_scode END)::text = (t."Diag_code")::text) AND ((klcountry_id = CASE WHEN (getregion() = 101) THEN 398 ELSE 643 END) OR (klcountry_id IS NULL)))
  • Rows Removed by Filter: 7,610
  • Heap Blocks: exact=252,393,579
28. 209,534.292 209,534.292 ↓ 90.6 7,610 1,058,254

Bitmap Index Scan on idx_diag_region_id_is_null (cost=0.00..4.92 rows=84 width=0) (actual time=0.198..0.198 rows=7,610 loops=1,058,254)

  • Index Cond: (COALESCE(region_id, '0'::bigint) = reg.reg)
29. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_mkb10causelink_diag_id_idxfiler on mkb10causelink (cost=0.28..0.31 rows=1 width=8) (never executed)

  • Index Cond: (diag_id = diag.diag_id)
  • Heap Fetches: 0
30. 0.000 1,058.254 ↓ 0.0 0 529,127

Limit (cost=4.35..4.35 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=529,127)

31. 1,058.254 1,058.254 ↓ 0.0 0 529,127

Sort (cost=4.35..4.36 rows=3 width=16) (actual time=0.002..0.002 rows=0 loops=529,127)

  • Sort Key: (COALESCE(st.ls_mainid, '1'::bigint)) DESC
  • Sort Method: quicksort Memory: 25kB
32. 0.000 0.000 ↓ 0.0 0 529,127

Index Only Scan using idx_tmp_tmp_for_loading_lpu_structure_idx2 on tmp_for_loading_lpu_structure st (cost=0.28..4.34 rows=3 width=16) (actual time=0.000..0.000 rows=0 loops=529,127)

  • Index Cond: ("id_РМИС" = (t."направившее отделение")::bigint)
  • Heap Fetches: 0
33. 529.127 2,645.635 ↑ 1.0 1 529,127

Limit (cost=4.35..4.36 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=529,127)

34. 529.127 2,116.508 ↑ 3.0 1 529,127

Sort (cost=4.35..4.36 rows=3 width=16) (actual time=0.004..0.004 rows=1 loops=529,127)

  • Sort Key: (COALESCE(st_1.ls_mainid, '1'::bigint)) DESC
  • Sort Method: top-N heapsort Memory: 25kB
35. 1,587.381 1,587.381 ↓ 1.3 4 529,127

Index Only Scan using idx_tmp_tmp_for_loading_lpu_structure_idx2 on tmp_for_loading_lpu_structure st_1 (cost=0.28..4.34 rows=3 width=16) (actual time=0.002..0.003 rows=4 loops=529,127)

  • Index Cond: ("id_РМИС" = (t."Ид отделения РМИС")::bigint)
  • Heap Fetches: 0
36. 529.127 5,820.397 ↑ 1.0 1 529,127

Limit (cost=0.43..12.01 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=529,127)

37. 5,291.270 5,291.270 ↑ 1.0 1 529,127

Index Scan using idx_tmp_tmp_for_loading_napr_idrmisycheiki on tmp_for_loading_napr tt (cost=0.43..12.01 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=529,127)

  • Index Cond: (("id ячейки РМИС")::text = (t."id ячейки РМИС")::text)
  • Filter: ((timetablegraf_id IS NOT NULL) AND (isload = 2))
  • Rows Removed by Filter: 0
Planning time : 1.318 ms
Execution time : 1,245,776.484 ms