explain.depesz.com

PostgreSQL's explain analyze made readable

Result: epK7

Settings
# exclusive inclusive rows x rows loops node
1. 280.004 54,500.029 ↓ 10,000.0 10,000 1

Limit (cost=851,817.52..1,791,299.15 rows=1 width=207) (actual time=11,298.04..54,500.029 rows=10,000 loops=1)

  • Buffers: shared hit=3672978
2. 15,324.273 54,220.025 ↓ 10,000.0 10,000 1

Nested Loop (cost=851,817.52..1,791,299.15 rows=1 width=207) (actual time=11,298.039..54,220.025 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=3201823
3. 32.568 10,489.864 ↓ 10,352.0 10,352 1

Merge Join (cost=851,817.52..1,790,134.59 rows=1 width=176) (actual time=10,430.113..10,489.864 rows=10,352 loops=1)

  • Buffers: shared hit=210095
4. 6.085 10,450.396 ↓ 690.1 10,352 1

Nested Loop (cost=851,815.83..2,131,332.94 rows=15 width=193) (actual time=10,430.078..10,450.396 rows=10,352 loops=1)

  • Buffers: shared hit=210094
5. 0.012 0.012 ↑ 5.0 1 1

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

  • Buffers: shared hit=2
6. 0.000 10,444.299 ↓ 3,450.7 10,352 1

Materialize (cost=851,815.7..2,131,320.55 rows=3 width=184) (actual time=10,430.064..10,444.299 rows=10,352 loops=1)

  • Buffers: shared hit=210092
7. 214.349 10,714.283 ↓ 3,450.7 10,352 1

Gather (cost=851,815.7..2,131,320.54 rows=3 width=184) (actual time=10,430.059..10,714.283 rows=10,352 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=681247
8. 4.430 10,499.934 ↓ 3,654.0 3,654 3 / 3

Nested Loop (cost=850,815.7..2,130,320.24 rows=1 width=184) (actual time=10,421.757..10,499.934 rows=3,654 loops=3)

  • Buffers: shared hit=681247
9. 43.817 10,488.195 ↓ 3,654.0 3,654 3 / 3

Hash Join (cost=850,815.26..2,130,319.7 rows=1 width=159) (actual time=10,421.729..10,488.195 rows=3,654 loops=3)

  • Buffers: shared hit=637333
10. 11.866 35.605 ↑ 226.7 58,295 3 / 3

Hash Join (cost=70.76..1,081,300.07 rows=13,218,337 width=24) (actual time=0.431..35.605 rows=58,295 loops=3)

  • Buffers: shared hit=4189
11. 23.345 23.345 ↑ 306.8 58,808 3 / 3

Seq Scan on incident i (cost=0..1,033,626.52 rows=18,043,899 width=16) (actual time=0.014..23.345 rows=58,808 loops=3)

  • Filter: ((NOT i.deleted) AND (i.incidentstatusid <> ALL ('{4,11,17}'::integer[])))
  • Buffers: shared hit=4085
12. 0.120 0.394 ↓ 1.3 783 3 / 3

Hash (cost=63.02..63.02 rows=619 width=12) (actual time=0.394..0.394 rows=783 loops=3)

  • Buffers: shared hit=104
13. 0.227 0.274 ↓ 1.3 783 3 / 3

Bitmap Heap Scan on contract_protectionservice cps (cost=18.32..63.02 rows=619 width=12) (actual time=0.057..0.274 rows=783 loops=3)

  • Filter: ((NOT cps.deleted) AND ((cps.domainusageid = 1) OR (cps.domainusageid = 2) OR (cps.domainusageid = 3)))
  • Heap Blocks: exact=29
  • Buffers: shared hit=104
14. 0.047 0.047 ↑ 1.0 785 3 / 3

Bitmap Index Scan on idx_contract_protectionservice_protectionservice (cost=0..18.17 rows=785 width=0) (actual time=0.047..0.047 rows=785 loops=3)

  • Index Cond: (cps.protectionserviceid = 2)
  • Buffers: shared hit=17
15. 2,321.214 10,408.773 ↓ 1.4 3,194,765 3 / 3

Hash (cost=811,917.08..811,917.08 rows=2,218,710 width=159) (actual time=10,408.772..10,408.773 rows=3,194,765 loops=3)

  • Buffers: shared hit=633086
16. 790.660 8,087.559 ↓ 1.4 3,194,765 3 / 3

Hash Join (cost=3,054.42..811,917.08 rows=2,218,710 width=159) (actual time=29.587..8,087.559 rows=3,194,765 loops=3)

  • Buffers: shared hit=633086
17. 807.923 7,296.879 ↓ 1.4 3,194,765 3 / 3

Hash Join (cost=3,053.12..804,192.97 rows=2,218,710 width=159) (actual time=29.547..7,296.879 rows=3,194,765 loops=3)

  • Buffers: shared hit=633083
18. 871.998 6,488.836 ↓ 1.4 3,215,163 3 / 3

Hash Join (cost=3,039.5..792,515.85 rows=2,218,710 width=130) (actual time=29.411..6,488.836 rows=3,215,163 loops=3)

  • Buffers: shared hit=633068
19. 741.527 5,604.270 ↓ 1.4 3,215,163 3 / 3

Hash Join (cost=1,709.73..785,361.33 rows=2,218,710 width=107) (actual time=16.602..5,604.27 rows=3,215,163 loops=3)

  • Buffers: shared hit=631394
20. 817.899 4,862.487 ↓ 1.4 3,215,163 3 / 3

Hash Join (cost=1,674.36..779,469.88 rows=2,218,710 width=99) (actual time=16.327..4,862.487 rows=3,215,163 loops=3)

  • Buffers: shared hit=631340
21. 885.869 4,029.933 ↓ 1.4 3,651,700 3 / 3

Hash Join (cost=170.85..771,314.88 rows=2,533,610 width=91) (actual time=1.517..4,029.933 rows=3,651,700 loops=3)

  • Buffers: shared hit=629138
22. 965.422 3,142.618 ↓ 1.2 3,926,611 3 / 3

Hash Join (cost=1.15..762,744.52 rows=3,197,067 width=75) (actual time=0.029..3,142.618 rows=3,926,611 loops=3)

  • Buffers: shared hit=628883
23. 2,177.187 2,177.187 ↑ 1.2 6,406,078 3 / 3

Seq Scan on link l (cost=0..732,870.77 rows=7,992,668 width=79) (actual time=0.006..2,177.187 rows=6,406,078 loops=3)

  • Filter: ((NOT l.deleted) AND (l.monitorexecutiontypeid <> 3))
  • Buffers: shared hit=628880
24. 0.003 0.009 ↑ 1.0 4 3 / 3

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

  • Buffers: shared hit=3
25. 0.006 0.006 ↑ 1.0 4 3 / 3

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

  • Filter: es.monitorizable
  • Buffers: shared hit=3
26. 0.498 1.446 ↑ 1.0 3,372 3 / 3

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

  • Buffers: shared hit=255
27. 0.948 0.948 ↑ 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.948 rows=3,372 loops=3)

  • Filter: a.active
  • Buffers: shared hit=255
28. 4.630 14.655 ↓ 1.0 32,186 3 / 3

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

  • Buffers: shared hit=2202
29. 10.025 10.025 ↓ 1.0 32,186 3 / 3

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

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

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

  • Buffers: shared hit=54
31. 0.158 0.158 ↑ 1.0 772 3 / 3

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

  • Buffers: shared hit=54
32. 5.798 12.568 ↑ 1.0 34,301 3 / 3

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

  • Buffers: shared hit=1674
33. 6.770 6.770 ↑ 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.77 rows=34,301 loops=3)

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

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

  • Buffers: shared hit=15
35. 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
36. 0.006 0.020 ↑ 1.0 13 3 / 3

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

  • Buffers: shared hit=3
37. 0.014 0.014 ↑ 1.0 13 3 / 3

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

  • Buffers: shared hit=3
38. 7.309 7.309 ↑ 1.0 1 10,963 / 3

Index Scan using idx_monitorrequest_link on monitorrequest mr (cost=0.44..0.53 rows=1 width=29) (actual time=0.002..0.002 rows=1 loops=10,963)

  • Index Cond: (l.linkid = mr.linkid)
  • Buffers: shared hit=43914
39. 6.893 6.900 ↓ 4,658.1 93,163 1

Sort (cost=1.68..1.73 rows=20 width=20) (actual time=0.019..6.9 rows=93,163 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
41. 28,405.888 28,405.888 ↑ 1.0 25,421 10,352

Seq Scan on contract_protectionservice__domain cpsd (cost=0..592.42 rows=25,428 width=12) (actual time=0.002..2.744 rows=25,421 loops=10,352)

  • Filter: (NOT cpsd.deleted)
  • Buffers: shared hit=2991728
Planning time : 29.054 ms
Execution time : 54,503.179 ms