explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xSOEs

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

Limit (cost=3,916.4..3,916.44 rows=1 width=586) (actual time=1,132.305..1,132.325 rows=20 loops=1)

  • Buffers: shared hit=998511
2. 0.021 1,132.314 ↓ 21.0 21 1

GroupAggregate (cost=3,916.36..3,916.4 rows=1 width=586) (actual time=1,132.303..1,132.314 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=998511
3. 2.046 1,132.293 ↓ 23.0 23 1

Sort (cost=3,916.36..3,916.37 rows=1 width=586) (actual time=1,132.292..1,132.293 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: 242kB
  • Buffers: shared hit=998511
4. 0.648 1,130.247 ↓ 1,373.0 1,373 1

Nested Loop (cost=4.3..3,916.35 rows=1 width=586) (actual time=0.403..1,130.247 rows=1,373 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=998511
5. 1.329 1,128.226 ↓ 1,373.0 1,373 1

Nested Loop (cost=4.15..3,915.87 rows=1 width=198) (actual time=0.388..1,128.226 rows=1,373 loops=1)

  • Buffers: shared hit=997138
6. 0.519 1,126.897 ↓ 1,373.0 1,373 1

Nested Loop (cost=4.01..3,914.81 rows=1 width=198) (actual time=0.377..1,126.897 rows=1,373 loops=1)

  • Buffers: shared hit=997138
7. 0.581 1,126.378 ↓ 1,373.0 1,373 1

Nested Loop (cost=4.01..3,903.06 rows=1 width=198) (actual time=0.375..1,126.378 rows=1,373 loops=1)

  • Buffers: shared hit=997138
8. 1.703 1,125.797 ↓ 1,373.0 1,373 1

Nested Loop (cost=4.01..3,891.94 rows=1 width=198) (actual time=0.372..1,125.797 rows=1,373 loops=1)

  • Buffers: shared hit=997138
9. 1.833 1,124.094 ↓ 1,373.0 1,373 1

Nested Loop (cost=3.72..3,885.79 rows=1 width=144) (actual time=0.364..1,124.094 rows=1,373 loops=1)

  • Buffers: shared hit=996220
10. 1.174 1,122.261 ↓ 1,373.0 1,373 1

Nested Loop (cost=3.43..3,885.38 rows=1 width=148) (actual time=0.356..1,122.261 rows=1,373 loops=1)

  • Buffers: shared hit=995302
11. 0.000 1,119.714 ↓ 1,373.0 1,373 1

Nested Loop (cost=3.14..3,885.01 rows=1 width=90) (actual time=0.343..1,119.714 rows=1,373 loops=1)

  • Filter: ((cl.linkid IS NULL) OR cl.hasminimumdata)
  • Buffers: shared hit=992101
12. 2.052 1,117.025 ↓ 1,375.0 1,375 1

Nested Loop (cost=2.71..3,877.76 rows=1 width=98) (actual time=0.333..1,117.025 rows=1,375 loops=1)

  • Buffers: shared hit=990587
13. 1.812 1,112.223 ↓ 1,375.0 1,375 1

Nested Loop (cost=2.27..3,875.38 rows=1 width=93) (actual time=0.319..1,112.223 rows=1,375 loops=1)

  • Buffers: shared hit=985069
14. 1.670 1,109.036 ↓ 1,375.0 1,375 1

Nested Loop (cost=1.98..3,875.07 rows=1 width=65) (actual time=0.305..1,109.036 rows=1,375 loops=1)

  • Buffers: shared hit=980944
15. 81.887 1,105.991 ↓ 1,375.0 1,375 1

Nested Loop (cost=1.71..3,866.77 rows=1 width=57) (actual time=0.293..1,105.991 rows=1,375 loops=1)

  • Buffers: shared hit=976819
16. 47.655 382.676 ↓ 3,603.5 320,714 1

Nested Loop (cost=1.28..3,772.34 rows=89 width=49) (actual time=0.087..382.676 rows=320,714 loops=1)

  • Buffers: shared hit=12120
17. 0.005 0.237 ↓ 8.0 8 1

Nested Loop (cost=0.85..52.85 rows=1 width=33) (actual time=0.049..0.237 rows=8 loops=1)

  • Buffers: shared hit=64
18. 0.071 0.071 ↑ 1.0 1 1

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

  • Filter: (scope.scopeid = 1)
  • Buffers: shared hit=12
19. 0.031 0.161 ↓ 8.0 8 1

Nested Loop (cost=0.85..29.76 rows=1 width=33) (actual time=0.041..0.161 rows=8 loops=1)

  • Buffers: shared hit=52
20. 0.029 0.098 ↓ 4.0 8 1

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

  • Buffers: shared hit=28
21. 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.017..0.029 rows=8 loops=1)

  • Index Cond: (a.customerid = 594)
  • Filter: a.active
  • Buffers: shared hit=4
22. 0.040 0.040 ↑ 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.005..0.005 rows=1 loops=8)

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

Index Scan using idx_contract_asset__asset on contract_asset coa (cost=0.28..0.36 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=8)

  • Index Cond: (coa.assetid = ascope.assetid)
  • Filter: (coa.contractid = 614)
  • Buffers: shared hit=24
24. 24.696 334.784 ↑ 1.2 40,089 8

Append (cost=0.43..3,129.54 rows=47,196 width=16) (actual time=0.021..41.848 rows=40,089 loops=8)

  • Buffers: shared hit=12056
25. 310.088 310.088 ↑ 1.2 40,089 8

Index Scan using link_partitioned_594_customerid_assetid_linkid_linktypeid_idx on link_partitioned_594 l (cost=0.43..2,893.56 rows=47,196 width=16) (actual time=0.019..38.761 rows=40,089 loops=8)

  • Index Cond: ((l.customerid = 594) AND (l.assetid = ascope.assetid) AND (l.linktypeid = 1))
  • Filter: (l.enforcementstatusid = 6)
  • Buffers: shared hit=12056
26. 0.000 641.428 ↓ 0.0 0 320,714

Append (cost=0.43..1.05 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=320,714)

  • Buffers: shared hit=964699
27. 641.428 641.428 ↓ 0.0 0 320,714

Index Scan using incident_partitioned_594_customerid_detectionlinkid_inciden_idx on incident_partitioned_594 i (cost=0.43..1.04 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=320,714)

  • Index Cond: ((i.customerid = 594) AND (i.detectionlinkid = l.linkid) AND (i.incidentstatusid = 2) AND (i.enforcementlevelid = 8))
  • Filter: ((i.contractid = 614) AND (l.assetid = i.assetid))
  • Buffers: shared hit=964699
28. 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
29. 1.375 1.375 ↑ 1.0 1 1,375

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

  • Index Cond: (d.domainid = l.domainid)
  • Filter: d.active
  • Buffers: shared hit=4125
30. 2.750 2.750 ↑ 1.0 1 1,375

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

  • Index Cond: ((dl.detectionlinkid = l.linkid) AND (dl.linktypeid = 1))
  • Buffers: shared hit=5518
31. 1.375 2.750 ↓ 0.0 0 1,375

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

  • Buffers: shared hit=1514
32. 1.375 1.375 ↓ 0.0 0 1,375

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

  • Index Cond: ((cl.customerid = 594) AND (cl.assetid = l.assetid) AND (cl.linkid = i.contentlinkid) AND (cl.linktypeid = 2))
  • Buffers: shared hit=1514
33. 1.373 1.373 ↑ 1.0 1 1,373

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

  • Index Cond: (cfg.domainconfigurationid = d.domainconfigurationid)
  • Buffers: shared hit=3201
34. 0.000 0.000 ↓ 0.0 0 1,373

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

  • Index Cond: (md.domainid = d.masterdomainid)
  • Filter: (md.hierarchylevelid = 1)
  • Buffers: shared hit=918
35. 0.000 0.000 ↓ 0.0 0 1,373

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

  • Index Cond: (mcfg.domainconfigurationid = md.domainconfigurationid)
  • Buffers: shared hit=918
36. 0.000 0.000 ↓ 0.0 0 1,373

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

37. 0.000 0.000 ↓ 0.0 0 1,373

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

38. 0.000 0.000 ↓ 0.0 0 1,373

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

  • Index Cond: (ccd.customerdivisionid = c.customerdivisionid)
  • Filter: (ccd.contractid = 614)
39. 1.373 1.373 ↓ 0.0 0 1,373

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

  • Index Cond: ((cc.scopeid = ascope.scopeid) AND (cc.scopeid = 1))
  • Buffers: shared hit=1373
Planning time : 52.372 ms
Execution time : 1,132.568 ms