explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EPL7

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Sort (cost=1,281.44..1,281.51 rows=28 width=172) (actual rows= loops=)

  • Sort Key: "*SELECT* 1".seen DESC NULLS LAST
2. 0.000 0.000 ↓ 0.0

Append (cost=45.49..1,280.76 rows=28 width=172) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=45.49..433.35 rows=10 width=172) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

HashAggregate (cost=45.49..433.25 rows=10 width=147) (actual rows= loops=)

  • Group Key: users.id, roles.id
5. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=25.96..45.31 rows=10 width=147) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Hash Join (cost=25.81..42.51 rows=10 width=147) (actual rows= loops=)

  • Hash Cond: (stories.user_id = users.id)
7. 0.000 0.000 ↓ 0.0

Seq Scan on stories (cost=0.00..14.80 rows=480 width=24) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Hash (cost=25.76..25.76 rows=4 width=131) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.15..25.76 rows=4 width=131) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Seq Scan on users (cost=0.00..1.04 rows=4 width=99) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Index Scan using roles_pkey on roles (cost=0.15..6.17 rows=1 width=40) (actual rows= loops=)

  • Index Cond: (id = users.role_id)
12. 0.000 0.000 ↓ 0.0

Index Scan using index_taggings_on_taggable_id_and_taggable_type on taggings (cost=0.15..0.27 rows=1 width=12) (actual rows= loops=)

  • Index Cond: ((taggable_id = stories.id) AND ((taggable_type)::text = 'Story'::text))
13.          

SubPlan (forHashAggregate)

14. 0.000 0.000 ↓ 0.0

Aggregate (cost=25.06..25.07 rows=1 width=0) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

HashAggregate (cost=25.03..25.04 rows=1 width=16) (actual rows= loops=)

  • Group Key: story_views.user_id
  • Filter: (count(stories.id) = count(story_views.story_id))
16. 0.000 0.000 ↓ 0.0

Seq Scan on story_views (cost=0.00..25.00 rows=6 width=16) (actual rows= loops=)

  • Filter: (array_agg(stories.id) @> ARRAY[story_id])
17. 0.000 0.000 ↓ 0.0

Aggregate (cost=13.68..13.70 rows=1 width=8) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on story_views story_views_1 (cost=4.20..13.68 rows=1 width=8) (actual rows= loops=)

  • Recheck Cond: (user_id = 2)
  • Filter: (array_agg(stories.id) @> ARRAY[story_id])
19. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on index_story_views_on_user_id (cost=0.00..4.20 rows=6 width=0) (actual rows= loops=)

  • Index Cond: (user_id = 2)
20. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=52.63..847.42 rows=18 width=172) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

HashAggregate (cost=52.63..847.24 rows=18 width=147) (actual rows= loops=)

  • Group Key: users_1.id, roles_1.id
22. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=25.96..52.31 rows=18 width=147) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Hash Join (cost=25.81..48.09 rows=18 width=147) (actual rows= loops=)

  • Hash Cond: (streams.user_id = users_1.id)
24. 0.000 0.000 ↓ 0.0

Seq Scan on streams (cost=0.00..18.80 rows=880 width=24) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Hash (cost=25.76..25.76 rows=4 width=131) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.15..25.76 rows=4 width=131) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Seq Scan on users users_1 (cost=0.00..1.04 rows=4 width=99) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Index Scan using roles_pkey on roles roles_1 (cost=0.15..6.17 rows=1 width=40) (actual rows= loops=)

  • Index Cond: (id = users_1.role_id)
29. 0.000 0.000 ↓ 0.0

Index Scan using index_taggings_on_taggable_id_and_taggable_type on taggings taggings_1 (cost=0.15..0.22 rows=1 width=12) (actual rows= loops=)

  • Index Cond: ((taggable_id = streams.id) AND ((taggable_type)::text = 'Stream'::text))
30.          

SubPlan (forHashAggregate)

31. 0.000 0.000 ↓ 0.0

Aggregate (cost=29.71..29.72 rows=1 width=0) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

HashAggregate (cost=29.66..29.69 rows=2 width=16) (actual rows= loops=)

  • Group Key: stream_views.user_id
  • Filter: (count(streams.id) = count(stream_views.stream_id))
33. 0.000 0.000 ↓ 0.0

Seq Scan on stream_views (cost=0.00..29.62 rows=8 width=16) (actual rows= loops=)

  • Filter: (array_agg(streams.id) @> ARRAY[stream_id])
34. 0.000 0.000 ↓ 0.0

Aggregate (cost=14.39..14.41 rows=1 width=8) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on stream_views stream_views_1 (cost=4.21..14.39 rows=1 width=8) (actual rows= loops=)

  • Recheck Cond: (user_id = 2)
  • Filter: (array_agg(streams.id) @> ARRAY[stream_id])
36. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on index_stream_views_on_user_id (cost=0.00..4.21 rows=8 width=0) (actual rows= loops=)

  • Index Cond: (user_id = 2)