explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xm9e

Settings
# exclusive inclusive rows x rows loops node
1. 0.024 0.326 ↑ 142.9 7 1

WindowAgg (cost=103.07..125.57 rows=1,000 width=92) (actual time=0.309..0.326 rows=7 loops=1)

2.          

CTE metrics

3. 0.024 0.243 ↑ 1.7 3 1

HashAggregate (cost=15.23..15.31 rows=5 width=72) (actual time=0.242..0.243 rows=3 loops=1)

4. 0.012 0.219 ↑ 1.7 3 1

HashAggregate (cost=14.86..14.91 rows=5 width=72) (actual time=0.218..0.219 rows=3 loops=1)

5. 0.012 0.207 ↓ 1.8 9 1

HashAggregate (cost=14.54..14.59 rows=5 width=16) (actual time=0.204..0.207 rows=9 loops=1)

6. 0.000 0.195 ↓ 1.8 9 1

Append (cost=6.06..14.43 rows=5 width=16) (actual time=0.167..0.195 rows=9 loops=1)

7. 0.002 0.171 ↓ 2.2 9 1

Result (cost=6.06..6.10 rows=4 width=16) (actual time=0.167..0.171 rows=9 loops=1)

8. 0.009 0.169 ↓ 2.2 9 1

HashAggregate (cost=6.06..6.10 rows=4 width=16) (actual time=0.167..0.169 rows=9 loops=1)

9. 0.003 0.160 ↓ 2.2 9 1

Append (cost=3.65..5.97 rows=4 width=16) (actual time=0.110..0.160 rows=9 loops=1)

10. 0.001 0.112 ↓ 2.3 7 1

Result (cost=3.65..3.68 rows=3 width=16) (actual time=0.110..0.112 rows=7 loops=1)

11. 0.010 0.111 ↓ 2.3 7 1

HashAggregate (cost=3.65..3.68 rows=3 width=16) (actual time=0.109..0.111 rows=7 loops=1)

12. 0.000 0.101 ↓ 2.3 7 1

Append (cost=2.34..3.59 rows=3 width=16) (actual time=0.078..0.101 rows=7 loops=1)

13. 0.005 0.082 ↓ 2.5 5 1

Result (cost=2.34..2.39 rows=2 width=16) (actual time=0.078..0.082 rows=5 loops=1)

14. 0.011 0.077 ↓ 2.5 5 1

Unique (cost=2.34..2.39 rows=2 width=16) (actual time=0.076..0.077 rows=5 loops=1)

15. 0.015 0.066 ↓ 2.5 5 1

Sort (cost=2.34..2.35 rows=2 width=16) (actual time=0.066..0.066 rows=5 loops=1)

  • Sort Key: "*SELECT* 1".date, ((0)::double precision), ((0)::double precision), ((0)::double precision), (0), (0), (0), (0), "*SELECT* 1".time_spent
  • Sort Method: quicksort Memory: 25kB
16. 0.000 0.051 ↓ 2.5 5 1

Append (cost=1.12..2.33 rows=2 width=16) (actual time=0.024..0.051 rows=5 loops=1)

17. 0.002 0.025 ↓ 3.0 3 1

Subquery Scan on *SELECT* 1 (cost=1.12..1.14 rows=1 width=16) (actual time=0.024..0.025 rows=3 loops=1)

18. 0.012 0.023 ↓ 3.0 3 1

HashAggregate (cost=1.12..1.13 rows=1 width=16) (actual time=0.023..0.023 rows=3 loops=1)

19. 0.011 0.011 ↓ 5.0 5 1

Seq Scan on investigation (cost=0.00..1.11 rows=1 width=16) (actual time=0.009..0.011 rows=5 loops=1)

  • Filter: ((state = 'closed'::text) AND (organization_id = 1))
  • Rows Removed by Filter: 2
20. 0.001 0.026 ↓ 2.0 2 1

Subquery Scan on *SELECT* 2 (cost=1.17..1.19 rows=1 width=16) (actual time=0.025..0.026 rows=2 loops=1)

21. 0.015 0.025 ↓ 2.0 2 1

HashAggregate (cost=1.17..1.18 rows=1 width=16) (actual time=0.025..0.025 rows=2 loops=1)

22. 0.010 0.010 ↓ 3.0 3 1

Seq Scan on investigation investigation_1 (cost=0.00..1.12 rows=1 width=16) (actual time=0.007..0.010 rows=3 loops=1)

  • Filter: ((classification = 'malicious'::investigation_classification) AND (state = 'closed'::text) AND (organization_id = 1))
  • Rows Removed by Filter: 4
23. 0.001 0.019 ↓ 2.0 2 1

Subquery Scan on *SELECT* 3 (cost=1.17..1.20 rows=1 width=16) (actual time=0.018..0.019 rows=2 loops=1)

24. 0.010 0.018 ↓ 2.0 2 1

HashAggregate (cost=1.17..1.19 rows=1 width=16) (actual time=0.018..0.018 rows=2 loops=1)

25. 0.008 0.008 ↓ 2.0 2 1

Seq Scan on investigation investigation_2 (cost=0.00..1.14 rows=1 width=16) (actual time=0.007..0.008 rows=2 loops=1)

  • Filter: ((state = 'closed'::text) AND (organization_id = 1) AND ((classification = 'benign'::investigation_classification) OR (classification = 'spam'::investigation_classification)))
  • Rows Removed by Filter: 5
26. 0.002 0.045 ↓ 2.0 2 1

Subquery Scan on *SELECT* 4 (cost=2.25..2.29 rows=1 width=16) (actual time=0.043..0.045 rows=2 loops=1)

27. 0.011 0.043 ↓ 2.0 2 1

GroupAggregate (cost=2.25..2.28 rows=1 width=16) (actual time=0.042..0.043 rows=2 loops=1)

28. 0.008 0.032 ↓ 3.0 3 1

Sort (cost=2.25..2.26 rows=1 width=16) (actual time=0.032..0.032 rows=3 loops=1)

  • Sort Key: (date_trunc('month'::text, investigation_3.created_at))
  • Sort Method: quicksort Memory: 25kB
29. 0.015 0.024 ↓ 3.0 3 1

Hash Join (cost=1.14..2.24 rows=1 width=16) (actual time=0.022..0.024 rows=3 loops=1)

  • Hash Cond: (phish_report.investigation_id = investigation_3.id)
30. 0.003 0.003 ↑ 1.0 7 1

Seq Scan on phish_report (cost=0.00..1.07 rows=7 width=16) (actual time=0.003..0.003 rows=7 loops=1)

31. 0.002 0.006 ↓ 3.0 3 1

Hash (cost=1.12..1.12 rows=1 width=16) (actual time=0.006..0.006 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
32. 0.004 0.004 ↓ 3.0 3 1

Seq Scan on investigation investigation_3 (cost=0.00..1.12 rows=1 width=16) (actual time=0.002..0.004 rows=3 loops=1)

  • Filter: ((classification = 'malicious'::investigation_classification) AND (organization_id = 1) AND (state = 'closed'::text))
  • Rows Removed by Filter: 4
33. 0.000 0.024 ↓ 0.0 0 1

Subquery Scan on *SELECT* 5 (cost=8.30..8.33 rows=1 width=16) (actual time=0.024..0.024 rows=0 loops=1)

34. 0.001 0.024 ↓ 0.0 0 1

GroupAggregate (cost=8.30..8.32 rows=1 width=16) (actual time=0.024..0.024 rows=0 loops=1)

35. 0.001 0.023 ↓ 0.0 0 1

Sort (cost=8.30..8.30 rows=1 width=16) (actual time=0.023..0.023 rows=0 loops=1)

  • Sort Key: (date_trunc('month'::text, investigation_4.created_at))
  • Sort Method: quicksort Memory: 25kB
36. 0.001 0.022 ↓ 0.0 0 1

Nested Loop (cost=1.28..8.29 rows=1 width=16) (actual time=0.022..0.022 rows=0 loops=1)

37. 0.012 0.018 ↓ 3.0 3 1

Hash Join (cost=1.14..2.24 rows=1 width=24) (actual time=0.016..0.018 rows=3 loops=1)

  • Hash Cond: (investigation_message.investigation_id = investigation_4.id)
38. 0.002 0.002 ↑ 1.0 7 1

Seq Scan on investigation_message (cost=0.00..1.07 rows=7 width=24) (actual time=0.002..0.002 rows=7 loops=1)

39. 0.001 0.004 ↓ 3.0 3 1

Hash (cost=1.12..1.12 rows=1 width=16) (actual time=0.004..0.004 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
40. 0.003 0.003 ↓ 3.0 3 1

Seq Scan on investigation investigation_4 (cost=0.00..1.12 rows=1 width=16) (actual time=0.002..0.003 rows=3 loops=1)

  • Filter: ((classification = 'malicious'::investigation_classification) AND (organization_id = 1) AND (state = 'closed'::text))
  • Rows Removed by Filter: 4
41. 0.003 0.003 ↓ 0.0 0 3

Index Scan using index_alert_action_on_organization_id_and_collector_message_id on alert_action (cost=0.14..6.03 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=3)

  • Index Cond: (collector_message_id = investigation_message.collector_message_id)
  • Filter: (result = 'success'::text)
42.          

CTE series

43. 0.026 0.026 ↑ 142.9 7 1

Function Scan on generate_series (cost=0.02..12.52 rows=1,000 width=8) (actual time=0.025..0.026 rows=7 loops=1)

44. 0.010 0.302 ↑ 142.9 7 1

Sort (cost=75.24..77.74 rows=1,000 width=92) (actual time=0.302..0.302 rows=7 loops=1)

  • Sort Key: series.date
  • Sort Method: quicksort Memory: 25kB
45. 0.012 0.292 ↑ 142.9 7 1

Hash Left Join (cost=0.16..25.41 rows=1,000 width=92) (actual time=0.286..0.292 rows=7 loops=1)

  • Hash Cond: (series.date = (metrics.date)::date)
46. 0.032 0.032 ↑ 142.9 7 1

CTE Scan on series (cost=0.00..20.00 rows=1,000 width=4) (actual time=0.027..0.032 rows=7 loops=1)

47. 0.002 0.248 ↑ 1.7 3 1

Hash (cost=0.10..0.10 rows=5 width=96) (actual time=0.248..0.248 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
48. 0.246 0.246 ↑ 1.7 3 1

CTE Scan on metrics (cost=0.00..0.10 rows=5 width=96) (actual time=0.243..0.246 rows=3 loops=1)