explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lB8W

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 1,965.993 ↓ 20.0 20 1

Limit (cost=544.39..544.42 rows=1 width=586) (actual time=1,965.975..1,965.993 rows=20 loops=1)

  • Buffers: shared hit=2570639
2. 0.018 1,965.983 ↓ 21.0 21 1

GroupAggregate (cost=544.35..544.39 rows=1 width=586) (actual time=1,965.972..1,965.983 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=2570639
3. 1.785 1,965.965 ↓ 23.0 23 1

Sort (cost=544.35..544.35 rows=1 width=586) (actual time=1,965.964..1,965.965 rows=23 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: 213kB
  • Buffers: shared hit=2570639
4. 0.647 1,964.180 ↓ 1,168.0 1,168 1

Nested Loop (cost=5..544.34 rows=1 width=586) (actual time=0.429..1,964.18 rows=1,168 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=2570639
5. 1.221 1,962.365 ↓ 1,168.0 1,168 1

Nested Loop (cost=4.85..543.85 rows=1 width=198) (actual time=0.412..1,962.365 rows=1,168 loops=1)

  • Buffers: shared hit=2569471
6. 0.502 1,961.144 ↓ 1,168.0 1,168 1

Nested Loop (cost=4.71..542.8 rows=1 width=198) (actual time=0.396..1,961.144 rows=1,168 loops=1)

  • Buffers: shared hit=2569471
7. 0.565 1,959.474 ↓ 1,168.0 1,168 1

Nested Loop (cost=4.56..542.62 rows=1 width=198) (actual time=0.384..1,959.474 rows=1,168 loops=1)

  • Buffers: shared hit=2568303
8. 1.628 1,958.909 ↓ 1,168.0 1,168 1

Nested Loop (cost=4.56..531.5 rows=1 width=198) (actual time=0.381..1,958.909 rows=1,168 loops=1)

  • Buffers: shared hit=2568303
9. 0.592 1,957.281 ↓ 1,168.0 1,168 1

Nested Loop (cost=4.27..525.35 rows=1 width=144) (actual time=0.372..1,957.281 rows=1,168 loops=1)

  • Buffers: shared hit=2567439
10. 1.167 1,955.521 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.98..524.94 rows=1 width=148) (actual time=0.363..1,955.521 rows=1,168 loops=1)

  • Buffers: shared hit=2566575
11. 0.661 1,953.186 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.69..524.57 rows=1 width=90) (actual time=0.352..1,953.186 rows=1,168 loops=1)

  • Buffers: shared hit=2563935
12. 1.536 1,950.189 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.4..524.25 rows=1 width=62) (actual time=0.336..1,950.189 rows=1,168 loops=1)

  • Buffers: shared hit=2560431
13. 0.489 1,947.485 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.13..515.95 rows=1 width=62) (actual time=0.322..1,947.485 rows=1,168 loops=1)

  • Buffers: shared hit=2556927
14. 1.022 1,944.660 ↓ 1,168.0 1,168 1

Nested Loop (cost=2.85..507.65 rows=1 width=58) (actual time=0.309..1,944.66 rows=1,168 loops=1)

  • Filter: ((cl.linkid IS NULL) OR cl.hasminimumdata)
  • Buffers: shared hit=2553423
15. 126.044 1,942.468 ↓ 1,170.0 1,170 1

Nested Loop (cost=2.42..499.94 rows=1 width=66) (actual time=0.3..1,942.468 rows=1,170 loops=1)

  • Buffers: shared hit=2551911
16. 131.812 1,179.834 ↓ 318,295.0 318,295 1

Nested Loop (cost=1.85..499.05 rows=1 width=66) (actual time=0.098..1,179.834 rows=318,295 loops=1)

  • Buffers: shared hit=1277131
17. 51.129 411.430 ↓ 318,296.0 318,296 1

Nested Loop (cost=1.42..496.87 rows=1 width=53) (actual time=0.086..411.43 rows=318,296 loops=1)

  • Buffers: shared hit=2216
18. 0.028 0.165 ↓ 8.0 8 1

Nested Loop (cost=0.85..33.33 rows=1 width=37) (actual time=0.059..0.165 rows=8 loops=1)

  • Buffers: shared hit=31
19. 0.017 0.089 ↓ 8.0 8 1

Nested Loop (cost=0.56..25.02 rows=1 width=29) (actual time=0.041..0.089 rows=8 loops=1)

  • Buffers: shared hit=7
20. 0.032 0.032 ↓ 2.7 8 1

Index Only Scan using uq_contract_asset on contract_asset coa (cost=0.28..12.51 rows=3 width=8) (actual time=0.022..0.032 rows=8 loops=1)

  • Index Cond: (coa.contractid = 614)
  • Heap Fetches: 8
  • Buffers: shared hit=3
21. 0.014 0.040 ↓ 2.0 4 8

Materialize (cost=0.28..12.42 rows=2 width=21) (actual time=0.003..0.005 rows=4 loops=8)

  • Buffers: shared hit=4
22. 0.026 0.026 ↓ 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.014..0.026 rows=8 loops=1)

  • Index Cond: (a.customerid = 594)
  • Filter: a.active
  • Buffers: shared hit=4
23. 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: ((ascope.assetid = a.assetid) AND (ascope.scopeid = 1))
  • Heap Fetches: 8
  • Buffers: shared hit=24
24. 360.136 360.136 ↓ 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.018..45.017 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
25. 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)
  • Buffers: shared hit=1274915
26. 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=20) (actual time=0.002..0.002 rows=0 loops=318,295)

  • Index Cond: ((i.incidentstatusid = 2) AND (i.customerid = 594) AND (i.detectionlinkid = dl.detectionlinkid) AND (i.enforcementlevelid = 8))
  • Buffers: shared hit=1274780
27. 1.170 1.170 ↓ 0.0 0 1,170

Index Scan using uq_link_linkid_linktypeid on link cl (cost=0.44..7.69 rows=1 width=9) (actual time=0.001..0.001 rows=0 loops=1,170)

  • Index Cond: ((cl.linkid = i.contentlinkid) AND (cl.linktypeid = 2))
  • Filter: (cl.customerid = 594)
  • Buffers: shared hit=1512
28. 2.336 2.336 ↑ 1.0 1 1,168

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,168)

  • Index Cond: (co.contractid = 614)
  • Filter: co.active
  • Buffers: shared hit=3504
29. 1.168 1.168 ↑ 1.0 1 1,168

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

  • Index Cond: (scope.scopeid = 1)
  • Heap Fetches: 1168
  • Buffers: shared hit=3504
30. 2.336 2.336 ↑ 1.0 1 1,168

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

  • Index Cond: (d.domainid = l.domainid)
  • Filter: d.active
  • Buffers: shared hit=3504
31. 1.168 1.168 ↑ 1.0 1 1,168

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

  • Index Cond: (cfg.domainconfigurationid = d.domainconfigurationid)
  • Buffers: shared hit=2640
32. 1.168 1.168 ↓ 0.0 0 1,168

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

  • Index Cond: (md.domainid = d.masterdomainid)
  • Filter: (md.hierarchylevelid = 1)
  • Buffers: shared hit=864
33. 0.000 0.000 ↓ 0.0 0 1,168

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

  • Index Cond: (mcfg.domainconfigurationid = md.domainconfigurationid)
  • Buffers: shared hit=864
34. 0.000 0.000 ↓ 0.0 0 1,168

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

35. 1.168 1.168 ↓ 0.0 0 1,168

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

  • Index Cond: (d.domainid = dc.domainid)
  • Buffers: shared hit=1168
36. 0.000 0.000 ↓ 0.0 0 1,168

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

  • Index Cond: (ccd.customerdivisionid = c.customerdivisionid)
  • Filter: (ccd.contractid = 614)
37. 1.168 1.168 ↓ 0.0 0 1,168

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

  • Index Cond: ((cc.scopeid = ascope.scopeid) AND (cc.scopeid = 1))
  • Buffers: shared hit=1168
Planning time : 31.741 ms
Execution time : 1,966.232 ms