explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r2Gt

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 4,422.966 ↓ 20.0 20 1

Limit (cost=541.07..541.1 rows=1 width=586) (actual time=4,422.931..4,422.966 rows=20 loops=1)

  • Buffers: shared hit=4640866
2. 0.034 4,422.950 ↓ 21.0 21 1

GroupAggregate (cost=541.03..541.07 rows=1 width=586) (actual time=4,422.928..4,422.95 rows=21 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
  • Buffers: shared hit=4640866
3. 268.821 4,422.916 ↓ 60.0 60 1

Sort (cost=541.03..541.03 rows=1 width=586) (actual time=4,422.911..4,422.916 rows=60 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: 20778kB
  • Buffers: shared hit=4640866
4. 150.805 4,154.095 ↓ 125,904.0 125,904 1

Nested Loop (cost=5..541.02 rows=1 width=586) (actual time=0.297..4,154.095 rows=125,904 loops=1)

  • 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))
  • Buffers: shared hit=4640866
5. 108.296 4,003.290 ↓ 125,904.0 125,904 1

Nested Loop (cost=4.85..540.53 rows=1 width=198) (actual time=0.282..4,003.29 rows=125,904 loops=1)

  • Buffers: shared hit=4514962
6. 150.010 3,894.994 ↓ 125,904.0 125,904 1

Nested Loop (cost=4.71..539.48 rows=1 width=198) (actual time=0.269..3,894.994 rows=125,904 loops=1)

  • Buffers: shared hit=4514962
7. 44.758 3,744.984 ↓ 125,904.0 125,904 1

Nested Loop (cost=4.56..539.3 rows=1 width=198) (actual time=0.256..3,744.984 rows=125,904 loops=1)

  • Buffers: shared hit=4389058
8. 126.248 3,700.226 ↓ 125,904.0 125,904 1

Nested Loop (cost=4.56..528.18 rows=1 width=198) (actual time=0.252..3,700.226 rows=125,904 loops=1)

  • Buffers: shared hit=4389058
9. 128.088 3,573.978 ↓ 125,904.0 125,904 1

Nested Loop (cost=4.27..522.03 rows=1 width=144) (actual time=0.237..3,573.978 rows=125,904 loops=1)

  • Buffers: shared hit=4342255
10. 100.775 3,445.890 ↓ 125,904.0 125,904 1

Nested Loop (cost=3.98..521.62 rows=1 width=148) (actual time=0.218..3,445.89 rows=125,904 loops=1)

  • Buffers: shared hit=4295452
11. 141.918 3,219.211 ↓ 125,904.0 125,904 1

Nested Loop (cost=3.69..521.25 rows=1 width=90) (actual time=0.207..3,219.211 rows=125,904 loops=1)

  • Buffers: shared hit=3964543
12. 122.876 2,951.389 ↓ 125,904.0 125,904 1

Nested Loop (cost=3.4..520.94 rows=1 width=62) (actual time=0.191..2,951.389 rows=125,904 loops=1)

  • Buffers: shared hit=3586831
13. 130.213 2,702.609 ↓ 125,904.0 125,904 1

Nested Loop (cost=3.13..512.63 rows=1 width=62) (actual time=0.172..2,702.609 rows=125,904 loops=1)

  • Buffers: shared hit=3209119
14. 102.649 2,446.492 ↓ 125,904.0 125,904 1

Nested Loop (cost=2.85..504.33 rows=1 width=58) (actual time=0.156..2,446.492 rows=125,904 loops=1)

  • Filter: ((cl.linkid IS NULL) OR cl.hasminimumdata)
  • Buffers: shared hit=2831407
15. 287.149 2,217.913 ↓ 125,930.0 125,930 1

Nested Loop (cost=2.42..496.38 rows=1 width=62) (actual time=0.141..2,217.913 rows=125,930 loops=1)

  • Buffers: shared hit=2674839
16. 209.935 1,294.174 ↓ 318,295.0 318,295 1

Nested Loop (cost=1.85..495.49 rows=1 width=54) (actual time=0.103..1,294.174 rows=318,295 loops=1)

  • Buffers: shared hit=1277152
17. 51.272 447.647 ↓ 318,296.0 318,296 1

Nested Loop (cost=1.42..493.3 rows=1 width=41) (actual time=0.085..447.647 rows=318,296 loops=1)

  • Buffers: shared hit=2237
18. 0.033 0.207 ↓ 8.0 8 1

Nested Loop (cost=0.85..29.76 rows=1 width=37) (actual time=0.054..0.207 rows=8 loops=1)

  • Buffers: shared hit=52
19. 0.034 0.126 ↓ 4.0 8 1

Nested Loop (cost=0.57..29.03 rows=2 width=29) (actual time=0.04..0.126 rows=8 loops=1)

  • Buffers: shared hit=28
20. 0.036 0.036 ↓ 4.0 8 1

Index Scan using idx_asset_customer on asset a (cost=0.28..12.41 rows=2 width=21) (actual time=0.021..0.036 rows=8 loops=1)

  • Index Cond: (a.customerid = 594)
  • Filter: a.active
  • Buffers: shared hit=4
21. 0.056 0.056 ↑ 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.007..0.007 rows=1 loops=8)

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

Index Scan using idx_contract_asset__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: (coa.assetid = ascope.assetid)
  • Filter: (coa.contractid = 614)
  • Buffers: shared hit=24
23. 396.168 396.168 ↓ 173.7 39,787 8

Index Scan using idx_link_enforcementstatus_customer_asset_minimumdata on link l (cost=0.56..460.68 rows=229 width=16) (actual time=0.02..49.521 rows=39,787 loops=8)

  • Index Cond: ((l.enforcementstatusid = 6) AND (l.customerid = 594) AND (l.assetid = ascope.assetid) AND (l.hasminimumdata = true))
  • Filter: ((NOT l.deleted) AND l.hasminimumdata AND (l.linktypeid = 1))
  • Buffers: shared hit=2185
24. 636.592 636.592 ↑ 1.0 1 318,296

Index Scan using idx_detection_link on detectionlink dl (cost=0.43..2.18 rows=1 width=13) (actual time=0.002..0.002 rows=1 loops=318,296)

  • Index Cond: ((dl.detectionlinkid = l.linkid) AND (dl.linktypeid = 1))
  • Buffers: shared hit=1274915
25. 636.590 636.590 ↓ 0.0 0 318,295

Index Scan using idx_incident_incidentstatus_customer_detectionlinkid_enforcemen on incident i (cost=0.56..0.88 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=318,295)

  • Index Cond: ((i.incidentstatusid = 12) AND (i.customerid = 594) AND (i.detectionlinkid = dl.detectionlinkid) AND (i.enforcementlevelid = 8))
  • Buffers: shared hit=1397687
26. 125.930 125.930 ↓ 0.0 0 125,930

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

  • Index Cond: ((cl.linkid = i.contentlinkid) AND (cl.linktypeid = 2))
  • Filter: (cl.customerid = 594)
  • Buffers: shared hit=156568
27. 125.904 125.904 ↑ 1.0 1 125,904

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

  • Index Cond: (co.contractid = 614)
  • Filter: co.active
  • Buffers: shared hit=377712
28. 125.904 125.904 ↑ 1.0 1 125,904

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

  • Index Cond: (scope.scopeid = 1)
  • Heap Fetches: 125904
  • Buffers: shared hit=377712
29. 125.904 125.904 ↑ 1.0 1 125,904

Index Scan using uq_domain_domainid_domaintypeid on domain d (cost=0.29..0.32 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=125,904)

  • Index Cond: (d.domainid = l.domainid)
  • Filter: d.active
  • Buffers: shared hit=377712
30. 125.904 125.904 ↑ 1.0 1 125,904

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

  • Index Cond: (cfg.domainconfigurationid = d.domainconfigurationid)
  • Buffers: shared hit=330909
31. 0.000 0.000 ↓ 0.0 0 125,904

Index Scan using uq_domain_domainid_domaintypeid on domain md (cost=0.29..0.4 rows=1 width=8) (actual time=0..0 rows=0 loops=125,904)

  • Index Cond: (md.domainid = d.masterdomainid)
  • Filter: (md.hierarchylevelid = 1)
  • Buffers: shared hit=46803
32. 0.000 0.000 ↓ 0.0 0 125,904

Index Scan using pk_domainconfiguration on domainconfiguration mcfg (cost=0.29..6.15 rows=1 width=62) (actual time=0..0 rows=0 loops=125,904)

  • Index Cond: (mcfg.domainconfigurationid = md.domainconfigurationid)
  • Buffers: shared hit=46803
33. 0.000 0.000 ↓ 0.0 0 125,904

Seq Scan on customerdivision c (cost=0..10.5 rows=50 width=8) (actual time=0..0 rows=0 loops=125,904)

34. 0.000 0.000 ↓ 0.0 0 125,904

Index Scan using idx_domaincustomerdivision_domain on domain_customerdivision dc (cost=0.14..0.16 rows=1 width=8) (actual time=0..0 rows=0 loops=125,904)

  • Index Cond: (d.domainid = dc.domainid)
  • Buffers: shared hit=125904
35. 0.000 0.000 ↓ 0.0 0 125,904

Index Scan using idx_contractcustomerdivision_customerdivision on contract_customerdivision ccd (cost=0.14..1.04 rows=1 width=8) (actual time=0..0 rows=0 loops=125,904)

  • Index Cond: (ccd.customerdivisionid = c.customerdivisionid)
  • Filter: (ccd.contractid = 614)
36. 0.000 0.000 ↓ 0.0 0 125,904

Index Scan using idx_customerdivisionscope_scope on customerdivision_scope cc (cost=0.15..0.36 rows=10 width=8) (actual time=0..0 rows=0 loops=125,904)

  • Index Cond: ((cc.scopeid = ascope.scopeid) AND (cc.scopeid = 1))
  • Buffers: shared hit=125904
Planning time : 29.982 ms
Execution time : 4,424.553 ms