explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UZlg

Settings
# exclusive inclusive rows x rows loops node
1. 119.410 23,253.478 ↑ 1.0 1 1

Limit (cost=832,454.22..1,760,851.87 rows=1 width=207) (actual time=23,134.069..23,253.478 rows=1 loops=1)

  • Buffers: shared hit=1391210 read=5
2. 0.112 23,134.068 ↑ 1.0 1 1

Merge Join (cost=832,454.22..1,760,851.87 rows=1 width=207) (actual time=23,134.068..23,134.068 rows=1 loops=1)

  • Buffers: shared hit=1391210 read=5
3. 0.002 23,132.559 ↑ 4.0 1 1

Nested Loop (cost=832,452.54..2,070,314.41 rows=4 width=181) (actual time=23,132.559..23,132.559 rows=1 loops=1)

  • Buffers: shared hit=1391210 read=4
4. 0.010 0.010 ↑ 5.0 1 1

Index Scan using pk_timeunit on timeunit tu (cost=0.13..12.21 rows=5 width=9) (actual time=0.009..0.01 rows=1 loops=1)

  • Buffers: shared hit=2
5. 0.007 23,132.547 ↑ 1.0 1 1

Materialize (cost=832,452.41..2,070,302.15 rows=1 width=172) (actual time=23,132.547..23,132.547 rows=1 loops=1)

  • Buffers: shared hit=1391208 read=4
6. 1.436 23,132.540 ↑ 1.0 1 1

Nested Loop (cost=832,452.41..2,070,302.14 rows=1 width=172) (actual time=23,132.54..23,132.54 rows=1 loops=1)

  • Filter: ((cps.domainusageid = 1) OR ((cps.domainusageid = 2) AND (cpsd.contractprotectionservicedomainid IS NOT NULL)) OR ((cps.domainusageid = 3) AND (cpsd.contractprotectionservicedomainid IS NULL)))
  • Buffers: shared hit=1391208 read=4
7. 0.005 23,128.014 ↑ 1.0 1 1

Nested Loop (cost=832,452.41..2,069,074.02 rows=1 width=184) (actual time=23,128.014..23,128.014 rows=1 loops=1)

  • Buffers: shared hit=1390919 read=4
8. 3,098.722 23,127.977 ↑ 1.0 1 1

Nested Loop (cost=832,451.97..2,069,073.45 rows=1 width=159) (actual time=23,127.977..23,127.977 rows=1 loops=1)

  • Buffers: shared hit=1390915 read=4
9. 0.043 0.043 ↑ 309.5 2 1

Index Scan using pk_contractprotectionservice on contract_protectionservice cps (cost=0.28..129.56 rows=619 width=12) (actual time=0.035..0.043 rows=2 loops=1)

  • Filter: ((NOT cps.deleted) AND (cps.protectionserviceid = 2) AND ((cps.domainusageid = 1) OR (cps.domainusageid = 2) OR (cps.domainusageid = 3)))
  • Buffers: shared hit=3
10. 5,409.907 20,029.212 ↓ 5,560,035.0 11,120,070 2

Materialize (cost=832,451.69..2,068,925.33 rows=2 width=151) (actual time=3,459.584..10,014.606 rows=11,120,070 loops=2)

  • Buffers: shared hit=1390912 read=4
11. 0.000 14,619.305 ↓ 11,119,844.5 22,239,689 1

Gather (cost=832,451.69..2,068,925.32 rows=2 width=151) (actual time=6,919.163..14,619.305 rows=22,239,689 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=1390912 read=4
12. 4,893.551 16,329.694 ↓ 7,413,230.0 7,413,230 3 / 3

Hash Join (cost=831,451.69..2,067,925.12 rows=1 width=151) (actual time=6,913.922..16,329.694 rows=7,413,230 loops=3)

  • Buffers: shared hit=1390912 read=4
13. 4,528.865 4,528.865 ↑ 1.3 14,401,595 3 / 3

Seq Scan on incident i (cost=0..1,033,541.82 rows=18,038,360 width=16) (actual time=0.014..4,528.865 rows=14,401,595 loops=3)

  • Filter: ((NOT i.deleted) AND (i.incidentstatusid <> ALL ('{4,11,17}'::integer[])))
  • Buffers: shared hit=757728
14. 1,301.927 6,907.278 ↓ 1.3 1,912,013 3 / 3

Hash (cost=806,222.01..806,222.01 rows=1,441,696 width=159) (actual time=6,907.278..6,907.278 rows=1,912,013 loops=3)

  • Buffers: shared hit=633081 read=4
15. 466.554 5,605.351 ↓ 1.3 1,912,013 3 / 3

Hash Join (cost=3,054.42..806,222.01 rows=1,441,696 width=159) (actual time=29.434..5,605.351 rows=1,912,013 loops=3)

  • Buffers: shared hit=633081 read=4
16. 490.412 5,138.779 ↓ 1.3 1,912,013 3 / 3

Hash Join (cost=3,053.12..801,202.5 rows=1,441,696 width=159) (actual time=29.392..5,138.779 rows=1,912,013 loops=3)

  • Buffers: shared hit=633078 read=4
17. 536.407 4,648.253 ↓ 1.3 1,930,930 3 / 3

Hash Join (cost=3,039.5..793,610.05 rows=1,441,696 width=130) (actual time=29.259..4,648.253 rows=1,930,930 loops=3)

  • Buffers: shared hit=633063 read=4
18. 449.822 4,099.350 ↓ 1.3 1,930,930 3 / 3

Hash Join (cost=1,709.73..788,495.42 rows=1,441,696 width=107) (actual time=16.521..4,099.35 rows=1,930,930 loops=3)

  • Buffers: shared hit=631391 read=3
19. 516.959 3,649.264 ↓ 1.3 1,930,930 3 / 3

Hash Join (cost=1,674.36..784,654.82 rows=1,441,696 width=99) (actual time=16.236..3,649.264 rows=1,930,930 loops=3)

  • Buffers: shared hit=631337 read=3
20. 576.991 3,117.847 ↓ 1.4 2,366,375 3 / 3

Hash Join (cost=170.85..778,829.24 rows=1,646,315 width=91) (actual time=1.623..3,117.847 rows=2,366,375 loops=3)

  • Buffers: shared hit=629135 read=3
21. 627.712 2,539.313 ↓ 1.2 2,577,149 3 / 3

Hash Join (cost=1.15..773,200.88 rows=2,077,423 width=75) (actual time=0.036..2,539.313 rows=2,577,149 loops=3)

  • Buffers: shared hit=628880 read=3
22. 1,911.586 1,911.586 ↑ 1.2 4,235,655 3 / 3

Seq Scan on link l (cost=0..753,788.8 rows=5,193,558 width=79) (actual time=0.006..1,911.586 rows=4,235,655 loops=3)

  • Filter: ((NOT l.deleted) AND (l.monitorexecutiontypeid <> 3) AND (l.linktypeid = 1))
  • Buffers: shared hit=628877 read=3
23. 0.006 0.015 ↑ 1.0 4 3 / 3

Hash (cost=1.1..1.1 rows=4 width=4) (actual time=0.014..0.015 rows=4 loops=3)

  • Buffers: shared hit=3
24. 0.009 0.009 ↑ 1.0 4 3 / 3

Seq Scan on enforcementstatus es (cost=0..1.1 rows=4 width=4) (actual time=0.008..0.009 rows=4 loops=3)

  • Filter: es.monitorizable
  • Buffers: shared hit=3
25. 0.503 1.543 ↑ 1.0 3,372 3 / 3

Hash (cost=127.55..127.55 rows=3,372 width=16) (actual time=1.542..1.543 rows=3,372 loops=3)

  • Buffers: shared hit=255
26. 1.040 1.040 ↑ 1.0 3,372 3 / 3

Seq Scan on asset a (cost=0..127.55 rows=3,372 width=16) (actual time=0.006..1.04 rows=3,372 loops=3)

  • Filter: a.active
  • Buffers: shared hit=255
27. 4.545 14.458 ↓ 1.0 32,186 3 / 3

Hash (cost=1,101.37..1,101.37 rows=32,171 width=8) (actual time=14.458..14.458 rows=32,186 loops=3)

  • Buffers: shared hit=2202
28. 9.913 9.913 ↓ 1.0 32,186 3 / 3

Seq Scan on domain d (cost=0..1,101.37 rows=32,171 width=8) (actual time=0.006..9.913 rows=32,186 loops=3)

  • Filter: (d.active AND (d.masterdomainid IS NULL))
  • Buffers: shared hit=2202
29. 0.100 0.264 ↑ 1.0 772 3 / 3

Hash (cost=25.72..25.72 rows=772 width=8) (actual time=0.264..0.264 rows=772 loops=3)

  • Buffers: shared hit=54
30. 0.164 0.164 ↑ 1.0 772 3 / 3

Seq Scan on customer cu (cost=0..25.72 rows=772 width=8) (actual time=0.009..0.164 rows=772 loops=3)

  • Buffers: shared hit=54
31. 5.656 12.496 ↑ 1.0 34,301 3 / 3

Hash (cost=901.01..901.01 rows=34,301 width=31) (actual time=12.496..12.496 rows=34,301 loops=3)

  • Buffers: shared hit=1672 read=1
32. 6.840 6.840 ↑ 1.0 34,301 3 / 3

Seq Scan on monitordomainconfiguration mdc (cost=0..901.01 rows=34,301 width=31) (actual time=0.006..6.84 rows=34,301 loops=3)

  • Buffers: shared hit=1672 read=1
33. 0.064 0.114 ↑ 1.0 345 3 / 3

Hash (cost=8.45..8.45 rows=345 width=45) (actual time=0.114..0.114 rows=345 loops=3)

  • Buffers: shared hit=15
34. 0.050 0.050 ↑ 1.0 345 3 / 3

Seq Scan on bot b (cost=0..8.45 rows=345 width=45) (actual time=0.01..0.05 rows=345 loops=3)

  • Buffers: shared hit=15
35. 0.006 0.018 ↑ 1.0 13 3 / 3

Hash (cost=1.13..1.13 rows=13 width=4) (actual time=0.018..0.018 rows=13 loops=3)

  • Buffers: shared hit=3
36. 0.012 0.012 ↑ 1.0 13 3 / 3

Seq Scan on obsolescence o (cost=0..1.13 rows=13 width=4) (actual time=0.01..0.012 rows=13 loops=3)

  • Buffers: shared hit=3
37. 0.032 0.032 ↑ 1.0 1 1

Index Scan using idx_monitorrequest_link on monitorrequest mr (cost=0.44..0.57 rows=1 width=29) (actual time=0.032..0.032 rows=1 loops=1)

  • Index Cond: (l.linkid = mr.linkid)
  • Buffers: shared hit=4
38. 3.090 3.090 ↑ 1.0 25,421 1

Seq Scan on contract_protectionservice__domain cpsd (cost=0..592.42 rows=25,428 width=12) (actual time=0.007..3.09 rows=25,421 loops=1)

  • Filter: (NOT cpsd.deleted)
  • Buffers: shared hit=289
39. 0.023 1.397 ↑ 5.0 4 1

Sort (cost=1.68..1.73 rows=20 width=20) (actual time=1.397..1.397 rows=4 loops=1)

  • Sort Key: cad.timeunitid
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared read=1
40. 1.374 1.374 ↑ 1.0 20 1

Seq Scan on cadence cad (cost=0..1.25 rows=20 width=20) (actual time=1.371..1.374 rows=20 loops=1)

  • Filter: (cad.cadence > 0)
  • Buffers: shared read=1
Planning time : 38.464 ms
Execution time : 24,215.04 ms