explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rN15

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 38.138 ↓ 40.0 40 1

Limit (cost=83.06..83.1 rows=1 width=586) (actual time=38.109..38.138 rows=40 loops=1)

  • Buffers: shared hit=37849
2. 0.029 38.128 ↓ 41.0 41 1

GroupAggregate (cost=83.02..83.06 rows=1 width=586) (actual time=38.106..38.128 rows=41 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=37849
3. 1.575 38.099 ↓ 52.0 52 1

Sort (cost=83.02..83.03 rows=1 width=586) (actual time=38.096..38.099 rows=52 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=37849
4. 1.516 36.524 ↓ 1,168.0 1,168 1

Nested Loop (cost=5.01..83.01 rows=1 width=586) (actual time=0.24..36.524 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=37849
5. 1.061 35.008 ↓ 1,168.0 1,168 1

Nested Loop (cost=4.86..82.52 rows=1 width=198) (actual time=0.229..35.008 rows=1,168 loops=1)

  • Buffers: shared hit=36681
6. 1.382 33.947 ↓ 1,168.0 1,168 1

Nested Loop (cost=4.72..81.47 rows=1 width=198) (actual time=0.221..33.947 rows=1,168 loops=1)

  • Buffers: shared hit=36681
7. 1.473 32.565 ↓ 1,168.0 1,168 1

Nested Loop (cost=4.58..81.3 rows=1 width=198) (actual time=0.213..32.565 rows=1,168 loops=1)

  • Buffers: shared hit=35513
8. 1.405 31.092 ↓ 1,168.0 1,168 1

Nested Loop (cost=4.44..73.13 rows=1 width=198) (actual time=0.196..31.092 rows=1,168 loops=1)

  • Buffers: shared hit=34345
9. 1.490 29.687 ↓ 1,168.0 1,168 1

Nested Loop (cost=4.15..66.98 rows=1 width=144) (actual time=0.189..29.687 rows=1,168 loops=1)

  • Buffers: shared hit=33481
10. 0.862 28.197 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.86..66.57 rows=1 width=148) (actual time=0.177..28.197 rows=1,168 loops=1)

  • Buffers: shared hit=32617
11. 0.794 26.167 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.57..66.2 rows=1 width=90) (actual time=0.166..26.167 rows=1,168 loops=1)

  • Filter: ((cl.linkid IS NULL) OR cl.hasminimumdata)
  • Buffers: shared hit=29977
12. 0.684 24.203 ↓ 1,170.0 1,170 1

Nested Loop (cost=3.13..57.78 rows=1 width=98) (actual time=0.158..24.203 rows=1,170 loops=1)

  • Buffers: shared hit=28465
13. 1.467 20.009 ↓ 1,170.0 1,170 1

Nested Loop (cost=2.7..55.6 rows=1 width=93) (actual time=0.142..20.009 rows=1,170 loops=1)

  • Buffers: shared hit=23769
14. 1.276 17.372 ↓ 1,170.0 1,170 1

Nested Loop (cost=2.41..55.28 rows=1 width=65) (actual time=0.127..17.372 rows=1,170 loops=1)

  • Buffers: shared hit=20259
15. 1.315 14.926 ↓ 1,170.0 1,170 1

Nested Loop (cost=2.12..54.97 rows=1 width=73) (actual time=0.113..14.926 rows=1,170 loops=1)

  • Buffers: shared hit=16749
16. 1.304 12.441 ↓ 1,170.0 1,170 1

Nested Loop (cost=1.85..46.66 rows=1 width=73) (actual time=0.1..12.441 rows=1,170 loops=1)

  • Buffers: shared hit=13239
17. 1.163 9.967 ↓ 1,170.0 1,170 1

Nested Loop (cost=1.56..46.34 rows=1 width=65) (actual time=0.084..9.967 rows=1,170 loops=1)

  • Buffers: shared hit=9729
18. 1.533 4.679 ↓ 1,375.0 1,375 1

Nested Loop (cost=1.12..37.92 rows=1 width=49) (actual time=0.054..4.679 rows=1,375 loops=1)

  • Buffers: shared hit=4225
19. 0.183 1.771 ↓ 1,375.0 1,375 1

Nested Loop (cost=0.85..29.61 rows=1 width=41) (actual time=0.043..1.771 rows=1,375 loops=1)

  • Buffers: shared hit=100
20. 0.028 0.028 ↓ 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.022..0.028 rows=8 loops=1)

  • Index Cond: (a.customerid = 594)
  • Filter: a.active
  • Buffers: shared hit=4
21. 1.560 1.560 ↓ 172.0 172 8

Index Scan using incident__index1 on incident i (cost=0.56..8.59 rows=1 width=20) (actual time=0.013..0.195 rows=172 loops=8)

  • Index Cond: ((i.customerid = 594) AND (i.contractid = 614) AND (i.assetid = a.assetid) AND (i.incidentstatusid = 2) AND (i.enforcementlevelid = 8))
  • Buffers: shared hit=96
22. 1.375 1.375 ↑ 1.0 1 1,375

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

  • Index Cond: (co.contractid = 614)
  • Filter: co.active
  • Buffers: shared hit=4125
23. 4.125 4.125 ↑ 1.0 1 1,375

Index Scan using uq_link_linkid_linktypeid on link l (cost=0.44..8.41 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=1,375)

  • Index Cond: ((l.linkid = i.detectionlinkid) AND (l.linktypeid = 1))
  • Filter: (l.hasminimumdata AND (NOT l.deleted) AND (l.customerid = 594) AND (l.enforcementstatusid = 6) AND (i.assetid = l.assetid))
  • Buffers: shared hit=5504
24. 1.170 1.170 ↑ 1.0 1 1,170

Index Only Scan using uq_asset_scope on asset_scope ascope (cost=0.29..0.31 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1,170)

  • Index Cond: ((ascope.assetid = l.assetid) AND (ascope.scopeid = 1))
  • Heap Fetches: 1170
  • Buffers: shared hit=3510
25. 1.170 1.170 ↑ 1.0 1 1,170

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

  • Index Cond: (scope.scopeid = 1)
  • Heap Fetches: 1170
  • Buffers: shared hit=3510
26. 1.170 1.170 ↑ 1.0 1 1,170

Index Scan using idx_contract_asset__asset on contract_asset coa (cost=0.28..0.3 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1,170)

  • Index Cond: (coa.assetid = l.assetid)
  • Filter: (coa.contractid = 614)
  • Buffers: shared hit=3510
27. 1.170 1.170 ↑ 1.0 1 1,170

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

  • Index Cond: (d.domainid = l.domainid)
  • Filter: d.active
  • Buffers: shared hit=3510
28. 3.510 3.510 ↑ 1.0 1 1,170

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

  • Index Cond: ((dl.detectionlinkid = l.linkid) AND (dl.linktypeid = 1))
  • Buffers: shared hit=4696
29. 1.170 1.170 ↓ 0.0 0 1,170

Index Scan using uq_link_linkid_linktypeid on link cl (cost=0.44..8.41 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: ((NOT cl.deleted) AND (cl.customerid = 594))
  • Buffers: shared hit=1512
30. 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
31. 0.000 0.000 ↓ 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..0 rows=0 loops=1,168)

  • Index Cond: (md.domainid = d.masterdomainid)
  • Filter: (md.hierarchylevelid = 1)
  • Buffers: shared hit=864
32. 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
33. 0.000 0.000 ↓ 0.0 0 1,168

Index Scan using idx_customerdivision_customer on customerdivision c (cost=0.14..8.16 rows=1 width=8) (actual time=0..0 rows=0 loops=1,168)

  • Index Cond: (c.customerid = co.customerid)
  • Buffers: shared hit=1168
34. 0.000 0.000 ↓ 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..0 rows=0 loops=1,168)

  • Index Cond: (d.domainid = dc.domainid)
  • Buffers: shared hit=1168
35. 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)
36. 0.000 0.000 ↓ 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..0 rows=0 loops=1,168)

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