explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5qLN

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 34.395 ↓ 20.0 20 1

Limit (cost=139.23..139.27 rows=1 width=586) (actual time=34.379..34.395 rows=20 loops=1)

  • Buffers: shared hit=31518
2. 0.021 34.387 ↓ 21.0 21 1

GroupAggregate (cost=139.19..139.23 rows=1 width=586) (actual time=34.377..34.387 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=31518
3. 1.566 34.366 ↓ 23.0 23 1

Sort (cost=139.19..139.2 rows=1 width=586) (actual time=34.365..34.366 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=31518
4. 1.509 32.800 ↓ 1,168.0 1,168 1

Nested Loop (cost=4.86..139.18 rows=1 width=586) (actual time=0.218..32.8 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=31518
5. 1.060 31.291 ↓ 1,168.0 1,168 1

Nested Loop (cost=4.71..138.7 rows=1 width=198) (actual time=0.207..31.291 rows=1,168 loops=1)

  • Buffers: shared hit=30350
6. 1.428 30.231 ↓ 1,168.0 1,168 1

Nested Loop (cost=4.56..137.64 rows=1 width=198) (actual time=0.199..30.231 rows=1,168 loops=1)

  • Buffers: shared hit=30350
7. 1.474 28.803 ↓ 1,168.0 1,168 1

Nested Loop (cost=4.42..137.47 rows=1 width=198) (actual time=0.19..28.803 rows=1,168 loops=1)

  • Buffers: shared hit=29182
8. 1.444 27.329 ↓ 1,168.0 1,168 1

Nested Loop (cost=4.28..129.3 rows=1 width=198) (actual time=0.178..27.329 rows=1,168 loops=1)

  • Buffers: shared hit=28014
9. 1.456 25.885 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.99..123.15 rows=1 width=144) (actual time=0.17..25.885 rows=1,168 loops=1)

  • Buffers: shared hit=27150
10. 0.891 24.429 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.7..122.74 rows=1 width=148) (actual time=0.159..24.429 rows=1,168 loops=1)

  • Buffers: shared hit=26286
11. 0.000 22.370 ↓ 1,168.0 1,168 1

Nested Loop (cost=3.41..122.37 rows=1 width=90) (actual time=0.148..22.37 rows=1,168 loops=1)

  • Filter: ((cl.linkid IS NULL) OR cl.hasminimumdata)
  • Buffers: shared hit=23646
12. 0.615 20.088 ↓ 1,170.0 1,170 1

Nested Loop (cost=2.98..115.25 rows=1 width=98) (actual time=0.137..20.088 rows=1,170 loops=1)

  • Buffers: shared hit=22132
13. 1.401 15.963 ↓ 1,170.0 1,170 1

Nested Loop (cost=2.55..112.67 rows=1 width=93) (actual time=0.122..15.963 rows=1,170 loops=1)

  • Buffers: shared hit=17436
14. 0.293 13.392 ↓ 1,170.0 1,170 1

Nested Loop (cost=2.26..112.35 rows=1 width=65) (actual time=0.109..13.392 rows=1,170 loops=1)

  • Buffers: shared hit=13926
15. 1.397 7.599 ↓ 1,375.0 1,375 1

Nested Loop (cost=1.83..105.23 rows=1 width=61) (actual time=0.091..7.599 rows=1,375 loops=1)

  • Buffers: shared hit=8390
16. 1.580 4.827 ↓ 1,375.0 1,375 1

Nested Loop (cost=1.56..96.92 rows=1 width=61) (actual time=0.077..4.827 rows=1,375 loops=1)

  • Buffers: shared hit=4265
17. 0.250 1.872 ↓ 1,375.0 1,375 1

Nested Loop (cost=1.28..88.62 rows=1 width=53) (actual time=0.063..1.872 rows=1,375 loops=1)

  • Buffers: shared hit=140
18. 0.016 0.110 ↓ 8.0 8 1

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

  • Buffers: shared hit=52
19. 0.014 0.070 ↓ 4.0 8 1

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

  • Buffers: shared hit=28
20. 0.024 0.024 ↓ 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.016..0.024 rows=8 loops=1)

  • Index Cond: (a.customerid = 594)
  • Filter: a.active
  • Buffers: shared hit=4
21. 0.032 0.032 ↑ 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.004..0.004 rows=1 loops=8)

  • Index Cond: ((ascope.assetid = a.assetid) AND (ascope.scopeid = 1))
  • Heap Fetches: 8
  • Buffers: shared hit=24
22. 0.024 0.024 ↑ 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.003..0.003 rows=1 loops=8)

  • Index Cond: (coa.assetid = ascope.assetid)
  • Filter: (coa.contractid = 614)
  • Buffers: shared hit=24
23. 0.136 1.512 ↑ 2.6 172 8

Append (cost=0.43..53.23 rows=450 width=20) (actual time=0.014..0.189 rows=172 loops=8)

  • Buffers: shared hit=88
24. 1.376 1.376 ↑ 2.6 172 8

Index Scan using incident_partitioned_594_customerid_contractid_assetid_inci_idx on incident_partitioned_594 i (cost=0.43..50.98 rows=450 width=20) (actual time=0.013..0.172 rows=172 loops=8)

  • Index Cond: ((i.customerid = 594) AND (i.contractid = 614) AND (i.assetid = ascope.assetid) AND (i.incidentstatusid = 2) AND (i.enforcementlevelid = 8))
  • Buffers: shared hit=88
25. 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
26. 1.375 1.375 ↑ 1.0 1 1,375

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

  • Index Cond: (scope.scopeid = 1)
  • Heap Fetches: 1375
  • Buffers: shared hit=4125
27. 1.375 5.500 ↑ 1.0 1 1,375

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

  • Buffers: shared hit=5536
28. 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..7.11 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
29. 1.170 1.170 ↑ 1.0 1 1,170

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

  • Index Cond: (d.domainid = l.domainid)
  • Filter: d.active
  • Buffers: shared hit=3510
30. 3.510 3.510 ↑ 1.0 1 1,170

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

  • Index Cond: ((dl.detectionlinkid = l.linkid) AND (dl.linktypeid = 1))
  • Buffers: shared hit=4696
31. 1.170 2.340 ↓ 0.0 0 1,170

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

  • Buffers: shared hit=1514
32. 1.170 1.170 ↓ 0.0 0 1,170

Index Scan using link_partitioned_594_customerid_assetid_linkid_linktypeid_idx on link_partitioned_594 cl (cost=0.43..7.11 rows=1 width=9) (actual time=0.001..0.001 rows=0 loops=1,170)

  • 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.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
34. 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
35. 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
36. 0.000 0.000 ↓ 0.0 0 1,168

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=1,168)

  • Index Cond: (c.customerid = co.customerid)
  • Buffers: shared hit=1168
37. 0.000 0.000 ↓ 0.0 0 1,168

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=1,168)

  • Index Cond: (d.domainid = dc.domainid)
  • Buffers: shared hit=1168
38. 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)
39. 0.000 0.000 ↓ 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..0 rows=0 loops=1,168)

  • Index Cond: ((cc.scopeid = ascope.scopeid) AND (cc.scopeid = 1))
  • Buffers: shared hit=1168
Planning time : 62.995 ms
Execution time : 34.643 ms