explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Aobj

Settings
# exclusive inclusive rows x rows loops node
1. 1.453 1,962.440 ↓ 10,000.0 10,000 1

Limit (cost=2,852.43..179,892.81 rows=1 width=207) (actual time=30.418..1,962.44 rows=10,000 loops=1)

  • Buffers: shared hit=563091
2. 39.219 1,960.987 ↓ 10,000.0 10,000 1

Merge Join (cost=2,852.43..179,892.81 rows=1 width=207) (actual time=30.417..1,960.987 rows=10,000 loops=1)

  • Buffers: shared hit=563091
3. 168.094 1,916.497 ↓ 2,500.0 10,000 1

Nested Loop (cost=2,850.75..238,902.94 rows=4 width=181) (actual time=30.371..1,916.497 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=563090
4. 27.486 521.327 ↓ 2,011.6 10,058 1

Nested Loop (cost=2,850.46..238,899.49 rows=5 width=193) (actual time=28.286..521.327 rows=10,058 loops=1)

  • Buffers: shared hit=474703
5. 9.330 450.798 ↓ 8,608.6 43,043 1

Nested Loop (cost=2,850.18..238,897.96 rows=5 width=185) (actual time=28.269..450.798 rows=43,043 loops=1)

  • Buffers: shared hit=378464
6. 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
7. 21.220 441.458 ↓ 43,043.0 43,043 1

Materialize (cost=2,850.05..238,885.69 rows=1 width=176) (actual time=28.257..441.458 rows=43,043 loops=1)

  • Buffers: shared hit=378462
8. 17.577 420.238 ↓ 43,043.0 43,043 1

Nested Loop (cost=2,850.05..238,885.68 rows=1 width=176) (actual time=28.251..420.238 rows=43,043 loops=1)

  • Buffers: shared hit=378462
9. 18.814 316.575 ↓ 43,043.0 43,043 1

Nested Loop (cost=2,849.61..238,885.15 rows=1 width=151) (actual time=28.237..316.575 rows=43,043 loops=1)

  • Buffers: shared hit=239318
10. 11.299 166.352 ↓ 2.8 43,803 1

Hash Join (cost=2,849.04..148,675.42 rows=15,912 width=159) (actual time=28.185..166.352 rows=43,803 loops=1)

  • Buffers: shared hit=21394
11. 13.362 155.042 ↓ 2.8 43,803 1

Hash Join (cost=2,847.75..148,618.74 rows=15,912 width=159) (actual time=28.167..155.042 rows=43,803 loops=1)

  • Buffers: shared hit=21393
12. 15.978 141.569 ↓ 3.0 48,130 1

Hash Join (cost=2,834.12..148,521.47 rows=15,912 width=130) (actual time=28.049..141.569 rows=48,130 loops=1)

  • Buffers: shared hit=21388
13. 16.505 112.215 ↓ 3.0 48,130 1

Hash Join (cost=1,504.35..147,149.92 rows=15,912 width=107) (actual time=14.445..112.215 rows=48,130 loops=1)

  • Buffers: shared hit=20830
14. 8.727 81.538 ↓ 3.1 55,804 1

Nested Loop (cost=0.84..145,598.72 rows=18,170 width=99) (actual time=0.146..81.538 rows=55,804 loops=1)

  • Buffers: shared hit=20096
15. 0.007 0.007 ↑ 2.0 2 1

Seq Scan on enforcementstatus es (cost=0..1.1 rows=4 width=4) (actual time=0.006..0.007 rows=2 loops=1)

  • Filter: es.monitorizable
  • Buffers: shared hit=1
16. 9.013 72.804 ↓ 36.1 27,902 2

Nested Loop (cost=0.84..36,391.69 rows=772 width=103) (actual time=0.082..36.402 rows=27,902 loops=2)

  • Buffers: shared hit=20095
17. 0.266 0.266 ↑ 1.9 412 2

Seq Scan on customer cu (cost=0..25.72 rows=772 width=8) (actual time=0.003..0.133 rows=412 loops=2)

  • Buffers: shared hit=20
18. 12.411 63.525 ↓ 68.0 68 825

Nested Loop (cost=0.84..47.1 rows=1 width=95) (actual time=0.014..0.077 rows=68 loops=825)

  • Buffers: shared hit=20075
19. 3.300 3.300 ↑ 1.0 4 825

Index Scan using idx_asset_customer on asset a (cost=0.28..0.9 rows=4 width=16) (actual time=0.002..0.004 rows=4 loops=825)

  • Index Cond: (a.customerid = cu.customerid)
  • Filter: a.active
  • Buffers: shared hit=3087
20. 47.814 47.814 ↓ 7.5 15 3,678

Index Scan using idx_link_enforcementstatus_customer_asset_minimumdata on link l (cost=0.56..11.52 rows=2 width=79) (actual time=0.002..0.013 rows=15 loops=3,678)

  • Index Cond: ((l.enforcementstatusid = es.enforcementstatusid) AND (l.customerid = a.customerid) AND (l.assetid = a.assetid))
  • Filter: ((NOT l.deleted) AND (l.monitorexecutiontypeid <> 3))
  • Buffers: shared hit=16988
21. 4.641 14.172 ↓ 1.0 32,186 1

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

  • Buffers: shared hit=734
22. 9.531 9.531 ↓ 1.0 32,186 1

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

  • Filter: (d.active AND (d.masterdomainid IS NULL))
  • Buffers: shared hit=734
23. 6.327 13.376 ↑ 1.0 34,301 1

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

  • Buffers: shared hit=558
24. 7.049 7.049 ↑ 1.0 34,301 1

Seq Scan on monitordomainconfiguration mdc (cost=0..901.01 rows=34,301 width=31) (actual time=0.005..7.049 rows=34,301 loops=1)

  • Buffers: shared hit=558
25. 0.065 0.111 ↑ 1.0 345 1

Hash (cost=8.45..8.45 rows=345 width=45) (actual time=0.111..0.111 rows=345 loops=1)

  • Buffers: shared hit=5
26. 0.046 0.046 ↑ 1.0 345 1

Seq Scan on bot b (cost=0..8.45 rows=345 width=45) (actual time=0.004..0.046 rows=345 loops=1)

  • Buffers: shared hit=5
27. 0.005 0.011 ↑ 1.0 13 1

Hash (cost=1.13..1.13 rows=13 width=4) (actual time=0.011..0.011 rows=13 loops=1)

  • Buffers: shared hit=1
28. 0.006 0.006 ↑ 1.0 13 1

Seq Scan on obsolescence o (cost=0..1.13 rows=13 width=4) (actual time=0.005..0.006 rows=13 loops=1)

  • Buffers: shared hit=1
29. 131.409 131.409 ↑ 1.0 1 43,803

Index Scan using idx_incident_detectionlink on incident i (cost=0.56..5.66 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=43,803)

  • Index Cond: (i.detectionlinkid = l.linkid)
  • Filter: ((NOT i.deleted) AND (l.customerid = i.customerid) AND (l.assetid = i.assetid) AND (i.incidentstatusid <> ALL ('{4,11,17}'::integer[])))
  • Buffers: shared hit=217924
30. 86.086 86.086 ↓ 0.0 0 43,043

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=0 loops=43,043)

  • Index Cond: (l.linkid = mr.linkid)
  • Buffers: shared hit=139144
31. 43.043 43.043 ↓ 0.0 0 43,043

Index Scan using uq_contract_protectionservice_contractprotectionservice on contract_protectionservice cps (cost=0.28..0.31 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=43,043)

  • Index Cond: ((cps.contractid = i.contractid) AND (cps.protectionserviceid = 2))
  • Filter: ((NOT cps.deleted) AND ((cps.domainusageid = 1) OR (cps.domainusageid = 2) OR (cps.domainusageid = 3)))
  • Buffers: shared hit=96239
32. 1,227.076 1,227.076 ↓ 30.1 241 10,058

Index Scan using idx_contprotservice_domain_domain on contract_protectionservice__domain cpsd (cost=0.29..0.51 rows=8 width=12) (actual time=0.007..0.122 rows=241 loops=10,058)

  • Index Cond: (cpsd.domainid = d.domainid)
  • Filter: (NOT cpsd.deleted)
  • Buffers: shared hit=88387
33. 5.263 5.271 ↓ 4,499.8 89,995 1

Sort (cost=1.68..1.73 rows=20 width=20) (actual time=0.018..5.271 rows=89,995 loops=1)

  • Sort Key: cad.timeunitid
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=1
34. 0.008 0.008 ↑ 1.0 20 1

Seq Scan on cadence cad (cost=0..1.25 rows=20 width=20) (actual time=0.004..0.008 rows=20 loops=1)

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