explain.depesz.com

PostgreSQL's explain analyze made readable

Result: u20c

Settings
# exclusive inclusive rows x rows loops node
1. 0.147 51.042 ↑ 1.0 1 1

Aggregate (cost=425.16..425.17 rows=1 width=8) (actual time=51.042..51.042 rows=1 loops=1)

2. 0.596 50.895 ↓ 696.0 696 1

Nested Loop Left Join (cost=3.99..425.15 rows=1 width=4) (actual time=0.159..50.895 rows=696 loops=1)

  • Join Filter: (cc.scopeid = ascope.scopeid)
  • Filter: (((c.customerdivisionid IS NOT NULL) AND (dc.domainid IS NOT NULL) AND (cc.scopeid IS NOT NULL) AND (ccd.contractid IS NOT NULL)) OR (c.customerdivisionid IS NULL))
3. 0.614 50.299 ↓ 696.0 696 1

Nested Loop Left Join (cost=3.84..424.09 rows=1 width=20) (actual time=0.150..50.299 rows=696 loops=1)

  • Join Filter: (co.contractid = ccd.contractid)
4. 0.827 49.685 ↓ 696.0 696 1

Nested Loop Left Join (cost=3.70..423.03 rows=1 width=20) (actual time=0.140..49.685 rows=696 loops=1)

  • Join Filter: (c.customerdivisionid = dc.customerdivisionid)
5. 0.211 48.858 ↓ 696.0 696 1

Nested Loop Left Join (cost=3.55..422.84 rows=1 width=20) (actual time=0.127..48.858 rows=696 loops=1)

  • Join Filter: (c.customerid = co.customerid)
6. 0.798 48.647 ↓ 696.0 696 1

Nested Loop (cost=3.55..411.72 rows=1 width=20) (actual time=0.125..48.647 rows=696 loops=1)

7. 0.479 47.153 ↓ 696.0 696 1

Nested Loop (cost=3.27..411.40 rows=1 width=20) (actual time=0.112..47.153 rows=696 loops=1)

8. 0.249 43.890 ↓ 696.0 696 1

Nested Loop (cost=3.27..408.38 rows=1 width=20) (actual time=0.107..43.890 rows=696 loops=1)

9. 0.673 42.249 ↓ 696.0 696 1

Nested Loop Left Join (cost=3.27..407.18 rows=1 width=16) (actual time=0.103..42.249 rows=696 loops=1)

  • Filter: ((cl.linkid IS NULL) OR cl.hasminimumdata)
  • Rows Removed by Filter: 4
10. 4.233 41.576 ↓ 700.0 700 1

Nested Loop (cost=2.86..399.98 rows=1 width=20) (actual time=0.095..41.576 rows=700 loops=1)

11. 5.868 25.759 ↓ 43.5 5,792 1

Nested Loop (cost=2.44..324.34 rows=133 width=20) (actual time=0.081..25.759 rows=5,792 loops=1)

12. 1.633 7.280 ↓ 68.9 12,611 1

Nested Loop (cost=2.15..191.67 rows=183 width=16) (actual time=0.068..7.280 rows=12,611 loops=1)

  • Join Filter: (a.assetid = l.assetid)
13. 0.010 0.059 ↓ 4.0 4 1

Nested Loop (cost=1.73..4.82 rows=1 width=20) (actual time=0.036..0.059 rows=4 loops=1)

  • Join Filter: (a.assetid = ascope.assetid)
14. 0.010 0.037 ↓ 4.0 4 1

Hash Join (cost=1.59..3.15 rows=1 width=12) (actual time=0.026..0.037 rows=4 loops=1)

  • Hash Cond: (coa.assetid = a.assetid)
15. 0.012 0.012 ↑ 1.0 8 1

Seq Scan on contract_asset coa (cost=0.00..1.54 rows=8 width=8) (actual time=0.007..0.012 rows=8 loops=1)

  • Filter: (contractid = 38)
  • Rows Removed by Filter: 35
16. 0.004 0.015 ↑ 1.0 4 1

Hash (cost=1.54..1.54 rows=4 width=4) (actual time=0.015..0.015 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.011 0.011 ↑ 1.0 4 1

Seq Scan on asset a (cost=0.00..1.54 rows=4 width=4) (actual time=0.005..0.011 rows=4 loops=1)

  • Filter: (active AND (customerid = 36))
  • Rows Removed by Filter: 39
18. 0.012 0.012 ↑ 1.0 1 4

Index Only Scan using uq_asset_scope on asset_scope ascope (cost=0.14..1.66 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=4)

  • Index Cond: ((assetid = coa.assetid) AND (scopeid = 1))
  • Heap Fetches: 4
19. 5.588 5.588 ↓ 6.8 3,153 4

Index Scan using idx_link_enforcementstatus_customer_asset_minimumdata on link l (cost=0.42..181.09 rows=461 width=12) (actual time=0.014..1.397 rows=3,153 loops=4)

  • Index Cond: ((enforcementstatusid = 6) AND (customerid = 36) AND (assetid = ascope.assetid) AND (hasminimumdata = true))
  • Filter: ((NOT deleted) AND hasminimumdata)
20. 12.611 12.611 ↓ 0.0 0 12,611

Index Only Scan using idx_detectionlink_detectionlinkid_highrisk on detectionlink dl (cost=0.29..0.72 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=12,611)

  • Index Cond: (detectionlinkid = l.linkid)
  • Heap Fetches: 5792
21. 11.584 11.584 ↓ 0.0 0 5,792

Index Scan using idx_incident_detectionlinkid_customerid on incident i (cost=0.42..0.56 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=5,792)

  • Index Cond: ((detectionlinkid = dl.detectionlinkid) AND (customerid = 36))
  • Filter: ((NOT deleted) AND (incidentstatusid = 12) AND (enforcementlevelid = 8))
  • Rows Removed by Filter: 2
22. 0.000 0.000 ↓ 0.0 0 700

Index Scan using uq_link_linkid_linktypeid on link cl (cost=0.42..7.19 rows=1 width=5) (actual time=0.000..0.000 rows=0 loops=700)

  • Index Cond: (linkid = i.contentlinkid)
  • Filter: (customerid = 36)
23. 1.392 1.392 ↑ 1.0 1 696

Seq Scan on contract co (cost=0.00..1.19 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=696)

  • Filter: (active AND (contractid = 38))
  • Rows Removed by Filter: 14
24. 2.784 2.784 ↑ 1.0 1 696

Seq Scan on scope (cost=0.00..3.01 rows=1 width=4) (actual time=0.001..0.004 rows=1 loops=696)

  • Filter: (scopeid = 1)
  • Rows Removed by Filter: 80
25. 0.696 0.696 ↑ 1.0 1 696

Index Scan using uq_domain_domainid_domaintypeid on domain d (cost=0.28..0.32 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=696)

  • Index Cond: (domainid = l.domainid)
  • Filter: active
26. 0.000 0.000 ↓ 0.0 0 696

Seq Scan on customerdivision c (cost=0.00..10.50 rows=50 width=8) (actual time=0.000..0.000 rows=0 loops=696)

27. 0.000 0.000 ↓ 0.0 0 696

Index Only Scan using uq_domain_customerdivision__domain_customerdivision on domain_customerdivision dc (cost=0.14..0.18 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=696)

  • Index Cond: (domainid = d.domainid)
  • Heap Fetches: 0
28. 0.000 0.000 ↓ 0.0 0 696

Index Only Scan using uq_contract_customerdivision__contract_customerdivision on contract_customerdivision ccd (cost=0.14..1.04 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=696)

  • Index Cond: ((contractid = 38) AND (customerdivisionid = c.customerdivisionid))
  • Heap Fetches: 0
29. 0.000 0.000 ↓ 0.0 0 696

Index Only Scan using uq_customerdivisionscope_customerdivision_scope on customerdivision_scope cc (cost=0.15..1.05 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=696)

  • Index Cond: ((customerdivisionid = c.customerdivisionid) AND (scopeid = 1))
  • Heap Fetches: 0
Planning time : 10.136 ms
Execution time : 51.208 ms