explain.depesz.com

PostgreSQL's explain analyze made readable

Result: U8QJ

Settings
# exclusive inclusive rows x rows loops node
1. 118.305 27,487.111 ↓ 1,000.0 1,000 1

Limit (cost=832,262.76..1,760,770.67 rows=1 width=207) (actual time=23,133.761..27,487.111 rows=1,000 loops=1)

  • Buffers: shared hit=1683920
2. 6.865 27,368.806 ↓ 1,000.0 1,000 1

Merge Join (cost=832,262.76..1,760,770.67 rows=1 width=207) (actual time=23,133.76..27,368.806 rows=1,000 loops=1)

  • Buffers: shared hit=1683920
3. 0.386 27,361.344 ↓ 250.0 1,000 1

Nested Loop (cost=832,261.08..2,070,269.97 rows=4 width=181) (actual time=23,133.705..27,361.344 rows=1,000 loops=1)

  • Buffers: shared hit=1683919
4. 0.011 0.011 ↑ 5.0 1 1

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

  • Buffers: shared hit=2
5. 1.318 27,360.947 ↓ 1,000.0 1,000 1

Materialize (cost=832,260.95..2,070,257.7 rows=1 width=172) (actual time=23,133.692..27,360.947 rows=1,000 loops=1)

  • Buffers: shared hit=1683917
6. 1,424.088 27,359.629 ↓ 1,000.0 1,000 1

Nested Loop (cost=832,260.95..2,070,257.69 rows=1 width=172) (actual time=23,133.686..27,359.629 rows=1,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=1683917
7. 2.310 23,185.541 ↓ 1,000.0 1,000 1

Nested Loop (cost=832,260.95..2,069,029.57 rows=1 width=184) (actual time=23,129.161..23,185.541 rows=1,000 loops=1)

  • Buffers: shared hit=1394917
8. 3,011.687 23,179.231 ↓ 1,000.0 1,000 1

Nested Loop (cost=832,260.51..2,069,029 rows=1 width=159) (actual time=23,129.132..23,179.231 rows=1,000 loops=1)

  • Buffers: shared hit=1390917
9. 0.042 0.042 ↑ 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.033..0.042 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,213.480 20,167.502 ↓ 5,628,898.0 11,257,796 2

Materialize (cost=832,260.23..2,068,880.88 rows=2 width=151) (actual time=3,472.915..10,083.751 rows=11,257,796 loops=2)

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

Gather (cost=832,260.23..2,068,880.87 rows=2 width=151) (actual time=6,945.826..14,954.022 rows=22,239,689 loops=1)

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

Hash Join (cost=831,260.23..2,067,880.67 rows=1 width=151) (actual time=6,940.52..16,421.838 rows=7,413,230 loops=3)

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

Seq Scan on incident i (cost=0..1,033,626.52 rows=18,043,899 width=16) (actual time=0.013..4,568.553 rows=14,401,595 loops=3)

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

Hash (cost=806,054.75..806,054.75 rows=1,440,313 width=159) (actual time=6,934.005..6,934.005 rows=1,912,013 loops=3)

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

Hash Join (cost=3,054.42..806,054.75 rows=1,440,313 width=159) (actual time=29.523..5,625.246 rows=1,912,013 loops=3)

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

Hash Join (cost=3,053.12..801,040.07 rows=1,440,313 width=159) (actual time=29.485..5,158.059 rows=1,912,013 loops=3)

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

Hash Join (cost=3,039.5..793,454.89 rows=1,440,313 width=130) (actual time=29.354..4,669.515 rows=1,930,930 loops=3)

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

Hash Join (cost=1,709.73..788,343.88 rows=1,440,313 width=107) (actual time=16.624..4,119.318 rows=1,930,930 loops=3)

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

Hash Join (cost=1,674.36..784,506.93 rows=1,440,313 width=99) (actual time=16.349..3,667.735 rows=1,930,930 loops=3)

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

Hash Join (cost=170.85..778,685.49 rows=1,644,735 width=91) (actual time=1.534..3,136.095 rows=2,366,375 loops=3)

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

Hash Join (cost=1.15..773,062.36 rows=2,075,429 width=75) (actual time=0.035..2,552.453 rows=2,577,149 loops=3)

  • Buffers: shared hit=628883
22. 1,923.737 1,923.737 ↑ 1.2 4,235,655 3 / 3

Seq Scan on link l (cost=0..753,668.93 rows=5,188,573 width=79) (actual time=0.006..1,923.737 rows=4,235,655 loops=3)

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

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

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

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

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

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

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

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

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

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

  • Buffers: shared hit=2202
28. 10.092 10.092 ↓ 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..10.092 rows=32,186 loops=3)

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

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

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

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

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

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

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

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

  • Buffers: shared hit=1674
33. 0.067 0.116 ↑ 1.0 345 3 / 3

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

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

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

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

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

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

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

  • Buffers: shared hit=3
37. 4.000 4.000 ↑ 1.0 1 1,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=1,000)

  • Index Cond: (l.linkid = mr.linkid)
  • Buffers: shared hit=4000
38. 2,750.000 2,750.000 ↑ 1.0 25,421 1,000

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

  • Filter: (NOT cpsd.deleted)
  • Buffers: shared hit=289000
39. 0.590 0.597 ↓ 449.8 8,995 1

Sort (cost=1.68..1.73 rows=20 width=20) (actual time=0.023..0.597 rows=8,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.808 ms
Execution time : 28,455.031 ms