explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yO6j

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

Limit (cost=71.2..71.24 rows=1 width=586) (actual time=3,434.54..3,434.589 rows=40 loops=1)

  • Buffers: shared hit=3,605,794
2. 0.050 3,434.573 ↓ 41.0 41 1

GroupAggregate (cost=71.17..71.2 rows=1 width=586) (actual time=3,434.536..3,434.573 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=3,605,794
3. 247.673 3,434.523 ↓ 105.0 105 1

Sort (cost=71.17..71.17 rows=1 width=586) (actual time=3,434.515..3,434.523 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: 20,778kB
  • Buffers: shared hit=3,605,794
4. 146.859 3,186.850 ↓ 125,904.0 125,904 1

Nested Loop (cost=4.74..71.16 rows=1 width=586) (actual time=0.218..3,186.85 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=3,605,794
5. 132.701 3,039.991 ↓ 125,904.0 125,904 1

Nested Loop (cost=4.58..70.67 rows=1 width=198) (actual time=0.204..3,039.991 rows=125,904 loops=1)

  • Buffers: shared hit=3,479,890
6. 137.139 2,907.290 ↓ 125,904.0 125,904 1

Nested Loop (cost=4.44..62.49 rows=1 width=198) (actual time=0.192..2,907.29 rows=125,904 loops=1)

  • Buffers: shared hit=3,353,986
7. 143.709 2,770.151 ↓ 125,904.0 125,904 1

Nested Loop (cost=4.3..62.32 rows=1 width=198) (actual time=0.184..2,770.151 rows=125,904 loops=1)

  • Buffers: shared hit=3,228,082
8. 120.973 2,626.442 ↓ 125,904.0 125,904 1

Nested Loop (cost=4.16..62.1 rows=1 width=198) (actual time=0.173..2,626.442 rows=125,904 loops=1)

  • Buffers: shared hit=3,102,178
9. 117.437 2,505.469 ↓ 125,904.0 125,904 1

Nested Loop (cost=3.87..55.98 rows=1 width=144) (actual time=0.162..2,505.469 rows=125,904 loops=1)

  • Buffers: shared hit=3,055,375
10. 103.436 2,388.032 ↓ 125,904.0 125,904 1

Nested Loop (cost=3.58..55.57 rows=1 width=148) (actual time=0.15..2,388.032 rows=125,904 loops=1)

  • Buffers: shared hit=3,008,572
11. 77.096 2,158.692 ↓ 125,904.0 125,904 1

Nested Loop (cost=3.29..55.21 rows=1 width=90) (actual time=0.142..2,158.692 rows=125,904 loops=1)

  • Filter: ((cl.linkid IS NULL) OR cl.hasminimumdata)
  • Buffers: shared hit=2,677,663
12. 143.887 1,955.666 ↓ 125,930.0 125,930 1

Nested Loop (cost=2.85..46.76 rows=1 width=102) (actual time=0.128..1,955.666 rows=125,930 loops=1)

  • Buffers: shared hit=2,521,095
13. 138.449 1,685.849 ↓ 125,930.0 125,930 1

Nested Loop (cost=2.56..46.44 rows=1 width=74) (actual time=0.115..1,685.849 rows=125,930 loops=1)

  • Buffers: shared hit=2,143,305
14. 133.167 1,421.470 ↓ 125,930.0 125,930 1

Nested Loop (cost=2.28..46.13 rows=1 width=82) (actual time=0.1..1,421.47 rows=125,930 loops=1)

  • Buffers: shared hit=1,765,515
15. 153.104 1,162.373 ↓ 125,930.0 125,930 1

Nested Loop (cost=1.99..45.81 rows=1 width=74) (actual time=0.088..1,162.373 rows=125,930 loops=1)

  • Buffers: shared hit=1,387,725
16. 121.173 757.339 ↓ 125,965.0 125,965 1

Nested Loop (cost=1.56..45.12 rows=1 width=66) (actual time=0.071..757.339 rows=125,965 loops=1)

  • Buffers: shared hit=883,179
17. 126.017 384.236 ↓ 125,965.0 125,965 1

Nested Loop (cost=1.12..36.72 rows=1 width=53) (actual time=0.057..384.236 rows=125,965 loops=1)

  • Buffers: shared hit=378,623
18. 17.258 132.254 ↓ 125,965.0 125,965 1

Nested Loop (cost=0.84..28.42 rows=1 width=53) (actual time=0.045..132.254 rows=125,965 loops=1)

  • Buffers: shared hit=728
19. 0.036 0.036 ↓ 4.0 8 1

Index Scan using idx_asset_customerid_assetid__active on asset a (cost=0.28..11.21 rows=2 width=25) (actual time=0.021..0.036 rows=8 loops=1)

  • Index Cond: (a.customerid = 594)
  • Buffers: shared hit=4
20. 114.960 114.960 ↓ 15,746.0 15,746 8

Index Scan using incident__index1 on incident i (cost=0.56..8.59 rows=1 width=28) (actual time=0.019..14.37 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
21. 125.965 125.965 ↑ 1.0 1 125,965

Index Only Scan using idx_contract_contractid__active on contract co (cost=0.28..8.29 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=125,965)

  • Index Cond: (co.contractid = 614)
  • Heap Fetches: 125,965
  • Buffers: shared hit=377,895
22. 251.930 251.930 ↑ 1.0 1 125,965

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

  • Index Cond: ((dl.detectionlinkid = i.detectionlinkid) AND (dl.linktypeid = 1))
  • Buffers: shared hit=504,556
23. 251.930 251.930 ↑ 1.0 1 125,965

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

  • Index Cond: ((l.linkid = dl.detectionlinkid) AND (l.linktypeid = 1))
  • Filter: (l.hasminimumdata AND (NOT l.deleted) AND (l.customerid = 594) AND (l.enforcementstatusid = 6))
  • Buffers: shared hit=504,546
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: 125,930
  • Buffers: shared hit=377,790
25. 125.930 125.930 ↑ 1.0 1 125,930

Index Only Scan using idx_contract_asset__contractid_assetid on contract_asset coa (cost=0.28..0.3 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=125,930)

  • Index Cond: ((coa.contractid = 614) AND (coa.assetid = l.assetid))
  • Heap Fetches: 125,930
  • Buffers: shared hit=377,790
26. 125.930 125.930 ↑ 1.0 1 125,930

Index Scan using idx_domain_domainid_hierarchylevelid__active 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)
  • Buffers: shared hit=377,790
27. 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=13) (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=156,568
28. 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=330,909
29. 0.000 0.000 ↓ 0.0 0 125,904

Index Scan using idx_domain_domainid_hierarchylevelid__active 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) AND (md.hierarchylevelid = 1))
  • Buffers: shared hit=46,803
30. 0.000 0.000 ↓ 0.0 0 125,904

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

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

Index Scan using idx_customerdivision_customer on customerdivision c (cost=0.14..0.21 rows=1 width=8) (actual time=0..0 rows=0 loops=125,904)

  • Index Cond: ((c.customerid = i.customerid) AND (c.customerid = 594))
  • Buffers: shared hit=125,904
32. 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=125,904
33. 0.000 0.000 ↓ 0.0 0 125,904

Index Only Scan using uq_contract_customerdivision__contract_customerdivision on contract_customerdivision ccd (cost=0.14..8.16 rows=1 width=8) (actual time=0..0 rows=0 loops=125,904)

  • Index Cond: (ccd.contractid = 614)
  • Buffers: shared hit=125,904
34. 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=125,904
Planning time : 97.723 ms
Execution time : 3,435.83 ms