explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Q3dD

Settings
# exclusive inclusive rows x rows loops node
1. 1.496 22,896.286 ↑ 546.4 865 1

Sort (cost=7,168,283.56..7,169,465.05 rows=472,598 width=36) (actual time=22,896.246..22,896.286 rows=865 loops=1)

  • Sort Key: (count(DISTINCT l.linkid)) DESC, d.url
  • Sort Method: quicksort Memory: 94kB
  • Buffers: shared hit=13368947, temp read=48554 written=48554
2. 19.876 22,894.790 ↑ 546.4 865 1

GroupAggregate (cost=7,114,288.63..7,123,740.59 rows=472,598 width=36) (actual time=22,870.024..22,894.790 rows=865 loops=1)

  • Group Key: d.domainid, dcfg.domainplatformid
  • Buffers: shared hit=13368947, temp read=48554 written=48554
3. 21.496 22,874.914 ↑ 4.7 99,551 1

Sort (cost=7,114,288.63..7,115,470.13 rows=472,598 width=32) (actual time=22,870.009..22,874.914 rows=99,551 loops=1)

  • Sort Key: d.domainid, dcfg.domainplatformid
  • Sort Method: quicksort Memory: 10851kB
  • Buffers: shared hit=13368947, temp read=48554 written=48554
4. 22.433 22,853.418 ↑ 4.7 99,551 1

Hash Left Join (cost=6,103,706.26..7,069,745.67 rows=472,598 width=32) (actual time=14,986.077..22,853.418 rows=99,551 loops=1)

  • Hash Cond: (COALESCE(d.domainconfigurationid, md.domainconfigurationid) = dcfg.domainconfigurationid)
  • Buffers: shared hit=13368947, temp read=48554 written=48554
5. 27.077 22,820.394 ↑ 4.7 99,551 1

Hash Left Join (cost=6,102,364.83..7,067,163.53 rows=472,598 width=36) (actual time=14,975.429..22,820.394 rows=99,551 loops=1)

  • Hash Cond: (d.masterdomainid = md.domainid)
  • Join Filter: (d.hierarchylevelid = 2)
  • Filter: (((b.botid IS NULL) AND (mb.botid IS NULL)) OR (NOT COALESCE(b.active, mb.active)) OR (NOT COALESCE(b.waitforcompletion, mb.waitforcompletion)) OR (COALESCE(b.waitforcompletion, mb.waitforcompletion) AND ((l.createdat <= (now() - '01:00:00'::interval)) OR ((monreq.monitorrequestid IS NOT NULL) AND (monreq.lastmonitorresponsedate IS NULL) AND (l.createdat <= (now() - '01:30:00'::interval))))))
  • Rows Removed by Filter: 584
  • Buffers: shared hit=13368445, temp read=48554 written=48554
6. 15.075 22,792.701 ↑ 5.4 100,135 1

Hash Left Join (cost=6,101,152.94..7,063,918.24 rows=540,120 width=66) (actual time=14,974.805..22,792.701 rows=100,135 loops=1)

  • Hash Cond: (cfg.monitorbotid = b.botid)
  • Buffers: shared hit=13367602, temp read=48554 written=48554
7. 3,846.618 22,777.553 ↑ 5.4 100,135 1

Hash Right Join (cost=6,101,138.89..7,062,481.06 rows=540,120 width=64) (actual time=14,974.723..22,777.553 rows=100,135 loops=1)

  • Hash Cond: (monreq.linkid = l.linkid)
  • Buffers: shared hit=13367596, temp read=48554 written=48554
8. 4,106.998 4,106.998 ↑ 1.0 22,134,630 1

Seq Scan on monitorrequest monreq (cost=0.00..651,930.18 rows=22,152,118 width=16) (actual time=0.008..4,106.998 rows=22,134,630 loops=1)

  • Buffers: shared hit=430409
9. 22.864 14,823.937 ↑ 5.4 100,135 1

Hash (cost=6,089,112.39..6,089,112.39 rows=540,120 width=52) (actual time=14,823.937..14,823.937 rows=100,135 loops=1)

  • Buckets: 524288 Batches: 2 Memory Usage: 7664kB
  • Buffers: shared hit=12937187, temp written=372
10. 20.110 14,801.073 ↑ 5.4 100,135 1

Hash Join (cost=3,723.97..6,089,112.39 rows=540,120 width=52) (actual time=235.448..14,801.073 rows=100,135 loops=1)

  • Hash Cond: (l.assetid = ascope.assetid)
  • Buffers: shared hit=12937187
11. 15.930 14,779.612 ↑ 5.3 100,135 1

Hash Left Join (cost=3,393.47..6,087,391.56 rows=529,141 width=60) (actual time=234.084..14,779.612 rows=100,135 loops=1)

  • Hash Cond: (d.monitordomainconfigurationid = cfg.monitordomainconfigurationid)
  • Buffers: shared hit=12937056
12. 24.160 14,753.796 ↑ 5.3 100,135 1

Hash Join (cost=1,941.71..6,084,550.67 rows=529,141 width=60) (actual time=224.139..14,753.796 rows=100,135 loops=1)

  • Hash Cond: (COALESCE(ic.contractid, id.contractid) = co.contractid)
  • Buffers: shared hit=12936447
13. 0.000 14,729.420 ↑ 6.8 112,859 1

Nested Loop Left Join (cost=1,911.33..6,082,485.60 rows=771,410 width=68) (actual time=29.582..14,729.420 rows=112,859 loops=1)

  • Filter: ((id.incidentid IS NOT NULL) OR (ic.incidentid IS NOT NULL))
  • Rows Removed by Filter: 1063539
  • Buffers: shared hit=12936433
14. 263.068 12,670.825 ↓ 1.4 1,082,814 1

Hash Join (cost=1,910.76..2,880,177.45 rows=771,410 width=72) (actual time=28.003..12,670.825 rows=1,082,814 loops=1)

  • Hash Cond: (l.domainid = d.domainid)
  • Buffers: shared hit=8328392
15. 221.971 12,389.325 ↓ 1.4 1,082,815 1

Hash Join (cost=190.65..2,876,431.73 rows=771,581 width=36) (actual time=9.513..12,389.325 rows=1,082,815 loops=1)

  • Hash Cond: (l.assetid = a.assetid)
  • Buffers: shared hit=8327583
16. 459.218 12,166.196 ↓ 1.1 1,131,418 1

Merge Left Join (cost=1.00..2,873,565.56 rows=1,018,668 width=32) (actual time=8.347..12,166.196 rows=1,131,418 loops=1)

  • Merge Cond: (l.linkid = ic.contentlinkid)
  • Join Filter: (ic.customerid = l.customerid)
  • Rows Removed by Join Filter: 22
  • Buffers: shared hit=8327481
17. 6,856.820 6,856.820 ↓ 1.1 1,130,527 1

Index Scan using pk_link on link l (cost=0.44..1,931,034.87 rows=1,018,668 width=24) (actual time=8.165..6,856.820 rows=1,130,527 loops=1)

  • Filter: ((NOT deleted) AND (NOT hasminimumdata) AND (enforcementstatusid <> 8) AND (((datacompleterequestuser IS NULL) AND (datacompleterequestdate IS NULL)) OR (((datacompleterequestuser)::text = 'system'::text) AND (now() >= datacompleterequestdate))))
  • Rows Removed by Filter: 23393807
  • Buffers: shared hit=3310736
18. 4,850.158 4,850.158 ↑ 11.1 4,414,911 1

Index Scan using idx_incident_contentlink on incident ic (cost=0.56..5,981,045.50 rows=49,052,430 width=16) (actual time=0.010..4,850.158 rows=4,414,911 loops=1)

  • Filter: ((NOT deleted) AND (incidentstatusid = ANY ('{1,2,3,5,6,8,9,10,11,12,13,14,15,18,19}'::integer[])))
  • Rows Removed by Filter: 4784678
  • Buffers: shared hit=5016745
19. 0.367 1.158 ↓ 1.0 3,423 1

Hash (cost=147.02..147.02 rows=3,410 width=4) (actual time=1.158..1.158 rows=3,423 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 153kB
  • Buffers: shared hit=102
20. 0.791 0.791 ↓ 1.0 3,423 1

Seq Scan on asset a (cost=0.00..147.02 rows=3,410 width=4) (actual time=0.007..0.791 rows=3,423 loops=1)

  • Filter: active
  • Rows Removed by Filter: 1092
  • Buffers: shared hit=102
21. 7.507 18.432 ↑ 1.0 40,168 1

Hash (cost=1,213.99..1,213.99 rows=40,490 width=40) (actual time=18.432..18.432 rows=40,168 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3223kB
  • Buffers: shared hit=809
22. 10.925 10.925 ↑ 1.0 40,168 1

Seq Scan on domain d (cost=0.00..1,213.99 rows=40,490 width=40) (actual time=0.005..10.925 rows=40,168 loops=1)

  • Filter: active
  • Rows Removed by Filter: 10
  • Buffers: shared hit=809
23. 2,165.628 2,165.628 ↓ 0.0 0 1,082,814

Index Scan using tmp_idx_incident_cus_detlink_status_del on incident id (cost=0.56..4.14 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1,082,814)

  • Index Cond: ((customerid = l.customerid) AND (detectionlinkid = l.linkid))
  • Filter: (incidentstatusid = ANY ('{1,2,3,5,6,8,9,10,11,12,13,14,15,18,19}'::integer[]))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4608041
24. 0.065 0.216 ↑ 1.0 602 1

Hash (cost=22.82..22.82 rows=605 width=4) (actual time=0.216..0.216 rows=602 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
  • Buffers: shared hit=14
25. 0.151 0.151 ↑ 1.0 602 1

Seq Scan on contract co (cost=0.00..22.82 rows=605 width=4) (actual time=0.008..0.151 rows=602 loops=1)

  • Filter: active
  • Rows Removed by Filter: 280
  • Buffers: shared hit=14
26. 4.879 9.886 ↑ 1.0 37,290 1

Hash (cost=983.56..983.56 rows=37,456 width=8) (actual time=9.886..9.886 rows=37,290 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 1880kB
  • Buffers: shared hit=609
27. 5.007 5.007 ↑ 1.0 37,290 1

Seq Scan on monitordomainconfiguration cfg (cost=0.00..983.56 rows=37,456 width=8) (actual time=0.006..5.007 rows=37,290 loops=1)

  • Buffers: shared hit=609
28. 0.489 1.351 ↓ 1.0 4,515 1

Hash (cost=274.31..274.31 rows=4,495 width=4) (actual time=1.351..1.351 rows=4,515 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 223kB
  • Buffers: shared hit=131
29. 0.712 0.862 ↓ 1.0 4,515 1

Bitmap Heap Scan on asset_scope ascope (cost=103.12..274.31 rows=4,495 width=4) (actual time=0.165..0.862 rows=4,515 loops=1)

  • Recheck Cond: (scopeid = 1)
  • Heap Blocks: exact=115
  • Buffers: shared hit=131
30. 0.150 0.150 ↓ 1.0 4,515 1

Bitmap Index Scan on idx_assetscope_scope (cost=0.00..102.00 rows=4,495 width=0) (actual time=0.150..0.150 rows=4,515 loops=1)

  • Index Cond: (scopeid = 1)
  • Buffers: shared hit=16
31. 0.023 0.073 ↑ 1.2 173 1

Hash (cost=11.45..11.45 rows=208 width=6) (actual time=0.073..0.073 rows=173 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
  • Buffers: shared hit=6
32. 0.050 0.050 ↑ 1.2 173 1

Seq Scan on bot b (cost=0.00..11.45 rows=208 width=6) (actual time=0.008..0.050 rows=173 loops=1)

  • Filter: (bottypeid = 2)
  • Rows Removed by Filter: 190
  • Buffers: shared hit=6
33. 0.027 0.616 ↓ 1.1 157 1

Hash (cost=1,210.14..1,210.14 rows=140 width=14) (actual time=0.616..0.616 rows=157 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
  • Buffers: shared hit=843
34. 0.000 0.589 ↓ 1.1 157 1

Nested Loop Left Join (cost=0.73..1,210.14 rows=140 width=14) (actual time=0.028..0.589 rows=157 loops=1)

  • Buffers: shared hit=843
35. 0.000 0.433 ↓ 1.1 157 1

Nested Loop Left Join (cost=0.58..1,186.57 rows=140 width=12) (actual time=0.023..0.433 rows=157 loops=1)

  • Buffers: shared hit=579
36. 0.134 0.134 ↓ 1.1 157 1

Index Scan using idx_domain_hierarchylevel on domain md (cost=0.29..303.52 rows=140 width=12) (actual time=0.015..0.134 rows=157 loops=1)

  • Index Cond: (hierarchylevelid = 1)
  • Filter: active
  • Buffers: shared hit=107
37. 0.314 0.314 ↑ 1.0 1 157

Index Scan using pk_monitordomainconfiguration on monitordomainconfiguration mcfg (cost=0.29..6.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=157)

  • Index Cond: (monitordomainconfigurationid = md.monitordomainconfigurationid)
  • Buffers: shared hit=472
38. 0.157 0.157 ↑ 1.0 1 157

Index Scan using idx_bot_bottypeid_botid on bot mb (cost=0.15..0.17 rows=1 width=6) (actual time=0.001..0.001 rows=1 loops=157)

  • Index Cond: ((bottypeid = 2) AND (botid = mcfg.monitorbotid))
  • Buffers: shared hit=264
39. 4.822 10.591 ↑ 1.0 37,288 1

Hash (cost=875.08..875.08 rows=37,308 width=8) (actual time=10.591..10.591 rows=37,288 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 1969kB
  • Buffers: shared hit=502
40. 5.769 5.769 ↑ 1.0 37,288 1

Seq Scan on domainconfiguration dcfg (cost=0.00..875.08 rows=37,308 width=8) (actual time=0.005..5.769 rows=37,288 loops=1)

  • Buffers: shared hit=502
Planning time : 9.601 ms
Execution time : 22,896.511 ms