explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jARA

Settings
# exclusive inclusive rows x rows loops node
1. 0.879 655.885 ↓ 10,000.0 10,000 1

Limit (cost=2,877.42..2,877.46 rows=1 width=300) (actual time=650.121..655.885 rows=10,000 loops=1)

  • Buffers: shared hit=640697
2. 4.182 655.006 ↓ 10,000.0 10,000 1

Unique (cost=2,877.42..2,877.46 rows=1 width=300) (actual time=650.12..655.006 rows=10,000 loops=1)

  • Buffers: shared hit=640697
3. 42.604 650.824 ↓ 10,040.0 10,040 1

Sort (cost=2,877.42..2,877.43 rows=1 width=300) (actual time=650.118..650.824 rows=10,040 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)), (COALESCE(mdc.queue, mastermdc.queue))
  • Sort Method: quicksort Memory: 11326kB
  • Buffers: shared hit=640697
4. 186.289 608.220 ↓ 37,642.0 37,642 1

Merge Join (cost=1,009.42..2,877.41 rows=1 width=300) (actual time=5.743..608.22 rows=37,642 loops=1)

  • Buffers: shared hit=640697
5. 26.787 391.917 ↓ 22,585.4 112,927 1

Nested Loop (cost=1,007.89..5,674.82 rows=5 width=202) (actual time=5.691..391.917 rows=112,927 loops=1)

  • Buffers: shared hit=640696
6. 0.022 0.022 ↑ 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.022 rows=4 loops=1)

  • Buffers: shared hit=2
7. 22.739 365.108 ↓ 28,232.0 28,232 4

Materialize (cost=1,007.75..5,662.55 rows=1 width=193) (actual time=1.419..91.277 rows=28,232 loops=4)

  • Buffers: shared hit=640694
8. 51.288 342.369 ↓ 37,642.0 37,642 1

Nested Loop (cost=1,007.75..5,662.54 rows=1 width=193) (actual time=5.669..342.369 rows=37,642 loops=1)

  • Buffers: shared hit=640694
9. 20.022 253.439 ↓ 37,642.0 37,642 1

Nested Loop (cost=1,007.46..5,662.14 rows=1 width=168) (actual time=5.642..253.439 rows=37,642 loops=1)

  • Buffers: shared hit=527876
10. 0.000 195.775 ↓ 37,642.0 37,642 1

Gather (cost=1,007.46..5,660.89 rows=1 width=176) (actual time=5.633..195.775 rows=37,642 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=490234
11. 17.356 262.965 ↓ 18,821.0 18,821 2 / 2

Nested Loop (cost=7.46..4,660.79 rows=1 width=176) (actual time=3.091..262.965 rows=18,821 loops=2)

  • Buffers: shared hit=490234
12. 16.796 226.788 ↓ 607.1 18,821 2 / 2

Nested Loop (cost=7.31..4,655.27 rows=31 width=157) (actual time=3.072..226.788 rows=18,821 loops=2)

  • Filter: COALESCE(mdc.monitorautomation, mastermdc.monitorautomation)
  • Buffers: shared hit=414949
13. 20.130 209.992 ↓ 307.0 19,034 2 / 2

Nested Loop (cost=7.03..4,631.57 rows=62 width=127) (actual time=3.059..209.992 rows=19,034 loops=2)

  • Buffers: shared hit=414208
14. 16.075 170.828 ↓ 307.0 19,034 2 / 2

Nested Loop (cost=6.75..4,608.48 rows=62 width=95) (actual time=3.037..170.828 rows=19,034 loops=2)

  • Buffers: shared hit=300744
15. 22.863 154.753 ↓ 307.0 19,034 2 / 2

Nested Loop (cost=6.47..4,584.34 rows=62 width=95) (actual time=3.023..154.753 rows=19,034 loops=2)

  • Buffers: shared hit=300003
16. 6.057 112.855 ↓ 268.1 19,034 2 / 2

Hash Join (cost=6.19..4,563.02 rows=71 width=87) (actual time=3.007..112.855 rows=19,034 loops=2)

  • Buffers: shared hit=185795
17. 17.447 106.783 ↓ 96.6 22,896 2 / 2

Nested Loop (cost=5.05..4,561 rows=237 width=91) (actual time=2.125..106.783 rows=22,896 loops=2)

  • Buffers: shared hit=185764
18. 5.451 43.486 ↓ 11.0 22,925 2 / 2

Hash Join (cost=4.64..3,393.29 rows=2,085 width=36) (actual time=2.1..43.486 rows=22,925 loops=2)

  • Buffers: shared hit=1969
19. 5.756 38.015 ↓ 8.6 22,925 2 / 2

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

  • Buffers: shared hit=1967
20. 10.324 32.245 ↓ 2.0 22,925 2 / 2

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

  • Buffers: shared hit=1965
21. 21.897 21.897 ↑ 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..21.897 rows=59,952 loops=2)

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

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

  • Buffers: shared hit=2
23. 0.017 0.017 ↑ 1.0 21 2 / 2

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

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

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

  • Buffers: shared hit=2
25. 0.009 0.009 ↑ 1.0 13 2 / 2

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

  • Buffers: shared hit=2
26. 0.004 0.020 ↓ 1.3 13 2 / 2

Hash (cost=1.48..1.48 rows=10 width=4) (actual time=0.02..0.02 rows=13 loops=2)

  • Buffers: shared hit=2
27. 0.016 0.016 ↓ 1.3 13 2 / 2

Seq Scan on contract_protectionservice cps (cost=0..1.48 rows=10 width=4) (actual time=0.01..0.016 rows=13 loops=2)

  • Filter: ((NOT cps.deleted) AND (cps.protectionserviceid = 2))
  • Buffers: shared hit=2
28. 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
29. 0.003 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
30. 0.012 0.012 ↑ 1.0 3 2 / 2

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

  • Filter: es.monitorizable
  • Buffers: shared hit=2
31. 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
32. 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
33. 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=40) (actual time=0.001..0.001 rows=1 loops=38,068)

  • Index Cond: (d.monitordomainconfigurationid = mdc.monitordomainconfigurationid)
  • Buffers: shared hit=113464
34. 0.000 0.000 ↓ 0.0 0 38,068 / 2

Index Scan using pk_monitordomainconfiguration on monitordomainconfiguration mastermdc (cost=0.28..0.37 rows=1 width=40) (actual time=0..0 rows=0 loops=38,068)

  • Index Cond: (masterdomain.monitordomainconfigurationid = mastermdc.monitordomainconfigurationid)
  • Buffers: shared hit=741
35. 18.821 18.821 ↑ 1.0 1 37,642 / 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=37,642)

  • Index Cond: ((COALESCE(mdc.monitorbottypeid, mastermdc.monitorbottypeid) = b.bottypeid) AND (COALESCE(mdc.monitorbotid, mastermdc.monitorbotid) = b.botid))
  • Filter: b.active
  • Buffers: shared hit=75285
36. 37.642 37.642 ↑ 2.2 5 37,642

Seq Scan on obsolescence o (cost=0..1.11 rows=11 width=4) (actual time=0..0.001 rows=5 loops=37,642)

  • Buffers: shared hit=37642
37. 37.642 37.642 ↑ 1.0 1 37,642

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,642)

  • Index Cond: (l.linkid = mr.linkid)
  • Buffers: shared hit=112818
38. 30.005 30.014 ↓ 35,289.4 564,631 1

Sort (cost=1.53..1.57 rows=16 width=20) (actual time=0.02..30.014 rows=564,631 loops=1)

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

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

  • Filter: (cad.cadence > 0)
  • Buffers: shared hit=1
Planning time : 33.388 ms
Execution time : 659.397 ms