explain.depesz.com

PostgreSQL's explain analyze made readable

Result: grkL

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 3,642.221 ↓ 40.0 40 1

Limit (cost=83.1..83.14 rows=1 width=586) (actual time=3,642.168..3,642.221 rows=40 loops=1)

  • Buffers: shared hit=3857554
2. 0.048 3,642.205 ↓ 41.0 41 1

GroupAggregate (cost=83.06..83.1 rows=1 width=586) (actual time=3,642.165..3,642.205 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=3857554
3. 248.850 3,642.157 ↓ 105.0 105 1

Sort (cost=83.06..83.07 rows=1 width=586) (actual time=3,642.148..3,642.157 rows=105 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: 20778kB
  • Buffers: shared hit=3857554
4. 145.315 3,393.307 ↓ 125,904.0 125,904 1

Nested Loop (cost=5.01..83.05 rows=1 width=586) (actual time=0.278..3,393.307 rows=125,904 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=3857540
5. 103.616 3,247.992 ↓ 125,904.0 125,904 1

Nested Loop (cost=4.86..82.56 rows=1 width=198) (actual time=0.265..3,247.992 rows=125,904 loops=1)

  • Buffers: shared hit=3731636
6. 138.491 3,144.376 ↓ 125,904.0 125,904 1

Nested Loop (cost=4.72..81.51 rows=1 width=198) (actual time=0.254..3,144.376 rows=125,904 loops=1)

  • Buffers: shared hit=3731636
7. 147.675 3,005.885 ↓ 125,904.0 125,904 1

Nested Loop (cost=4.58..81.34 rows=1 width=198) (actual time=0.241..3,005.885 rows=125,904 loops=1)

  • Buffers: shared hit=3605732
8. 114.530 2,858.210 ↓ 125,904.0 125,904 1

Nested Loop (cost=4.44..73.17 rows=1 width=198) (actual time=0.221..2,858.21 rows=125,904 loops=1)

  • Buffers: shared hit=3479828
9. 126.602 2,743.680 ↓ 125,904.0 125,904 1

Nested Loop (cost=4.15..67.02 rows=1 width=144) (actual time=0.206..2,743.68 rows=125,904 loops=1)

  • Buffers: shared hit=3433025
10. 100.356 2,617.078 ↓ 125,904.0 125,904 1

Nested Loop (cost=3.86..66.61 rows=1 width=148) (actual time=0.191..2,617.078 rows=125,904 loops=1)

  • Buffers: shared hit=3386222
11. 78.884 2,390.818 ↓ 125,904.0 125,904 1

Nested Loop (cost=3.57..66.24 rows=1 width=90) (actual time=0.18..2,390.818 rows=125,904 loops=1)

  • Filter: ((cl.linkid IS NULL) OR cl.hasminimumdata)
  • Buffers: shared hit=3055313
12. 117.668 2,186.004 ↓ 125,930.0 125,930 1

Nested Loop (cost=3.13..57.8 rows=1 width=98) (actual time=0.169..2,186.004 rows=125,930 loops=1)

  • Buffers: shared hit=2898745
13. 138.807 1,816.476 ↓ 125,930.0 125,930 1

Nested Loop (cost=2.7..55.62 rows=1 width=93) (actual time=0.148..1,816.476 rows=125,930 loops=1)

  • Buffers: shared hit=2394329
14. 125.624 1,551.739 ↓ 125,930.0 125,930 1

Nested Loop (cost=2.41..55.3 rows=1 width=65) (actual time=0.133..1,551.739 rows=125,930 loops=1)

  • Buffers: shared hit=2016539
15. 131.627 1,300.185 ↓ 125,930.0 125,930 1

Nested Loop (cost=2.12..54.99 rows=1 width=73) (actual time=0.119..1,300.185 rows=125,930 loops=1)

  • Buffers: shared hit=1638749
16. 127.066 1,042.628 ↓ 125,930.0 125,930 1

Nested Loop (cost=1.85..46.68 rows=1 width=73) (actual time=0.1..1,042.628 rows=125,930 loops=1)

  • Buffers: shared hit=1260959
17. 156.718 789.632 ↓ 125,930.0 125,930 1

Nested Loop (cost=1.56..46.36 rows=1 width=65) (actual time=0.075..789.632 rows=125,930 loops=1)

  • Buffers: shared hit=883169
18. 136.894 380.984 ↓ 125,965.0 125,965 1

Nested Loop (cost=1.12..37.92 rows=1 width=49) (actual time=0.051..380.984 rows=125,965 loops=1)

  • Buffers: shared hit=378623
19. 16.132 118.125 ↓ 125,965.0 125,965 1

Nested Loop (cost=0.85..29.61 rows=1 width=41) (actual time=0.036..118.125 rows=125,965 loops=1)

  • Buffers: shared hit=728
20. 0.025 0.025 ↓ 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.025 rows=8 loops=1)

  • Index Cond: (a.customerid = 594)
  • Filter: a.active
  • Buffers: shared hit=4
21. 101.968 101.968 ↓ 15,746.0 15,746 8

Index Scan using incident__index1 on incident i (cost=0.56..8.59 rows=1 width=20) (actual time=0.018..12.746 rows=15,746 loops=8)

  • Index Cond: ((i.customerid = 594) AND (i.contractid = 614) AND (i.assetid = a.assetid) AND (i.incidentstatusid = 12) AND (i.enforcementlevelid = 8))
  • Buffers: shared hit=724
22. 125.965 125.965 ↑ 1.0 1 125,965

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=125,965)

  • Index Cond: (co.contractid = 614)
  • Filter: co.active
  • Buffers: shared hit=377895
23. 251.930 251.930 ↑ 1.0 1 125,965

Index Scan using uq_link_linkid_linktypeid on link l (cost=0.44..8.43 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=125,965)

  • 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=504546
24. 125.930 125.930 ↑ 1.0 1 125,930

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=125,930)

  • Index Cond: ((ascope.assetid = l.assetid) AND (ascope.scopeid = 1))
  • Heap Fetches: 125930
  • Buffers: shared hit=377790
25. 125.930 125.930 ↑ 1.0 1 125,930

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=125,930)

  • Index Cond: (scope.scopeid = 1)
  • Heap Fetches: 125930
  • Buffers: shared hit=377790
26. 125.930 125.930 ↑ 1.0 1 125,930

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=125,930)

  • Index Cond: (coa.assetid = l.assetid)
  • Filter: (coa.contractid = 614)
  • Buffers: shared hit=377790
27. 125.930 125.930 ↑ 1.0 1 125,930

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=125,930)

  • Index Cond: (d.domainid = l.domainid)
  • Filter: d.active
  • Buffers: shared hit=377790
28. 251.860 251.860 ↑ 1.0 1 125,930

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

  • Index Cond: ((dl.detectionlinkid = l.linkid) AND (dl.linktypeid = 1))
  • Buffers: shared hit=504416
29. 125.930 125.930 ↓ 0.0 0 125,930

Index Scan using uq_link_linkid_linktypeid on link cl (cost=0.44..8.43 rows=1 width=9) (actual time=0.001..0.001 rows=0 loops=125,930)

  • Index Cond: ((cl.linkid = i.contentlinkid) AND (cl.linktypeid = 2))
  • Filter: ((NOT cl.deleted) AND (cl.customerid = 594))
  • Buffers: shared hit=156568
30. 125.904 125.904 ↑ 1.0 1 125,904

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=125,904)

  • Index Cond: (cfg.domainconfigurationid = d.domainconfigurationid)
  • Buffers: shared hit=330909
31. 0.000 0.000 ↓ 0.0 0 125,904

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=125,904)

  • Index Cond: (md.domainid = d.masterdomainid)
  • Filter: (md.hierarchylevelid = 1)
  • Buffers: shared hit=46803
32. 0.000 0.000 ↓ 0.0 0 125,904

Index Scan using pk_domainconfiguration on domainconfiguration mcfg (cost=0.29..6.15 rows=1 width=62) (actual time=0..0 rows=0 loops=125,904)

  • Index Cond: (mcfg.domainconfigurationid = md.domainconfigurationid)
  • Buffers: shared hit=46803
33. 0.000 0.000 ↓ 0.0 0 125,904

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=125,904)

  • Index Cond: (c.customerid = co.customerid)
  • Buffers: shared hit=125904
34. 0.000 0.000 ↓ 0.0 0 125,904

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=125,904)

  • Index Cond: (d.domainid = dc.domainid)
  • Buffers: shared hit=125904
35. 0.000 0.000 ↓ 0.0 0 125,904

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=125,904)

  • Index Cond: (ccd.customerdivisionid = c.customerdivisionid)
  • Filter: (ccd.contractid = 614)
36. 0.000 0.000 ↓ 0.0 0 125,904

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=125,904)

  • Index Cond: ((cc.scopeid = ascope.scopeid) AND (cc.scopeid = 1))
  • Buffers: shared hit=125904
Planning time : 89.666 ms
Execution time : 3,643.696 ms