explain.depesz.com

PostgreSQL's explain analyze made readable

Result: C4yi

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 2,361.737 ↓ 40.0 40 1

Limit (cost=545.96..546.00 rows=1 width=586) (actual time=2,361.706..2,361.737 rows=40 loops=1)

2. 0.030 2,361.725 ↓ 40.0 40 1

GroupAggregate (cost=545.96..546.00 rows=1 width=586) (actual time=2,361.705..2,361.725 rows=40 loops=1)

  • Group Key: dl.highrisk, dl.createdat, l.linkid, d.url, (COALESCE(cfg.icon, mcfg.icon)), (COALESCE(cfg.channelid, mcfg.channelid)), a.assetid, i.incidentstatusid
3. 1.816 2,361.695 ↓ 53.0 53 1

Sort (cost=545.96..545.97 rows=1 width=586) (actual time=2,361.692..2,361.695 rows=53 loops=1)

  • Sort Key: dl.highrisk DESC, dl.createdat, l.linkid, d.url, (COALESCE(cfg.icon, mcfg.icon)), (COALESCE(cfg.channelid, mcfg.channelid)), a.assetid
  • Sort Method: quicksort Memory: 212kB
4. 1.328 2,359.879 ↓ 1,164.0 1,164 1

Nested Loop Left Join (cost=5.00..545.95 rows=1 width=586) (actual time=0.425..2,359.879 rows=1,164 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))
5. 1.254 2,358.551 ↓ 1,164.0 1,164 1

Nested Loop Left Join (cost=4.85..544.89 rows=1 width=202) (actual time=0.414..2,358.551 rows=1,164 loops=1)

  • Join Filter: (co.contractid = ccd.contractid)
6. 0.646 2,357.297 ↓ 1,164.0 1,164 1

Nested Loop Left Join (cost=4.71..543.83 rows=1 width=202) (actual time=0.399..2,357.297 rows=1,164 loops=1)

  • Join Filter: (c.customerdivisionid = dc.customerdivisionid)
7. 0.590 2,355.487 ↓ 1,164.0 1,164 1

Nested Loop Left Join (cost=4.56..543.66 rows=1 width=202) (actual time=0.391..2,355.487 rows=1,164 loops=1)

  • Join Filter: (c.customerid = co.customerid)
8. 1.760 2,354.897 ↓ 1,164.0 1,164 1

Nested Loop Left Join (cost=4.56..532.53 rows=1 width=202) (actual time=0.387..2,354.897 rows=1,164 loops=1)

9. 0.645 2,353.137 ↓ 1,164.0 1,164 1

Nested Loop Left Join (cost=4.27..526.41 rows=1 width=146) (actual time=0.379..2,353.137 rows=1,164 loops=1)

  • Join Filter: (d.domainconfigurationid IS NULL)
10. 1.308 2,351.328 ↓ 1,164.0 1,164 1

Nested Loop Left Join (cost=3.98..526.00 rows=1 width=150) (actual time=0.371..2,351.328 rows=1,164 loops=1)

11. 0.856 2,348.856 ↓ 1,164.0 1,164 1

Nested Loop (cost=3.69..525.63 rows=1 width=90) (actual time=0.355..2,348.856 rows=1,164 loops=1)

12. 0.569 2,345.672 ↓ 1,164.0 1,164 1

Nested Loop (cost=3.40..525.32 rows=1 width=62) (actual time=0.342..2,345.672 rows=1,164 loops=1)

13. 0.652 2,342.775 ↓ 1,164.0 1,164 1

Nested Loop (cost=3.13..517.01 rows=1 width=62) (actual time=0.328..2,342.775 rows=1,164 loops=1)

14. 1.208 2,339.795 ↓ 1,164.0 1,164 1

Nested Loop Left Join (cost=2.85..508.71 rows=1 width=58) (actual time=0.314..2,339.795 rows=1,164 loops=1)

  • Filter: ((cl.linkid IS NULL) OR cl.hasminimumdata)
  • Rows Removed by Filter: 3
15. 90.646 2,337.420 ↓ 1,167.0 1,167 1

Nested Loop (cost=2.42..500.93 rows=1 width=62) (actual time=0.305..2,337.420 rows=1,167 loops=1)

16. 34.082 1,609.908 ↓ 318,433.0 318,433 1

Nested Loop (cost=1.85..500.04 rows=1 width=54) (actual time=0.098..1,609.908 rows=318,433 loops=1)

17. 76.695 489.338 ↓ 543,244.0 543,244 1

Nested Loop (cost=1.42..498.42 rows=1 width=41) (actual time=0.082..489.338 rows=543,244 loops=1)

  • Join Filter: (a.assetid = l.assetid)
18. 0.033 0.203 ↓ 8.0 8 1

Nested Loop (cost=0.85..31.47 rows=1 width=37) (actual time=0.055..0.203 rows=8 loops=1)

  • Join Filter: (a.assetid = coa.assetid)
19. 0.035 0.122 ↓ 4.0 8 1

Nested Loop (cost=0.57..30.74 rows=2 width=29) (actual time=0.039..0.122 rows=8 loops=1)

20. 0.039 0.039 ↓ 4.0 8 1

Index Scan using uq_asset_name_customerid on asset a (cost=0.28..14.12 rows=2 width=21) (actual time=0.025..0.039 rows=8 loops=1)

  • Index Cond: (customerid = 594)
  • Filter: active
21. 0.048 0.048 ↑ 1.0 1 8

Index Only Scan using uq_asset_scope on asset_scope ascope (cost=0.29..8.31 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=8)

  • Index Cond: ((assetid = a.assetid) AND (scopeid = 1))
  • Heap Fetches: 8
22. 0.048 0.048 ↑ 1.0 1 8

Index Only Scan using uq_contract_asset on contract_asset coa (cost=0.28..0.36 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=8)

  • Index Cond: ((contractid = 614) AND (assetid = ascope.assetid))
  • Heap Fetches: 8
23. 412.440 412.440 ↓ 197.4 67,906 8

Index Scan using idx_link_enforcementstatus_customer_asset_minimumdata on link l (cost=0.56..462.65 rows=344 width=16) (actual time=0.020..51.555 rows=67,906 loops=8)

  • Index Cond: ((enforcementstatusid = 6) AND (customerid = 594) AND (assetid = ascope.assetid) AND (hasminimumdata = true))
  • Filter: ((NOT deleted) AND hasminimumdata)
24. 1,086.488 1,086.488 ↑ 1.0 1 543,244

Index Scan using uq_detectionlink_detectionlinkid_linktypeid on detectionlink dl (cost=0.43..1.61 rows=1 width=13) (actual time=0.002..0.002 rows=1 loops=543,244)

  • Index Cond: (detectionlinkid = l.linkid)
25. 636.866 636.866 ↓ 0.0 0 318,433

Index Scan using idx_incident_incidentstatus_customer_detectionlinkid on incident i (cost=0.56..0.89 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=318,433)

  • Index Cond: ((incidentstatusid = 2) AND (customerid = 594) AND (detectionlinkid = dl.detectionlinkid))
  • Filter: ((NOT deleted) AND (enforcementlevelid = 8))
  • Rows Removed by Filter: 0
26. 1.167 1.167 ↓ 0.0 0 1,167

Index Scan using uq_link_linkid_linktypeid on link cl (cost=0.44..7.77 rows=1 width=5) (actual time=0.001..0.001 rows=0 loops=1,167)

  • Index Cond: (linkid = i.contentlinkid)
  • Filter: (customerid = 594)
27. 2.328 2.328 ↑ 1.0 1 1,164

Index Scan using pk_contract on contract co (cost=0.28..8.29 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1,164)

  • Index Cond: (contractid = 614)
  • Filter: active
28. 2.328 2.328 ↑ 1.0 1 1,164

Index Only Scan using pk_scope on scope (cost=0.28..8.29 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1,164)

  • Index Cond: (scopeid = 1)
  • Heap Fetches: 1164
29. 2.328 2.328 ↑ 1.0 1 1,164

Index Scan using uq_domain_domainid_domaintypeid on domain d (cost=0.29..0.31 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1,164)

  • Index Cond: (domainid = l.domainid)
  • Filter: active
30. 1.164 1.164 ↑ 1.0 1 1,164

Index Scan using pk_domainconfiguration on domainconfiguration cfg (cost=0.29..0.37 rows=1 width=64) (actual time=0.001..0.001 rows=1 loops=1,164)

  • Index Cond: (domainconfigurationid = d.domainconfigurationid)
31. 1.164 1.164 ↓ 0.0 0 1,164

Index Scan using uq_domain_domainid_domaintypeid on domain md (cost=0.29..0.40 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1,164)

  • Index Cond: (domainid = d.masterdomainid)
  • Filter: (hierarchylevelid = 1)
32. 0.000 0.000 ↓ 0.0 0 1,164

Index Scan using pk_domainconfiguration on domainconfiguration mcfg (cost=0.29..6.12 rows=1 width=64) (actual time=0.000..0.000 rows=0 loops=1,164)

  • Index Cond: (domainconfigurationid = md.domainconfigurationid)
33. 0.000 0.000 ↓ 0.0 0 1,164

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

34. 1.164 1.164 ↓ 0.0 0 1,164

Index Only Scan using uq_domain_customerdivision__domain_customerdivision on domain_customerdivision dc (cost=0.14..0.16 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1,164)

  • Index Cond: (domainid = d.domainid)
  • Heap Fetches: 0
35. 0.000 0.000 ↓ 0.0 0 1,164

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=1,164)

  • Index Cond: ((contractid = 614) AND (customerdivisionid = c.customerdivisionid))
  • Heap Fetches: 0
36. 0.000 0.000 ↓ 0.0 0 1,164

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=1,164)

  • Index Cond: ((customerdivisionid = c.customerdivisionid) AND (scopeid = 1))
  • Heap Fetches: 0
Planning time : 20.322 ms
Execution time : 2,362.004 ms