explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1mqO

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 1,593.109 ↑ 39,840.0 2 1

Gather Merge (cost=102,157.09..111,453.73 rows=79,680 width=2,692) (actual time=1,536.860..1,593.109 rows=2 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 0.237 4,322.844 ↑ 39,840.0 1 3

Sort (cost=101,157.06..101,256.66 rows=39,840 width=2,692) (actual time=1,440.948..1,440.948 rows=1 loops=3)

  • Sort Key: x.detect_time DESC
  • Sort Method: quicksort Memory: 29kB
3. 32.301 4,322.607 ↑ 39,840.0 1 3

Hash Join (cost=49,670.68..51,811.40 rows=39,840 width=2,692) (actual time=1,231.514..1,440.869 rows=1 loops=3)

  • Hash Cond: (x.id = x0.threat_id)
4. 158.237 3,990.306 ↑ 1.2 31,916 3

Hash Left Join (cost=34,470.66..36,063.59 rows=39,840 width=2,692) (actual time=1,011.732..1,330.102 rows=31,916 loops=3)

  • Hash Cond: (x.level_id = "x.Level".id)
5. 150.896 3,832.011 ↑ 1.2 31,916 3

Hash Left Join (cost=34,442.44..35,930.24 rows=39,840 width=2,620) (actual time=1,011.698..1,277.337 rows=31,916 loops=3)

  • Hash Cond: ("x.Job".level_id = "x.Job.Level".id)
6. 176.544 3,680.727 ↑ 1.2 31,916 3

Hash Join (cost=34,414.21..35,796.94 rows=39,840 width=2,548) (actual time=1,011.553..1,226.909 rows=31,916 loops=3)

  • Hash Cond: (x.job_id = "x.Job".id)
7. 90.202 3,502.074 ↑ 1.2 31,916 3

Hash Left Join (cost=34,341.25..35,609.08 rows=39,840 width=1,357) (actual time=1,010.812..1,167.358 rows=31,916 loops=3)

  • Hash Cond: (x.workflow_id = "x.Workflow".id)
8. 206.400 3,411.486 ↑ 1.2 31,916 3

Merge Join (cost=34,304.95..35,423.38 rows=39,840 width=781) (actual time=1,010.648..1,137.162 rows=31,916 loops=3)

  • Merge Cond: ("x.EventUser".id = x.event_user_id)
9. 1,301.676 1,301.676 ↑ 1.7 177,597 3

Parallel Index Scan using event_user_pkey on event_user "x.EventUser" (cost=0.42..299,893.30 rows=309,400 width=351) (actual time=0.048..433.892 rows=177,597 loops=3)

10. 216.786 1,903.410 ↓ 1.0 95,747 3

Materialize (cost=34,304.53..34,782.60 rows=95,615 width=430) (actual time=459.506..634.470 rows=95,747 loops=3)

11. 1,282.392 1,686.624 ↓ 1.0 95,747 3

Sort (cost=34,304.53..34,543.56 rows=95,615 width=430) (actual time=459.478..562.208 rows=95,747 loops=3)

  • Sort Key: x.event_user_id
  • Sort Method: external sort Disk: 42120kB
12. 404.232 404.232 ↓ 1.0 95,748 3

Seq Scan on threat x (cost=0.00..7,764.30 rows=95,615 width=430) (actual time=0.317..134.744 rows=95,748 loops=3)

  • Filter: ((NOT ignore) AND ((level_id <> 5) OR (level_id IS NULL)) AND (detect_time >= '2019-06-12 07:00:00'::timestamp without time zone) AND (detect_time <= '2019-07-12 06:59:59'::timestamp without time zone) AND (threat_type_id = 2))
  • Rows Removed by Filter: 1
13. 0.002 0.386 ↓ 0.0 0 2

Hash (cost=28.30..28.30 rows=640 width=576) (actual time=0.193..0.193 rows=0 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
14. 0.004 0.384 ↓ 0.0 0 2

Hash Left Join (cost=9.47..28.30 rows=640 width=576) (actual time=0.192..0.192 rows=0 loops=2)

  • Hash Cond: ("x.Workflow".assignee_user_id = "x.Workflow.AssigneeUser".id)
15. 0.380 0.380 ↓ 0.0 0 2

Seq Scan on workflow "x.Workflow" (cost=0.00..16.40 rows=640 width=99) (actual time=0.190..0.190 rows=0 loops=2)

16. 0.000 0.000 ↓ 0.0 0

Hash (cost=9.45..9.45 rows=1 width=477) (never executed)

17. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.42..9.45 rows=1 width=477) (never executed)

18. 0.000 0.000 ↓ 0.0 0

Seq Scan on user_account "x.Workflow.AssigneeUser" (cost=0.00..1.01 rows=1 width=126) (never executed)

19. 0.000 0.000 ↓ 0.0 0

Index Scan using event_user_pkey on event_user "x.Workflow.AssigneeUser.EventUser" (cost=0.42..8.44 rows=1 width=351) (never executed)

  • Index Cond: ("x.Workflow.AssigneeUser".event_user_id = id)
20. 0.327 2.109 ↑ 1.6 27 3

Hash (cost=72.43..72.43 rows=43 width=1,191) (actual time=0.702..0.703 rows=27 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
21. 1.782 1.782 ↑ 1.6 27 3

Seq Scan on job "x.Job" (cost=0.00..72.43 rows=43 width=1,191) (actual time=0.350..0.594 rows=27 loops=3)

22. 0.048 0.388 ↑ 162.0 5 2

Hash (cost=18.10..18.10 rows=810 width=72) (actual time=0.194..0.194 rows=5 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.340 0.340 ↑ 162.0 5 2

Seq Scan on level "x.Job.Level" (cost=0.00..18.10 rows=810 width=72) (actual time=0.167..0.170 rows=5 loops=2)

24. 0.024 0.058 ↑ 162.0 5 2

Hash (cost=18.10..18.10 rows=810 width=72) (actual time=0.029..0.029 rows=5 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 0.034 0.034 ↑ 162.0 5 2

Seq Scan on level "x.Level" (cost=0.00..18.10 rows=810 width=72) (actual time=0.015..0.017 rows=5 loops=2)

26. 0.081 300.000 ↑ 49,333.0 2 3

Hash (cost=13,966.69..13,966.69 rows=98,666 width=8) (actual time=100.000..100.000 rows=2 loops=3)

  • Buckets: 131072 Batches: 1 Memory Usage: 1025kB
27. 3.282 299.919 ↑ 49,333.0 2 3

HashAggregate (cost=12,980.03..13,966.69 rows=98,666 width=8) (actual time=99.631..99.973 rows=2 loops=3)

  • Group Key: x0.threat_id
28. 145.539 296.637 ↑ 49,382.5 2 3

Hash Join (cost=8,037.17..12,733.11 rows=98,765 width=8) (actual time=98.456..98.879 rows=2 loops=3)

  • Hash Cond: (x0.file_id = "x.File".id)
29. 68.589 68.589 ↑ 1.0 96,168 3

Seq Scan on threat_event x0 (cost=0.00..4,324.65 rows=98,765 width=24) (actual time=0.449..22.863 rows=96,168 loops=3)

30. 1.869 82.509 ↑ 1.0 969 3

Hash (cost=8,025.04..8,025.04 rows=970 width=32) (actual time=27.503..27.503 rows=969 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 69kB
31. 3.780 80.640 ↑ 1.0 969 3

Nested Loop (cost=306.06..8,025.04 rows=970 width=32) (actual time=14.984..26.880 rows=969 loops=3)

32. 16.182 44.883 ↑ 1.0 969 3

HashAggregate (cost=305.63..315.33 rows=970 width=16) (actual time=14.450..14.961 rows=969 loops=3)

  • Group Key: t.file_id
33. 16.959 28.701 ↓ 1.0 12,132 3

Hash Join (cost=13.21..275.92 rows=11,886 width=16) (actual time=1.047..9.567 rows=12,132 loops=3)

  • Hash Cond: (t.tag_id = "t.Tag".id)
34. 9.864 9.864 ↑ 1.0 12,132 3

Seq Scan on file_tag t (cost=0.00..230.42 rows=12,142 width=24) (actual time=0.393..3.288 rows=12,132 loops=3)

35. 0.318 1.878 ↑ 1.0 321 3

Hash (cost=9.15..9.15 rows=325 width=8) (actual time=0.626..0.626 rows=321 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
36. 1.560 1.560 ↑ 1.0 321 3

Seq Scan on tag "t.Tag" (cost=0.00..9.15 rows=325 width=8) (actual time=0.347..0.520 rows=321 loops=3)

  • Filter: (name ~~ 'SDD:%'::text)
  • Rows Removed by Filter: 11
37. 31.977 31.977 ↑ 1.0 1 2,907

Index Only Scan using files_pkey on file "x.File" (cost=0.42..7.96 rows=1 width=16) (actual time=0.011..0.011 rows=1 loops=2,907)

  • Index Cond: (id = t.file_id)
  • Heap Fetches: 969
Planning time : 11.121 ms