explain.depesz.com

PostgreSQL's explain analyze made readable

Result: N7tT

Settings
# exclusive inclusive rows x rows loops node
1. 0.924 748.521 ↓ 10,000.0 10,000 1

Limit (cost=5,415.40..5,415.44 rows=1 width=300) (actual time=743.256..748.521 rows=10,000 loops=1)

2. 3.892 747.597 ↓ 10,000.0 10,000 1

Unique (cost=5,415.40..5,415.44 rows=1 width=300) (actual time=743.255..747.597 rows=10,000 loops=1)

3. 42.172 743.705 ↓ 10,094.0 10,094 1

Sort (cost=5,415.40..5,415.41 rows=1 width=300) (actual time=743.247..743.705 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)), (COALESCE(mdc.queue, mastermdc.queue))
  • Sort Method: quicksort Memory: 11249kB
4. 118.853 701.533 ↓ 37,348.0 37,348 1

Nested Loop (cost=1,008.01..5,415.39 rows=1 width=300) (actual time=4.255..701.533 rows=37,348 loops=1)

  • Join Filter: ((cad.timeunitid = tu.timeunitid) AND (mr.retry OR (mr.lastrequest IS NULL) OR ((mr.monitorimportstatusid = ANY ('{3,4}'::integer[])) AND (mr.lastrequest <= (statement_timestamp() - ('24 HOUR'::cstring)::interval))) OR ((mr.nextrequest > mr.lastrequest) AND (mr.nextrequest <= statement_timestamp())) OR (((mr.nextrequest IS NULL) OR (mr.nextrequest <= mr.lastrequest)) AND (mr.lastrequest <= (statement_timestamp() - (((((tu.hourequivalence * (cad.cadence)::numeric) * '2'::numeric))::text || ' HOUR'::text))::interval)))))
  • Rows Removed by Join Filter: 105
5. 58.219 582.680 ↓ 37,348.0 37,348 1

Nested Loop (cost=1,008.01..5,414.00 rows=1 width=197) (actual time=4.229..582.680 rows=37,348 loops=1)

  • Join Filter: ((mr.nextcadenceid = cad.cadenceid) OR ((mr.nextcadenceid IS NULL) AND (o.obsolescenceid = cad.obsolescenceid) AND (cad.age = 0)))
  • Rows Removed by Join Filter: 560220
6. 48.905 449.765 ↓ 37,348.0 37,348 1

Nested Loop Left Join (cost=1,008.01..5,412.51 rows=1 width=193) (actual time=4.222..449.765 rows=37,348 loops=1)

7. 10.410 363.512 ↓ 37,348.0 37,348 1

Nested Loop (cost=1,007.72..5,412.11 rows=1 width=168) (actual time=4.202..363.512 rows=37,348 loops=1)

  • Join Filter: (COALESCE(a.monitorobsolescenceid, mdc.monitorobsolescenceid, mastermdc.monitorobsolescenceid) = o.obsolescenceid)
  • Rows Removed by Join Filter: 149701
8. 89.661 315.754 ↓ 37,348.0 37,348 1

Nested Loop Left Join (cost=1,007.72..5,410.86 rows=1 width=176) (actual time=4.196..315.754 rows=37,348 loops=1)

  • Join Filter: ((cpsd.contractprotectionserviceid = cps.contractprotectionserviceid) AND (cpsd.domainid = d.domainid))
  • Rows Removed by Join Filter: 1656248
  • Filter: ((cps.domainusageid = 1) OR ((cps.domainusageid = 2) AND (cpsd.contractprotectionservicedomainid IS NOT NULL)) OR ((cps.domainusageid = 3) AND (cpsd.contractprotectionservicedomainid IS NULL)))
  • Rows Removed by Filter: 294
9. 0.000 37.883 ↓ 37,642.0 37,642 1

Gather (cost=1,007.72..5,408.43 rows=1 width=188) (actual time=4.183..37.883 rows=37,642 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
10. 16.839 249.889 ↓ 18,821.0 18,821 2 / 2

Nested Loop (cost=7.72..4,408.33 rows=1 width=188) (actual time=2.026..249.889 rows=18,821 loops=2)

11. 16.900 214.229 ↓ 752.8 18,821 2 / 2

Nested Loop Left Join (cost=7.57..4,403.88 rows=25 width=169) (actual time=2.011..214.229 rows=18,821 loops=2)

  • Filter: COALESCE(mdc.monitorautomation, mastermdc.monitorautomation)
  • Rows Removed by Filter: 213
12. 18.233 197.329 ↓ 380.7 19,034 2 / 2

Nested Loop Left Join (cost=7.29..4,384.76 rows=50 width=139) (actual time=2.000..197.329 rows=19,034 loops=2)

13. 16.017 160.062 ↓ 380.7 19,034 2 / 2

Nested Loop Left Join (cost=7.01..4,366.14 rows=50 width=107) (actual time=1.985..160.062 rows=19,034 loops=2)

14. 21.133 144.045 ↓ 380.7 19,034 2 / 2

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

15. 5.803 103.877 ↓ 333.9 19,034 2 / 2

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

  • Hash Cond: (l.enforcementstatusid = es.enforcementstatusid)
16. 13.436 98.057 ↓ 121.1 22,896 2 / 2

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

  • Join Filter: (a.assetid = l.assetid)
17. 5.538 38.771 ↓ 13.7 22,925 2 / 2

Hash Join (cost=4.90..3,393.55 rows=1,668 width=44) (actual time=1.412..38.771 rows=22,925 loops=2)

  • Hash Cond: (i.contractid = cps.contractid)
18. 5.200 33.213 ↓ 8.6 22,925 2 / 2

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

  • Hash Cond: (a.customerid = cu.customerid)
19. 9.056 28.001 ↓ 2.0 22,925 2 / 2

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

  • Hash Cond: ((i.assetid = a.assetid) AND (i.customerid = a.customerid))
20. 18.918 18.918 ↑ 1.2 59,952 2 / 2

Parallel Seq Scan on incident i (cost=0.00..2,961.80 rows=70,599 width=16) (actual time=0.007..18.918 rows=59,952 loops=2)

  • Filter: ((NOT deleted) AND (incidentstatusid <> ALL ('{4,11,17}'::integer[])))
  • Rows Removed by Filter: 1792
21. 0.007 0.027 ↑ 1.0 21 2 / 2

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.020 0.020 ↑ 1.0 21 2 / 2

Seq Scan on asset a (cost=0.00..1.43 rows=21 width=20) (actual time=0.011..0.020 rows=21 loops=2)

  • Filter: active
  • Rows Removed by Filter: 22
23. 0.004 0.012 ↑ 1.0 13 2 / 2

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.008 0.008 ↑ 1.0 13 2 / 2

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

25. 0.004 0.020 ↓ 1.6 13 2 / 2

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.016 0.016 ↓ 1.6 13 2 / 2

Seq Scan on contract_protectionservice cps (cost=0.00..1.76 rows=8 width=12) (actual time=0.010..0.016 rows=13 loops=2)

  • Filter: ((NOT deleted) AND (protectionserviceid = 2) AND ((domainusageid = 1) OR (domainusageid = 2) OR (domainusageid = 3)))
  • Rows Removed by Filter: 25
27. 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: ((linkid = i.detectionlinkid) AND (linktypeid = 1))
  • Filter: ((NOT deleted) AND (monitorexecutiontypeid <> 3) AND (i.assetid = assetid))
  • Rows Removed by Filter: 0
28. 0.005 0.017 ↑ 1.0 3 2 / 2

Hash (cost=1.10..1.10 rows=3 width=4) (actual time=0.017..0.017 rows=3 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.012 0.012 ↑ 1.0 3 2 / 2

Seq Scan on enforcementstatus es (cost=0.00..1.10 rows=3 width=4) (actual time=0.011..0.012 rows=3 loops=2)

  • Filter: monitorizable
  • Rows Removed by Filter: 7
30. 19.035 19.035 ↑ 1.0 1 38,069 / 2

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

  • Index Cond: (domainid = l.domainid)
  • Filter: active
  • Rows Removed by Filter: 0
31. 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.000..0.000 rows=0 loops=38,068)

  • Index Cond: (d.masterdomainid = domainid)
  • Filter: active
32. 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 = monitordomainconfigurationid)
33. 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.000..0.000 rows=0 loops=38,068)

  • Index Cond: (masterdomain.monitordomainconfigurationid = monitordomainconfigurationid)
34. 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) = bottypeid) AND (COALESCE(mdc.monitorbotid, mastermdc.monitorbotid) = botid))
  • Filter: active
35. 188.210 188.210 ↑ 1.0 44 37,642

Seq Scan on contract_protectionservice__domain cpsd (cost=0.00..1.44 rows=44 width=12) (actual time=0.001..0.005 rows=44 loops=37,642)

  • Filter: (NOT deleted)
36. 37.348 37.348 ↑ 2.2 5 37,348

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

37. 37.348 37.348 ↑ 1.0 1 37,348

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

  • Index Cond: (l.linkid = linkid)
38. 74.696 74.696 ↑ 1.0 16 37,348

Seq Scan on cadence cad (cost=0.00..1.21 rows=16 width=20) (actual time=0.000..0.002 rows=16 loops=37,348)

  • Filter: (cadence > 0)
  • Rows Removed by Filter: 1
39. 0.000 0.000 ↑ 5.0 1 37,348

Seq Scan on timeunit tu (cost=0.00..1.05 rows=5 width=9) (actual time=0.000..0.000 rows=1 loops=37,348)

Planning time : 61.599 ms
Execution time : 749.835 ms