explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4X0p

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 1,170.617 ↓ 20.0 20 1

Limit (cost=1,729.56..1,729.6 rows=1 width=586) (actual time=1,170.597..1,170.617 rows=20 loops=1)

  • Buffers: shared hit=28752
2. 0.021 1,170.606 ↓ 21.0 21 1

GroupAggregate (cost=1,729.52..1,729.56 rows=1 width=586) (actual time=1,170.595..1,170.606 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=28752
3. 1.696 1,170.585 ↓ 23.0 23 1

Sort (cost=1,729.52..1,729.53 rows=1 width=586) (actual time=1,170.584..1,170.585 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=28752
4. 0.607 1,168.889 ↓ 1,168.0 1,168 1

Nested Loop (cost=4.17..1,729.51 rows=1 width=586) (actual time=1.469..1,168.889 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=28752
5. 1.192 1,167.114 ↓ 1,168.0 1,168 1

Nested Loop (cost=4.02..1,729.03 rows=1 width=198) (actual time=1.457..1,167.114 rows=1,168 loops=1)

  • Buffers: shared hit=27584
6. 0.472 1,165.922 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.88..1,727.97 rows=1 width=198) (actual time=1.449..1,165.922 rows=1,168 loops=1)

  • Buffers: shared hit=27584
7. 0.539 1,165.450 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.88..1,716.22 rows=1 width=198) (actual time=1.448..1,165.45 rows=1,168 loops=1)

  • Buffers: shared hit=27584
8. 1.585 1,164.911 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.88..1,705.1 rows=1 width=198) (actual time=1.446..1,164.911 rows=1,168 loops=1)

  • Buffers: shared hit=27584
9. 1.671 1,163.326 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.59..1,698.95 rows=1 width=144) (actual time=1.437..1,163.326 rows=1,168 loops=1)

  • Buffers: shared hit=26720
10. 1.083 1,161.655 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.3..1,698.54 rows=1 width=148) (actual time=1.427..1,161.655 rows=1,168 loops=1)

  • Buffers: shared hit=25856
11. 1.717 1,159.404 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.01..1,698.17 rows=1 width=90) (actual time=1.416..1,159.404 rows=1,168 loops=1)

  • Buffers: shared hit=23216
12. 0.256 1,156.519 ↓ 1,168.0 1,168 1

Nested Loop (cost=2.72..1,697.85 rows=1 width=62) (actual time=1.403..1,156.519 rows=1,168 loops=1)

  • Buffers: shared hit=19712
13. 0.067 0.067 ↑ 1.0 1 1

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

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

Nested Loop (cost=2.72..1,674.77 rows=1 width=62) (actual time=1.395..1,156.196 rows=1,168 loops=1)

  • Filter: ((cl.linkid IS NULL) OR cl.hasminimumdata)
  • Buffers: shared hit=19700
15. 0.787 1,153.984 ↓ 1,170.0 1,170 1

Nested Loop (cost=2.28..1,667.06 rows=1 width=70) (actual time=1.381..1,153.984 rows=1,170 loops=1)

  • Buffers: shared hit=18188
16. 0.894 1,149.687 ↓ 1,170.0 1,170 1

Nested Loop (cost=1.84..1,664.49 rows=1 width=65) (actual time=1.367..1,149.687 rows=1,170 loops=1)

  • Buffers: shared hit=13492
17. 0.341 1,143.293 ↓ 1,375.0 1,375 1

Nested Loop (cost=1.42..1,660.22 rows=1 width=61) (actual time=1.347..1,143.293 rows=1,375 loops=1)

  • Buffers: shared hit=7956
18. 0.006 0.296 ↓ 8.0 8 1

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

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

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

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

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

  • Buffers: shared hit=52
21. 0.030 0.107 ↓ 4.0 8 1

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

  • Buffers: shared hit=28
22. 0.029 0.029 ↓ 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.015..0.029 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.040 0.040 ↑ 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.005..0.005 rows=1 loops=8)

  • Index Cond: (coa.assetid = ascope.assetid)
  • Filter: (coa.contractid = 614)
  • Buffers: shared hit=24
25. 1,142.656 1,142.656 ↓ 11.5 172 8

Index Scan using idx_incident_asset on incident i (cost=0.56..1,606.79 rows=15 width=20) (actual time=86.954..142.832 rows=172 loops=8)

  • Index Cond: (i.assetid = ascope.assetid)
  • Filter: ((NOT i.deleted) AND (i.incidentstatusid = 2) AND (i.enforcementlevelid = 8) AND (i.customerid = 594))
  • Buffers: shared hit=7891
26. 1.375 5.500 ↑ 1.0 1 1,375

Append (cost=0.43..4.26 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=1,375)

  • Buffers: shared hit=5536
27. 4.125 4.125 ↑ 1.0 1 1,375

Index Scan using link_partitioned_594_customerid_assetid_linkid_linktypeid_idx on link_partitioned_594 l (cost=0.43..4.25 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=1,375)

  • Index Cond: ((l.customerid = 594) AND (l.assetid = i.assetid) AND (l.linkid = i.detectionlinkid) AND (l.linktypeid = 1))
  • Filter: (l.hasminimumdata AND (l.enforcementstatusid = 6))
  • Buffers: shared hit=5536
28. 3.510 3.510 ↑ 1.0 1 1,170

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

  • Index Cond: (dl.detectionlinkid = l.linkid)
  • 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..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
30. 1.168 1.168 ↑ 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.001..0.001 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. 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
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. 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)

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 : 22.859 ms
Execution time : 1,170.855 ms