explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CxiR

Settings
# exclusive inclusive rows x rows loops node
1. 287.248 55,549.789 ↓ 10,000.0 10,000 1

Limit (cost=852,027.74..1,791,509.37 rows=1 width=207) (actual time=12,185.151..55,549.789 rows=10,000 loops=1)

  • Buffers: shared hit=3752526 read=1
2. 15,483.374 55,262.541 ↓ 10,000.0 10,000 1

Nested Loop (cost=852,027.74..1,791,509.37 rows=1 width=207) (actual time=12,185.149..55,262.541 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=3283247
3. 33.215 10,573.791 ↓ 10,624.0 10,624 1

Merge Join (cost=852,027.74..1,790,344.81 rows=1 width=176) (actual time=10,512.126..10,573.791 rows=10,624 loops=1)

  • Buffers: shared hit=212911
4. 6.497 10,533.642 ↓ 708.3 10,624 1

Nested Loop (cost=852,026.05..2,131,543.16 rows=15 width=193) (actual time=10,512.092..10,533.642 rows=10,624 loops=1)

  • Buffers: shared hit=212910
5. 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
6. 0.000 10,527.135 ↓ 3,541.3 10,624 1

Materialize (cost=852,025.92..2,131,530.77 rows=3 width=184) (actual time=10,512.08..10,527.135 rows=10,624 loops=1)

  • Buffers: shared hit=212908
7. 220.977 10,803.834 ↓ 3,541.3 10,624 1

Gather (cost=852,025.92..2,131,530.76 rows=3 width=184) (actual time=10,512.073..10,803.834 rows=10,624 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=682187 read=1
8. 4.898 10,582.857 ↓ 3,736.0 3,736 3 / 3

Nested Loop (cost=851,025.92..2,130,530.46 rows=1 width=184) (actual time=10,504.409..10,582.857 rows=3,736 loops=3)

  • Buffers: shared hit=682187 read=1
9. 43.696 10,570.487 ↓ 3,736.0 3,736 3 / 3

Hash Join (cost=851,025.48..2,130,529.92 rows=1 width=159) (actual time=10,504.38..10,570.487 rows=3,736 loops=3)

  • Buffers: shared hit=637298
10. 12.016 35.137 ↑ 221.0 59,816 3 / 3

Hash Join (cost=70.76..1,081,300.07 rows=13,218,337 width=24) (actual time=0.42..35.137 rows=59,816 loops=3)

  • Buffers: shared hit=4154
11. 22.735 22.735 ↑ 298.5 60,457 3 / 3

Seq Scan on incident i (cost=0..1,033,626.52 rows=18,043,899 width=16) (actual time=0.013..22.735 rows=60,457 loops=3)

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

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

  • Buffers: shared hit=104
13. 0.219 0.268 ↓ 1.3 783 3 / 3

Bitmap Heap Scan on contract_protectionservice cps (cost=18.32..63.02 rows=619 width=12) (actual time=0.059..0.268 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.049 0.049 ↑ 1.0 785 3 / 3

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

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

Hash (cost=812,089.99..812,089.99 rows=2,220,842 width=159) (actual time=10,491.654..10,491.654 rows=3,194,765 loops=3)

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

Hash Join (cost=3,054.42..812,089.99 rows=2,220,842 width=159) (actual time=28.747..8,154.516 rows=3,194,765 loops=3)

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

Hash Join (cost=3,053.12..804,358.46 rows=2,220,842 width=159) (actual time=28.705..7,368.676 rows=3,194,765 loops=3)

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

Hash Join (cost=3,039.5..792,670.14 rows=2,220,842 width=130) (actual time=28.57..6,557.937 rows=3,215,163 loops=3)

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

Hash Join (cost=1,709.73..785,510.02 rows=2,220,842 width=107) (actual time=15.805..5,664.499 rows=3,215,163 loops=3)

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

Hash Join (cost=1,674.36..779,612.93 rows=2,220,842 width=99) (actual time=15.518..4,911.737 rows=3,215,163 loops=3)

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

Hash Join (cost=170.85..771,451.54 rows=2,536,044 width=91) (actual time=1.508..4,070.656 rows=3,651,700 loops=3)

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

Hash Join (cost=1.15..762,873.11 rows=3,200,138 width=75) (actual time=0.03..3,172.222 rows=3,926,611 loops=3)

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

Seq Scan on link l (cost=0..732,970.67 rows=8,000,346 width=79) (actual time=0.006..2,197.257 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.497 1.437 ↑ 1.0 3,372 3 / 3

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Buffers: shared hit=15
35. 0.053 0.053 ↑ 1.0 345 3 / 3

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

  • Buffers: shared hit=15
36. 0.008 0.022 ↑ 1.0 13 3 / 3

Hash (cost=1.13..1.13 rows=13 width=4) (actual time=0.022..0.022 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.013..0.014 rows=13 loops=3)

  • Buffers: shared hit=3
38. 7.472 7.472 ↑ 1.0 1 11,208 / 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=11,208)

  • Index Cond: (l.linkid = mr.linkid)
  • Buffers: shared hit=44889 read=1
39. 6.927 6.934 ↓ 4,780.6 95,611 1

Sort (cost=1.68..1.73 rows=20 width=20) (actual time=0.019..6.934 rows=95,611 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.003..0.007 rows=20 loops=1)

  • Filter: (cad.cadence > 0)
  • Buffers: shared hit=1
41. 29,205.376 29,205.376 ↑ 1.0 25,421 10,624

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

  • Filter: (NOT cpsd.deleted)
  • Buffers: shared hit=3070336
Planning time : 29.2 ms
Execution time : 55,553.217 ms