explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hSCY

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 1,983.962 ↓ 20.0 20 1

Limit (cost=3,765.61..3,765.65 rows=1 width=586) (actual time=1,983.941..1,983.962 rows=20 loops=1)

  • Buffers: shared hit=2254832
2. 0.026 1,983.950 ↓ 21.0 21 1

GroupAggregate (cost=3,765.57..3,765.61 rows=1 width=586) (actual time=1,983.939..1,983.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, l.reopenfromlinkid
  • Buffers: shared hit=2254832
3. 1.785 1,983.924 ↓ 23.0 23 1

Sort (cost=3,765.57..3,765.57 rows=1 width=586) (actual time=1,983.923..1,983.924 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, l.reopenfromlinkid
  • Sort Method: quicksort Memory: 213kB
  • Buffers: shared hit=2254832
4. 0.699 1,982.139 ↓ 1,168.0 1,168 1

Nested Loop (cost=4.03..3,765.56 rows=1 width=586) (actual time=0.586..1,982.139 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=2254832
5. 1.229 1,980.272 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.88..3,765.07 rows=1 width=198) (actual time=0.574..1,980.272 rows=1,168 loops=1)

  • Buffers: shared hit=2253664
6. 0.488 1,979.043 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.73..3,764.02 rows=1 width=198) (actual time=0.566..1,979.043 rows=1,168 loops=1)

  • Buffers: shared hit=2253664
7. 0.572 1,978.555 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.73..3,752.27 rows=1 width=198) (actual time=0.564..1,978.555 rows=1,168 loops=1)

  • Buffers: shared hit=2253664
8. 1.586 1,977.983 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.73..3,741.14 rows=1 width=198) (actual time=0.56..1,977.983 rows=1,168 loops=1)

  • Buffers: shared hit=2253664
9. 1.701 1,976.397 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.44..3,734.99 rows=1 width=144) (actual time=0.55..1,976.397 rows=1,168 loops=1)

  • Buffers: shared hit=2252800
10. 1.163 1,974.696 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.15..3,734.58 rows=1 width=148) (actual time=0.543..1,974.696 rows=1,168 loops=1)

  • Buffers: shared hit=2251936
11. 0.615 1,972.365 ↓ 1,168.0 1,168 1

Nested Loop (cost=2.86..3,734.21 rows=1 width=90) (actual time=0.531..1,972.365 rows=1,168 loops=1)

  • Buffers: shared hit=2249296
12. 0.255 1,969.414 ↓ 1,168.0 1,168 1

Nested Loop (cost=2.57..3,733.9 rows=1 width=62) (actual time=0.517..1,969.414 rows=1,168 loops=1)

  • Buffers: shared hit=2245792
13. 0.066 0.066 ↑ 1.0 1 1

Seq Scan on scope scope (cost=0..23.08 rows=1 width=4) (actual time=0.006..0.066 rows=1 loops=1)

  • Filter: (scope.scopeid = 1)
  • Buffers: shared hit=12
14. 0.215 1,969.093 ↓ 1,168.0 1,168 1

Nested Loop (cost=2.57..3,710.81 rows=1 width=62) (actual time=0.509..1,969.093 rows=1,168 loops=1)

  • Filter: ((cl.linkid IS NULL) OR cl.hasminimumdata)
  • Buffers: shared hit=2245780
15. 114.253 1,966.538 ↓ 1,170.0 1,170 1

Nested Loop (cost=2.15..3,703.56 rows=1 width=70) (actual time=0.499..1,966.538 rows=1,170 loops=1)

  • Buffers: shared hit=2244266
16. 146.986 1,215.695 ↓ 318,295.0 318,295 1

Nested Loop (cost=1.71..3,703.07 rows=1 width=70) (actual time=0.175..1,215.695 rows=318,295 loops=1)

  • Buffers: shared hit=1287036
17. 51.067 432.117 ↓ 318,296.0 318,296 1

Nested Loop (cost=1.28..3,700.51 rows=1 width=57) (actual time=0.162..432.117 rows=318,296 loops=1)

  • Buffers: shared hit=12121
18. 0.005 0.306 ↓ 8.0 8 1

Nested Loop (cost=0.85..53.25 rows=1 width=41) (actual time=0.121..0.306 rows=8 loops=1)

  • Buffers: shared hit=65
19. 0.115 0.115 ↑ 1.0 1 1

Seq Scan on contract co (cost=0..23.48 rows=1 width=8) (actual time=0.08..0.115 rows=1 loops=1)

  • Filter: (co.active AND (co.contractid = 614))
  • Buffers: shared hit=13
20. 0.030 0.186 ↓ 8.0 8 1

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

  • Buffers: shared hit=52
21. 0.028 0.108 ↓ 4.0 8 1

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

  • Buffers: shared hit=28
22. 0.032 0.032 ↓ 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.016..0.032 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. 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
25. 29.296 380.744 ↑ 1.1 39,787 8

Append (cost=0.43..3,108.9 rows=43,069 width=16) (actual time=0.022..47.593 rows=39,787 loops=8)

  • Buffers: shared hit=12056
26. 351.448 351.448 ↑ 1.1 39,787 8

Index Scan using link_partitioned_594_customerid_assetid_linkid_linktypeid_idx on link_partitioned_594 l (cost=0.43..2,893.56 rows=43,069 width=16) (actual time=0.02..43.931 rows=39,787 loops=8)

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

Index Scan using idx_detection_link on detectionlink dl (cost=0.43..2.56 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
28. 0.000 636.590 ↓ 0.0 0 318,295

Append (cost=0.43..0.48 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=318,295)

  • Buffers: shared hit=957230
29. 636.590 636.590 ↓ 0.0 0 318,295

Index Scan using incident_partitioned_594_customerid_detectionlinkid_inciden_idx on incident_partitioned_594 i (cost=0.43..0.47 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=318,295)

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

Append (cost=0.43..7.24 rows=1 width=9) (actual time=0.002..0.002 rows=0 loops=1,170)

  • Buffers: shared hit=1514
31. 1.170 1.170 ↓ 0.0 0 1,170

Index Scan using link_partitioned_594_customerid_assetid_linkid_linktypeid_idx on link_partitioned_594 cl (cost=0.43..7.23 rows=1 width=9) (actual time=0.001..0.001 rows=0 loops=1,170)

  • Index Cond: ((cl.customerid = 594) AND (cl.assetid = l.assetid) AND (cl.linkid = i.contentlinkid) AND (cl.linktypeid = 2))
  • Buffers: shared hit=1514
32. 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
33. 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
34. 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
35. 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
36. 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)

37. 0.000 0.000 ↓ 0.0 0 1,168

Seq Scan on domain_customerdivision dc (cost=0..10.7 rows=70 width=8) (actual time=0..0 rows=0 loops=1,168)

38. 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)
39. 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 : 20.784 ms
Execution time : 1,984.201 ms