explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Y6fP

Settings
# exclusive inclusive rows x rows loops node
1. 99.571 67,155.769 ↓ 10,000.0 10,000 1

Limit (cost=832,454.22..1,761,219.8 rows=1 width=207) (actual time=22,955.974..67,155.769 rows=10,000 loops=1)

  • Buffers: shared hit=4320974
2. 68.957 67,056.198 ↓ 10,000.0 10,000 1

Merge Join (cost=832,454.22..1,761,219.8 rows=1 width=207) (actual time=22,955.973..67,056.198 rows=10,000 loops=1)

  • Buffers: shared hit=4320974
3. 4.069 66,981.126 ↓ 2,500.0 10,000 1

Nested Loop (cost=832,452.54..2,070,804.98 rows=4 width=181) (actual time=22,955.913..66,981.126 rows=10,000 loops=1)

  • Buffers: shared hit=4320973
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.01..0.01 rows=1 loops=1)

  • Buffers: shared hit=2
5. 13.223 66,977.047 ↓ 10,000.0 10,000 1

Materialize (cost=832,452.41..2,070,792.72 rows=1 width=172) (actual time=22,955.901..66,977.047 rows=10,000 loops=1)

  • Buffers: shared hit=4320971
6. 14,319.652 66,963.824 ↓ 10,000.0 10,000 1

Nested Loop (cost=832,452.41..2,070,792.71 rows=1 width=172) (actual time=22,955.895..66,963.824 rows=10,000 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=4320971
7. 28.017 24,864.172 ↓ 10,000.0 10,000 1

Nested Loop (cost=832,452.41..2,069,564.59 rows=1 width=184) (actual time=22,951.355..24,864.172 rows=10,000 loops=1)

  • Buffers: shared hit=1430971
8. 4,299.548 24,796.155 ↓ 10,000.0 10,000 1

Nested Loop (cost=832,451.97..2,069,564.02 rows=1 width=159) (actual time=22,951.33..24,796.155 rows=10,000 loops=1)

  • Buffers: shared hit=1390917
9. 0.037 0.037 ↑ 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.032..0.037 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,590.402 20,496.570 ↓ 8,115,983.0 16,231,966 2

Materialize (cost=832,451.69..2,069,415.9 rows=2 width=151) (actual time=3,461.528..10,248.285 rows=16,231,966 loops=2)

  • Buffers: shared hit=1390914
11. 0.000 14,906.168 ↓ 11,119,844.5 22,239,689 1

Gather (cost=832,451.69..2,069,415.89 rows=2 width=151) (actual time=6,923.051..14,906.168 rows=22,239,689 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=1390914
12. 4,886.317 16,294.218 ↓ 7,413,230.0 7,413,230 3 / 3

Hash Join (cost=831,451.69..2,068,415.69 rows=1 width=151) (actual time=6,917.86..16,294.218 rows=7,413,230 loops=3)

  • Buffers: shared hit=1390914
13. 4,496.405 4,496.405 ↑ 1.3 14,401,595 3 / 3

Seq Scan on incident i (cost=0..1,033,648.88 rows=18,072,449 width=16) (actual time=0.01..4,496.405 rows=14,401,595 loops=3)

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

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

  • Buffers: shared hit=633086
15. 460.860 5,628.572 ↓ 1.3 1,912,013 3 / 3

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

  • Buffers: shared hit=633086
16. 486.452 5,167.691 ↓ 1.3 1,912,013 3 / 3

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

  • Buffers: shared hit=633083
17. 532.425 4,681.119 ↓ 1.3 1,930,930 3 / 3

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

  • Buffers: shared hit=633068
18. 462.577 4,136.002 ↓ 1.3 1,930,930 3 / 3

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

  • Buffers: shared hit=631394
19. 514.778 3,673.157 ↓ 1.3 1,930,930 3 / 3

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

  • Buffers: shared hit=631340
20. 579.630 3,144.635 ↓ 1.4 2,366,375 3 / 3

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

  • Buffers: shared hit=629138
21. 640.955 2,563.546 ↓ 1.2 2,577,149 3 / 3

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

  • Buffers: shared hit=628883
22. 1,922.578 1,922.578 ↑ 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,922.578 rows=4,235,655 loops=3)

  • Filter: ((NOT l.deleted) AND (l.monitorexecutiontypeid <> 3) AND (l.linktypeid = 1))
  • Buffers: shared hit=628880
23. 0.004 0.013 ↑ 1.0 4 3 / 3

Hash (cost=1.1..1.1 rows=4 width=4) (actual time=0.013..0.013 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.506 1.459 ↑ 1.0 3,372 3 / 3

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Buffers: shared hit=1674
32. 6.779 6.779 ↑ 1.0 34,301 3 / 3

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

  • Buffers: shared hit=1674
33. 0.066 0.120 ↑ 1.0 345 3 / 3

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

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

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

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

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

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

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

  • Buffers: shared hit=3
37. 40.000 40.000 ↑ 1.0 1 10,000

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

  • Index Cond: (l.linkid = mr.linkid)
  • Buffers: shared hit=40054
38. 27,780.000 27,780.000 ↑ 1.0 25,421 10,000

Seq Scan on contract_protectionservice__domain cpsd (cost=0..592.42 rows=25,428 width=12) (actual time=0.001..2.778 rows=25,421 loops=10,000)

  • Filter: (NOT cpsd.deleted)
  • Buffers: shared hit=2890000
39. 6.108 6.115 ↓ 4,499.8 89,995 1

Sort (cost=1.68..1.73 rows=20 width=20) (actual time=0.029..6.115 rows=89,995 loops=1)

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

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

  • Filter: (cad.cadence > 0)
  • Buffers: shared hit=1
Planning time : 31.382 ms
Execution time : 68,098.535 ms