explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xS1V

Settings
# exclusive inclusive rows x rows loops node
1. 0.025 25.754 ↑ 3.6 5 1

GroupAggregate (cost=5,869.38..5,890.13 rows=18 width=60) (actual time=25.734..25.754 rows=5 loops=1)

  • Group Key: vulnerability_historical_statistics.date
2. 0.055 25.729 ↑ 13.1 70 1

Sort (cost=5,869.38..5,871.67 rows=914 width=32) (actual time=25.725..25.729 rows=70 loops=1)

  • Sort Key: vulnerability_historical_statistics.date
  • Sort Method: quicksort Memory: 30kB
3. 0.224 25.674 ↑ 13.1 70 1

Hash Semi Join (cost=5,739.93..5,824.43 rows=914 width=32) (actual time=25.154..25.674 rows=70 loops=1)

  • Hash Cond: (vulnerability_historical_statistics.project_id = projects.id)
4. 0.732 0.732 ↓ 1.0 917 1

Index Scan using index_vulnerability_historical_statistics_on_date_and_id on vulnerability_historical_statistics (cost=0.28..72.21 rows=914 width=40) (actual time=0.067..0.732 rows=917 loops=1)

  • Index Cond: ((date >= '2020-04-30'::date) AND (date <= '2020-07-29'::date))
5. 0.302 24.718 ↑ 2.7 978 1

Hash (cost=5,706.15..5,706.15 rows=2,680 width=4) (actual time=24.718..24.718 rows=978 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 67kB
6. 0.118 24.416 ↑ 2.7 978 1

Nested Loop (cost=1,425.21..5,706.15 rows=2,680 width=4) (actual time=4.771..24.416 rows=978 loops=1)

7. 0.089 4.714 ↓ 1.0 153 1

HashAggregate (cost=1,424.77..1,426.28 rows=151 width=4) (actual time=4.671..4.714 rows=153 loops=1)

  • Group Key: namespaces.id
8. 4.625 4.625 ↓ 1.0 153 1

CTE Scan on base_and_descendants namespaces (cost=1,419.86..1,422.88 rows=151 width=4) (actual time=0.077..4.625 rows=153 loops=1)

9.          

CTE base_and_descendants

10. 0.359 4.391 ↓ 1.0 153 1

Recursive Union (cost=0.43..1,419.86 rows=151 width=347) (actual time=0.074..4.391 rows=153 loops=1)

11. 0.062 0.062 ↑ 1.0 1 1

Index Scan using namespaces_pkey on namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=347) (actual time=0.062..0.062 rows=1 loops=1)

  • Index Cond: (id = 9,970)
  • Filter: ((type)::text = 'Group'::text)
12. 0.130 3.970 ↓ 2.0 30 5

Nested Loop (cost=0.56..141.34 rows=15 width=347) (actual time=0.067..0.794 rows=30 loops=5)

13. 0.015 0.015 ↓ 3.1 31 5

WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.003 rows=31 loops=5)

14. 3.825 3.825 ↑ 2.0 1 153

Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_2 (cost=0.56..14.09 rows=2 width=347) (actual time=0.015..0.025 rows=1 loops=153)

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)
15. 19.584 19.584 ↑ 3.0 6 153

Index Scan using index_projects_on_namespace_id_and_id on projects (cost=0.43..28.16 rows=18 width=8) (actual time=0.026..0.128 rows=6 loops=153)

  • Index Cond: (namespace_id = namespaces.id)
  • Filter: ((NOT archived) AND (NOT pending_delete))
  • Rows Removed by Filter: 0
Planning time : 9.576 ms
Execution time : 26.309 ms