explain.depesz.com

PostgreSQL's explain analyze made readable

Result: t9MN

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

Limit (cost=18,257.89..18,257.93 rows=1 width=586) (actual time=10,955.82..10,955.841 rows=20 loops=1)

  • Buffers: shared hit=3924474
2. 0.021 10,955.829 ↓ 21.0 21 1

GroupAggregate (cost=18,257.85..18,257.89 rows=1 width=586) (actual time=10,955.818..10,955.829 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=3924474
3. 2.467 10,955.808 ↓ 23.0 23 1

Sort (cost=18,257.85..18,257.85 rows=1 width=586) (actual time=10,955.807..10,955.808 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=3924474
4. 1.956 10,953.341 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.75..18,257.84 rows=1 width=586) (actual time=0.828..10,953.341 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=3924474
5. 2.048 10,950.217 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.59..18,257.35 rows=1 width=198) (actual time=0.814..10,950.217 rows=1,168 loops=1)

  • Buffers: shared hit=3923306
6. 0.747 10,948.169 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.45..18,256.29 rows=1 width=198) (actual time=0.802..10,948.169 rows=1,168 loops=1)

  • Buffers: shared hit=3923306
7. 1.040 10,947.422 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.45..18,244.54 rows=1 width=198) (actual time=0.8..10,947.422 rows=1,168 loops=1)

  • Buffers: shared hit=3923306
8. 2.325 10,946.382 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.45..18,233.42 rows=1 width=198) (actual time=0.797..10,946.382 rows=1,168 loops=1)

  • Buffers: shared hit=3923306
9. 1.355 10,944.057 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.16..18,227.27 rows=1 width=144) (actual time=0.787..10,944.057 rows=1,168 loops=1)

  • Buffers: shared hit=3922442
10. 2.491 10,941.534 ↓ 1,168.0 1,168 1

Nested Loop (cost=2.87..18,226.86 rows=1 width=148) (actual time=0.775..10,941.534 rows=1,168 loops=1)

  • Buffers: shared hit=3921578
11. 1.661 10,937.875 ↓ 1,168.0 1,168 1

Nested Loop (cost=2.58..18,226.49 rows=1 width=90) (actual time=0.763..10,937.875 rows=1,168 loops=1)

  • Buffers: shared hit=3918938
12. 0.353 10,932.710 ↓ 1,168.0 1,168 1

Nested Loop (cost=2.29..18,226.18 rows=1 width=62) (actual time=0.749..10,932.71 rows=1,168 loops=1)

  • Buffers: shared hit=3915434
13. 0.068 0.068 ↑ 1.0 1 1

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

  • Filter: (scope.scopeid = 1)
  • Buffers: shared hit=12
14. 0.376 10,932.289 ↓ 1,168.0 1,168 1

Nested Loop (cost=2.29..18,203.09 rows=1 width=62) (actual time=0.741..10,932.289 rows=1,168 loops=1)

  • Buffers: shared hit=3915422
15. 0.120 0.120 ↑ 1.0 1 1

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

  • Filter: (co.active AND (co.contractid = 614))
  • Buffers: shared hit=13
16. 0.976 10,931.793 ↓ 1,168.0 1,168 1

Nested Loop (cost=2.29..18,179.61 rows=1 width=58) (actual time=0.658..10,931.793 rows=1,168 loops=1)

  • Filter: ((cl.linkid IS NULL) OR cl.hasminimumdata)
  • Buffers: shared hit=3915409
17. 149.087 2,008.397 ↓ 1,170.0 1,170 1

Nested Loop (cost=1.86..3,767.09 rows=1 width=62) (actual time=0.648..2,008.397 rows=1,170 loops=1)

  • Buffers: shared hit=2244271
18. 158.364 1,222.720 ↓ 318,295.0 318,295 1

Nested Loop (cost=1.43..3,766.6 rows=1 width=66) (actual time=0.323..1,222.72 rows=318,295 loops=1)

  • Buffers: shared hit=1287041
19. 50.865 427.764 ↓ 318,296.0 318,296 1

Nested Loop (cost=1..3,764.04 rows=1 width=53) (actual time=0.311..427.764 rows=318,296 loops=1)

  • Buffers: shared hit=12126
20. 0.029 0.451 ↓ 8.0 8 1

Nested Loop (cost=0.57..116 rows=1 width=37) (actual time=0.275..0.451 rows=8 loops=1)

  • Buffers: shared hit=70
21. 0.014 0.366 ↓ 8.0 8 1

Nested Loop (cost=0.28..107.69 rows=1 width=29) (actual time=0.254..0.366 rows=8 loops=1)

  • Buffers: shared hit=46
22. 0.304 0.304 ↓ 2.7 8 1

Seq Scan on contract_asset coa (cost=0..95.19 rows=3 width=8) (actual time=0.234..0.304 rows=8 loops=1)

  • Filter: (coa.contractid = 614)
  • Buffers: shared hit=42
23. 0.019 0.048 ↓ 2.0 4 8

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

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

  • Index Cond: (a.customerid = 594)
  • Filter: a.active
  • Buffers: shared hit=4
25. 0.056 0.056 ↑ 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.007..0.007 rows=1 loops=8)

  • Index Cond: ((ascope.assetid = a.assetid) AND (ascope.scopeid = 1))
  • Heap Fetches: 8
  • Buffers: shared hit=24
26. 26.784 376.448 ↑ 1.1 39,787 8

Append (cost=0.43..3,109.55 rows=43,079 width=16) (actual time=0.022..47.056 rows=39,787 loops=8)

  • Buffers: shared hit=12056
27. 349.664 349.664 ↑ 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,894.16 rows=43,079 width=16) (actual time=0.02..43.708 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
28. 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
29. 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
30. 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
31. 1.170 8,922.420 ↓ 0.0 0 1,170

Append (cost=0.43..14,412.5 rows=1 width=5) (actual time=5.591..7.626 rows=0 loops=1,170)

  • Buffers: shared hit=1671138
32. 8,921.250 8,921.250 ↓ 0.0 0 1,170

Index Scan using link_partitioned_594_customerid_assetid_linkid_linktypeid_idx on link_partitioned_594 cl (cost=0.43..14,412.5 rows=1 width=5) (actual time=5.589..7.625 rows=0 loops=1,170)

  • Index Cond: ((cl.customerid = 594) AND (cl.linkid = i.contentlinkid) AND (cl.linktypeid = 2))
  • Buffers: shared hit=1671138
33. 3.504 3.504 ↑ 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.003..0.003 rows=1 loops=1,168)

  • Index Cond: (d.domainid = l.domainid)
  • Filter: d.active
  • Buffers: shared hit=3504
34. 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
35. 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
36. 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
37. 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)

38. 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)

39. 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)
40. 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 : 26.893 ms
Execution time : 10,956.08 ms