explain.depesz.com

PostgreSQL's explain analyze made readable

Result: X5cZ : asd

Settings
# exclusive inclusive rows x rows loops node
1. 310.970 87,824.376 ↑ 1,339,138,231,841.7 128,698 1

Merge Join (cost=412,128.77..2,585,166,362,053,465.50 rows=172,344,412,161,562,080 width=328) (actual time=85,377.037..87,824.376 rows=128,698 loops=1)

  • Merge Cond: (x.external_id = x1.external_id)
2.          

CTE v_alarms

3. 1,327.217 1,327.230 ↓ 1.5 125,714 1

Seq Scan on alarms (cost=11.98..76,002.42 rows=83,720 width=195) (actual time=0.240..1,327.230 rows=125,714 loops=1)

  • Filter: (((emergency_reason)::text <> '8.0'::text) AND (NOT (hashed SubPlan 1)) AND (COALESCE(btrim((iin)::text), ''::text) <> ''::text))
  • Rows Removed by Filter: 43,479
4.          

SubPlan (for Seq Scan)

5. 0.013 0.013 ↓ 0.0 0 1

Seq Scan on foms_integration_result (cost=0.00..11.75 rows=93 width=4) (actual time=0.012..0.013 rows=0 loops=1)

  • Filter: (is_migrated OR (retry_count >= 5))
6.          

CTE v_alarms_secondary

7. 78,479.396 79,365.484 ↓ 1.5 125,714 1

Hash Join (cost=48,277.26..136,180.44 rows=83,720 width=61) (actual time=716.378..79,365.484 rows=125,714 loops=1)

  • Hash Cond: (x1_1.external_id = x_1.alarm_id)
8. 173.538 173.538 ↓ 1.5 125,714 1

CTE Scan on v_alarms x1_1 (cost=0.00..1,674.40 rows=83,720 width=16) (actual time=0.043..173.538 rows=125,714 loops=1)

9. 172.537 712.550 ↑ 1.0 166,683 1

Hash (cost=44,715.56..44,715.56 rows=167,256 width=41) (actual time=712.550..712.550 rows=166,683 loops=1)

  • Buckets: 65,536 Batches: 4 Memory Usage: 3,473kB
10. 540.013 540.013 ↑ 1.0 166,683 1

Seq Scan on alarms_secondary x_1 (cost=0.00..44,715.56 rows=167,256 width=41) (actual time=0.104..540.013 rows=166,683 loops=1)

11.          

CTE v_profile

12. 221.056 348.031 ↓ 21.5 125,714 1

Hash Join (cost=1.32..1,900.14 rows=5,860 width=12) (actual time=0.180..348.031 rows=125,714 loops=1)

  • Hash Cond: ((x_2.required_profile)::text = (x1_2.profile)::text)
13. 126.920 126.920 ↓ 1.5 125,714 1

CTE Scan on v_alarms x_2 (cost=0.00..1,674.40 rows=83,720 width=16) (actual time=0.017..126.920 rows=125,714 loops=1)

14. 0.018 0.055 ↑ 1.0 14 1

Hash (cost=1.14..1.14 rows=14 width=16) (actual time=0.055..0.055 rows=14 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
15. 0.037 0.037 ↑ 1.0 14 1

Seq Scan on dic_brigade_profiles x1_2 (cost=0.00..1.14 rows=14 width=16) (actual time=0.027..0.037 rows=14 loops=1)

16.          

CTE v_result

17. 197.926 613.770 ↑ 7.4 128,698 1

Merge Join (cost=8,678.39..27,610.41 rows=946,036 width=12) (actual time=308.053..613.770 rows=128,698 loops=1)

  • Merge Cond: (x1_3.dic_aid_result_id = x_3.aid_result)
18. 0.136 0.176 ↑ 61.1 37 1

Sort (cost=158.51..164.16 rows=2,260 width=8) (actual time=0.141..0.176 rows=37 loops=1)

  • Sort Key: x1_3.dic_aid_result_id
  • Sort Method: quicksort Memory: 26kB
19. 0.040 0.040 ↑ 61.1 37 1

Seq Scan on foms_result_dic_aid_result_mapping x1_3 (cost=0.00..32.60 rows=2,260 width=8) (actual time=0.018..0.040 rows=37 loops=1)

20. 299.600 415.668 ↓ 1.5 128,699 1

Sort (cost=8,519.88..8,729.18 rows=83,720 width=16) (actual time=307.896..415.668 rows=128,699 loops=1)

  • Sort Key: x_3.aid_result
  • Sort Method: external sort Disk: 3,696kB
21. 116.068 116.068 ↓ 1.5 125,714 1

CTE Scan on v_alarms_secondary x_3 (cost=0.00..1,674.40 rows=83,720 width=16) (actual time=0.058..116.068 rows=125,714 loops=1)

22.          

CTE v_hospi_mo_id

23. 209.196 80,122.544 ↑ 1.2 31,191 1

Hash Join (cost=4.03..1,902.85 rows=37,674 width=16) (actual time=718.401..80,122.544 rows=31,191 loops=1)

  • Hash Cond: (x1_4.hospital = x_4.hospital_id)
24. 79,913.147 79,913.147 ↓ 1.5 125,714 1

CTE Scan on v_alarms_secondary x1_4 (cost=0.00..1,674.40 rows=83,720 width=16) (actual time=716.385..79,913.147 rows=125,714 loops=1)

25. 0.111 0.201 ↑ 1.0 90 1

Hash (cost=2.90..2.90 rows=90 width=16) (actual time=0.200..0.201 rows=90 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
26. 0.090 0.090 ↑ 1.0 90 1

Seq Scan on dic_hospitals x_4 (cost=0.00..2.90 rows=90 width=16) (actual time=0.018..0.090 rows=90 loops=1)

27.          

CTE v_receiver_ter_mo

28. 199.835 321.919 ↓ 3.3 123,027 1

Hash Join (cost=4.03..1,902.85 rows=37,674 width=16) (actual time=0.248..321.919 rows=123,027 loops=1)

  • Hash Cond: (x1_5.policlinic = x_5.hospital_id)
29. 121.908 121.908 ↓ 1.5 125,714 1

CTE Scan on v_alarms x1_5 (cost=0.00..1,674.40 rows=83,720 width=16) (actual time=0.029..121.908 rows=125,714 loops=1)

30. 0.076 0.176 ↑ 1.0 90 1

Hash (cost=2.90..2.90 rows=90 width=16) (actual time=0.175..0.176 rows=90 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
31. 0.100 0.100 ↑ 1.0 90 1

Seq Scan on dic_hospitals x_5 (cost=0.00..2.90 rows=90 width=16) (actual time=0.024..0.100 rows=90 loops=1)

32. 273.218 83,745.111 ↑ 23,630.3 125,714 1

Merge Left Join (cost=24,339.28..44,860,611.64 rows=2,970,658,192 width=232) (actual time=83,019.000..83,745.111 rows=125,714 loops=1)

  • Merge Cond: (x.external_id = x5.external_id)
33. 213.848 82,602.353 ↑ 125.4 125,714 1

Merge Left Join (cost=20,722.33..257,695.97 rows=15,770,336 width=224) (actual time=82,252.653..82,602.353 rows=125,714 loops=1)

  • Merge Cond: (x.external_id = x4.alarm_id)
34. 385.578 2,109.870 ↓ 1.5 125,714 1

Sort (cost=17,105.38..17,314.68 rows=83,720 width=216) (actual time=1,997.978..2,109.870 rows=125,714 loops=1)

  • Sort Key: x.external_id
  • Sort Method: external merge Disk: 17,240kB
35. 1,724.292 1,724.292 ↓ 1.5 125,714 1

CTE Scan on v_alarms x (cost=0.00..1,674.40 rows=83,720 width=216) (actual time=0.246..1,724.292 rows=125,714 loops=1)

36. 89.083 80,278.635 ↑ 1.2 31,191 1

Sort (cost=3,616.95..3,711.13 rows=37,674 width=16) (actual time=80,254.659..80,278.635 rows=31,191 loops=1)

  • Sort Key: x4.alarm_id
  • Sort Method: quicksort Memory: 2,231kB
37. 80,189.552 80,189.552 ↑ 1.2 31,191 1

CTE Scan on v_hospi_mo_id x4 (cost=0.00..753.48 rows=37,674 width=16) (actual time=718.407..80,189.552 rows=31,191 loops=1)

38. 333.851 869.540 ↓ 3.3 123,027 1

Sort (cost=3,616.95..3,711.13 rows=37,674 width=16) (actual time=766.332..869.540 rows=123,027 loops=1)

  • Sort Key: x5.external_id
  • Sort Method: external sort Disk: 3,360kB
39. 535.689 535.689 ↓ 3.3 123,027 1

CTE Scan on v_receiver_ter_mo x5 (cost=0.00..753.48 rows=37,674 width=16) (actual time=0.253..535.689 rows=123,027 loops=1)

40. 184.971 3,768.295 ↑ 90,157.7 128,698 1

Materialize (cost=142,290.38..203,238,525.14 rows=11,603,112,703 width=120) (actual time=2,358.029..3,768.295 rows=128,698 loops=1)

41. 300.796 3,583.324 ↑ 90,157.7 128,698 1

Merge Join (cost=142,290.38..174,230,743.39 rows=11,603,112,703 width=120) (actual time=2,358.021..3,583.324 rows=128,698 loops=1)

  • Merge Cond: (x2.alarm_id = x1.external_id)
42. 342.756 1,197.165 ↑ 7.4 128,698 1

Sort (cost=128,992.06..131,357.15 rows=946,036 width=12) (actual time=1,106.305..1,197.165 rows=128,698 loops=1)

  • Sort Key: x2.alarm_id
  • Sort Method: external merge Disk: 3,280kB
43. 854.409 854.409 ↑ 7.4 128,698 1

CTE Scan on v_result x2 (cost=0.00..18,920.72 rows=946,036 width=12) (actual time=308.060..854.409 rows=128,698 loops=1)

44. 177.597 2,085.363 ↑ 19.0 128,801 1

Materialize (cost=13,298.32..56,464.35 rows=2,452,996 width=108) (actual time=1,251.703..2,085.363 rows=128,801 loops=1)

45. 297.390 1,907.766 ↑ 19.5 125,714 1

Merge Join (cost=13,298.32..50,331.86 rows=2,452,996 width=108) (actual time=1,251.694..1,907.766 rows=125,714 loops=1)

  • Merge Cond: (x1.external_id = x3.alarm_id)
46. 313.826 898.093 ↓ 21.5 125,714 1

Sort (cost=483.94..498.59 rows=5,860 width=12) (actual time=811.008..898.093 rows=125,714 loops=1)

  • Sort Key: x1.external_id
  • Sort Method: external merge Disk: 3,208kB
47. 584.267 584.267 ↓ 21.5 125,714 1

CTE Scan on v_profile x1 (cost=0.00..117.20 rows=5,860 width=12) (actual time=0.186..584.267 rows=125,714 loops=1)

48. 159.077 712.283 ↓ 1.5 125,714 1

Materialize (cost=12,814.38..13,232.98 rows=83,720 width=96) (actual time=440.670..712.283 rows=125,714 loops=1)

49. 406.969 553.206 ↓ 1.5 125,714 1

Sort (cost=12,814.38..13,023.68 rows=83,720 width=96) (actual time=440.660..553.206 rows=125,714 loops=1)

  • Sort Key: x3.alarm_id
  • Sort Method: external merge Disk: 6,520kB
50. 146.237 146.237 ↓ 1.5 125,714 1

CTE Scan on v_alarms_secondary x3 (cost=0.00..1,674.40 rows=83,720 width=96) (actual time=0.019..146.237 rows=125,714 loops=1)

Planning time : 6.399 ms
Execution time : 87,930.290 ms