explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2scN

Settings
# exclusive inclusive rows x rows loops node
1. 0.892 1,041.492 ↓ 10,000.0 10,000 1

Limit (cost=2,771.69..2,771.73 rows=1 width=205) (actual time=1,035.386..1,041.492 rows=10,000 loops=1)

  • Buffers: shared hit=640115
2. 4.593 1,040.600 ↓ 10,000.0 10,000 1

Unique (cost=2,771.69..2,771.73 rows=1 width=205) (actual time=1,035.385..1,040.6 rows=10,000 loops=1)

  • Buffers: shared hit=640115
3. 41.423 1,036.007 ↓ 10,094.0 10,094 1

Sort (cost=2,771.69..2,771.7 rows=1 width=205) (actual time=1,035.383..1,036.007 rows=10,094 loops=1)

  • Sort Key: l.linkid, b.docker, l.assetid, l.domainid, i.contractid, cu.customerid, a.assettypeid, l.url, mr.nextcadenceid, (COALESCE(a.matchingthreshold, cu.matchingthreshold)), (trunc((((tu.hourequivalence * (cad.cadence)::numeric) / '2'::numeric) * '3600'::numeric), 0)), (to_char(mr.lastrequest, 'YYYY-MM-DD HH24:MI:SS'::text)), mdc.queue
  • Sort Method: quicksort Memory: 11250kB
  • Buffers: shared hit=640115
4. 174.134 994.584 ↓ 37,349.0 37,349 1

Merge Join (cost=1,009.4..2,771.68 rows=1 width=205) (actual time=328.066..994.584 rows=37,349 loops=1)

  • Buffers: shared hit=640115
5. 24.921 791.991 ↓ 22,409.6 112,048 1

Nested Loop (cost=1,007.87..5,410.53 rows=5 width=179) (actual time=328.013..791.991 rows=112,048 loops=1)

  • Buffers: shared hit=640114
6. 0.026 0.026 ↑ 1.2 4 1

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

  • Buffers: shared hit=2
7. 21.063 767.044 ↓ 28,012.0 28,012 4

Materialize (cost=1,007.73..5,398.26 rows=1 width=170) (actual time=81.999..191.761 rows=28,012 loops=4)

  • Buffers: shared hit=640112
8. 45.918 745.981 ↓ 37,349.0 37,349 1

Nested Loop (cost=1,007.73..5,398.25 rows=1 width=170) (actual time=327.991..745.981 rows=37,349 loops=1)

  • Buffers: shared hit=640112
9. 127.805 662.714 ↓ 37,349.0 37,349 1

Nested Loop (cost=1,007.44..5,397.85 rows=1 width=145) (actual time=327.96..662.714 rows=37,349 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=527880
10. 63.286 384.337 ↓ 37,643.0 37,643 1

Nested Loop (cost=1,007.44..5,395.31 rows=1 width=157) (actual time=327.934..384.337 rows=37,643 loops=1)

  • Buffers: shared hit=490237
11. 0.016 0.016 ↑ 1.0 11 1

Seq Scan on obsolescence o (cost=0..1.11 rows=11 width=4) (actual time=0.004..0.016 rows=11 loops=1)

  • Buffers: shared hit=1
12. 31.381 321.035 ↓ 18,821.5 37,643 11

Materialize (cost=1,007.44..5,393.88 rows=2 width=161) (actual time=0.36..29.185 rows=37,643 loops=11)

  • Buffers: shared hit=490236
13. 45.928 289.654 ↓ 18,821.5 37,643 1

Gather (cost=1,007.44..5,393.87 rows=2 width=161) (actual time=3.949..289.654 rows=37,643 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=490236
14. 18.206 243.726 ↓ 18,822.0 18,822 2 / 2

Nested Loop (cost=7.44..4,393.67 rows=1 width=161) (actual time=1.948..243.726 rows=18,822 loops=2)

  • Buffers: shared hit=490236
15. 23.041 206.486 ↓ 380.7 19,034 2 / 2

Nested Loop (cost=7.29..4,384.76 rows=50 width=134) (actual time=1.932..206.486 rows=19,034 loops=2)

  • Buffers: shared hit=414949
16. 15.599 164.411 ↓ 380.7 19,034 2 / 2

Nested Loop (cost=7.01..4,366.14 rows=50 width=107) (actual time=1.917..164.411 rows=19,034 loops=2)

  • Buffers: shared hit=300744
17. 24.726 148.812 ↓ 380.7 19,034 2 / 2

Nested Loop (cost=6.73..4,346.67 rows=50 width=107) (actual time=1.907..148.812 rows=19,034 loops=2)

  • Buffers: shared hit=300003
18. 5.900 105.052 ↓ 333.9 19,034 2 / 2

Hash Join (cost=6.45..4,329.56 rows=57 width=95) (actual time=1.889..105.052 rows=19,034 loops=2)

  • Buffers: shared hit=185795
19. 15.595 99.137 ↓ 121.1 22,896 2 / 2

Nested Loop (cost=5.31..4,327.72 rows=189 width=99) (actual time=1.347..99.137 rows=22,896 loops=2)

  • Buffers: shared hit=185764
20. 5.203 37.692 ↓ 13.7 22,925 2 / 2

Hash Join (cost=4.9..3,393.55 rows=1,668 width=44) (actual time=1.325..37.692 rows=22,925 loops=2)

  • Buffers: shared hit=1969
21. 5.203 32.471 ↓ 8.6 22,925 2 / 2

Hash Join (cost=3.04..3,382.41 rows=2,652 width=36) (actual time=1.299..32.471 rows=22,925 loops=2)

  • Buffers: shared hit=1967
22. 9.124 27.257 ↓ 2.0 22,925 2 / 2

Hash Join (cost=1.74..3,340.93 rows=11,546 width=36) (actual time=1.28..27.257 rows=22,925 loops=2)

  • Buffers: shared hit=1965
23. 18.115 18.115 ↑ 1.2 59,952 2 / 2

Seq Scan on incident i (cost=0..2,961.8 rows=70,599 width=16) (actual time=0.006..18.115 rows=59,952 loops=2)

  • Filter: ((NOT i.deleted) AND (i.incidentstatusid <> ALL ('{4,11,17}'::integer[])))
  • Buffers: shared hit=1963
24. 0.005 0.018 ↑ 1.0 21 2 / 2

Hash (cost=1.43..1.43 rows=21 width=20) (actual time=0.018..0.018 rows=21 loops=2)

  • Buffers: shared hit=2
25. 0.013 0.013 ↑ 1.0 21 2 / 2

Seq Scan on asset a (cost=0..1.43 rows=21 width=20) (actual time=0.006..0.013 rows=21 loops=2)

  • Filter: a.active
  • Buffers: shared hit=2
26. 0.005 0.011 ↑ 1.0 13 2 / 2

Hash (cost=1.13..1.13 rows=13 width=8) (actual time=0.011..0.011 rows=13 loops=2)

  • Buffers: shared hit=2
27. 0.006 0.006 ↑ 1.0 13 2 / 2

Seq Scan on customer cu (cost=0..1.13 rows=13 width=8) (actual time=0.004..0.006 rows=13 loops=2)

  • Buffers: shared hit=2
28. 0.004 0.018 ↓ 1.6 13 2 / 2

Hash (cost=1.76..1.76 rows=8 width=12) (actual time=0.017..0.018 rows=13 loops=2)

  • Buffers: shared hit=2
29. 0.014 0.014 ↓ 1.6 13 2 / 2

Seq Scan on contract_protectionservice cps (cost=0..1.76 rows=8 width=12) (actual time=0.009..0.014 rows=13 loops=2)

  • Filter: ((NOT cps.deleted) AND (cps.protectionserviceid = 2) AND ((cps.domainusageid = 1) OR (cps.domainusageid = 2) OR (cps.domainusageid = 3)))
  • Buffers: shared hit=2
30. 45.850 45.850 ↑ 1.0 1 45,850 / 2

Index Scan using uq_link_linkid_linktypeid on link l (cost=0.42..0.55 rows=1 width=67) (actual time=0.002..0.002 rows=1 loops=45,850)

  • Index Cond: ((l.linkid = i.detectionlinkid) AND (l.linktypeid = 1))
  • Filter: ((NOT l.deleted) AND (l.monitorexecutiontypeid <> 3) AND (i.assetid = l.assetid))
  • Buffers: shared hit=183795
31. 0.004 0.015 ↑ 1.0 3 2 / 2

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

  • Buffers: shared hit=2
32. 0.011 0.011 ↑ 1.0 3 2 / 2

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

  • Filter: es.monitorizable
  • Buffers: shared hit=2
33. 19.035 19.035 ↑ 1.0 1 38,069 / 2

Index Scan using uq_domain_domainid_domaintypeid on domain d (cost=0.28..0.3 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=38,069)

  • Index Cond: (d.domainid = l.domainid)
  • Filter: d.active
  • Buffers: shared hit=114208
34. 0.000 0.000 ↓ 0.0 0 38,068 / 2

Index Scan using uq_domain_domainid_domaintypeid on domain masterdomain (cost=0.28..0.39 rows=1 width=8) (actual time=0..0 rows=0 loops=38,068)

  • Index Cond: (d.masterdomainid = masterdomain.domainid)
  • Filter: masterdomain.active
  • Buffers: shared hit=741
35. 19.034 19.034 ↑ 1.0 1 38,068 / 2

Index Scan using pk_monitordomainconfiguration on monitordomainconfiguration mdc (cost=0.28..0.37 rows=1 width=39) (actual time=0.001..0.001 rows=1 loops=38,068)

  • Index Cond: (mdc.monitordomainconfigurationid = COALESCE(d.monitordomainconfigurationid, masterdomain.monitordomainconfigurationid))
  • Buffers: shared hit=114205
36. 19.034 19.034 ↑ 1.0 1 38,068 / 2

Index Scan using idx_bot_bottypeid_botid on bot b (cost=0.15..0.18 rows=1 width=43) (actual time=0.001..0.001 rows=1 loops=38,068)

  • Index Cond: ((b.bottypeid = mdc.monitorbottypeid) AND (b.botid = mdc.monitorbotid))
  • Filter: b.active
  • Buffers: shared hit=75287
37. 150.572 150.572 ↑ 1.0 44 37,643

Seq Scan on contract_protectionservice__domain cpsd (cost=0..1.44 rows=44 width=12) (actual time=0.001..0.004 rows=44 loops=37,643)

  • Filter: (NOT cpsd.deleted)
  • Buffers: shared hit=37643
38. 37.349 37.349 ↑ 1.0 1 37,349

Index Scan using idx_uq_detectionrequest_lastsearch on monitorrequest mr (cost=0.29..0.4 rows=1 width=29) (actual time=0.001..0.001 rows=1 loops=37,349)

  • Index Cond: (l.linkid = mr.linkid)
  • Buffers: shared hit=112232
39. 28.453 28.459 ↓ 35,014.8 560,236 1

Sort (cost=1.53..1.57 rows=16 width=20) (actual time=0.019..28.459 rows=560,236 loops=1)

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

Seq Scan on cadence cad (cost=0..1.21 rows=16 width=20) (actual time=0.003..0.006 rows=16 loops=1)

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