explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8oRH

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 1,697.339 ↑ 1.0 20 1

Limit (cost=73,147.19..73,147.24 rows=20 width=359) (actual time=1,697.336..1,697.339 rows=20 loops=1)

  • Buffers: shared hit=838,242 read=17
2.          

CTE _commenters

3. 0.729 2.617 ↑ 42.9 45 1

HashAggregate (cost=603.59..622.89 rows=1,930 width=4) (actual time=2.581..2.617 rows=45 loops=1)

  • Group Key: c.author_id
  • Buffers: shared hit=694
4. 1.888 1.888 ↓ 1.3 2,545 1

Index Scan using index_comment_on_post_id on comment c (cost=0.43..598.62 rows=1,990 width=4) (actual time=0.015..1.888 rows=2,545 loops=1)

  • Index Cond: (post_id = 758,082)
  • Buffers: shared hit=694
5.          

CTE _author

6. 0.013 0.013 ↑ 1.0 1 1

Index Scan using post_pkey on post p (cost=0.42..2.44 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1)

  • Index Cond: (id = 758,082)
  • Buffers: shared hit=4
7.          

CTE _group_members

8. 0.002 7.323 ↑ 1.0 20 1

Limit (cost=1.15..85.79 rows=20 width=8) (actual time=0.030..7.323 rows=20 loops=1)

  • Buffers: shared hit=147 read=17
9. 0.004 7.321 ↑ 196.8 20 1

Nested Loop Anti Join (cost=1.15..16,658.49 rows=3,936 width=8) (actual time=0.029..7.321 rows=20 loops=1)

  • Buffers: shared hit=147 read=17
10. 0.018 0.277 ↑ 196.8 20 1

Nested Loop (cost=0.85..15,425.05 rows=3,936 width=12) (actual time=0.026..0.277 rows=20 loops=1)

  • Buffers: shared hit=122
11. 0.091 0.091 ↑ 278.4 21 1

Index Scan using index_pages_group_memberships_on_group_id on pages_group_memberships pgm (cost=0.43..5,144.58 rows=5,846 width=8) (actual time=0.013..0.091 rows=21 loops=1)

  • Index Cond: (group_id = 8,401)
  • Filter: (status = 'joined'::pages_group_membership_status)
  • Rows Removed by Filter: 15
  • Buffers: shared hit=39
12. 0.168 0.168 ↑ 1.0 1 21

Index Only Scan using index_users_active on users u (cost=0.42..1.76 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=21)

  • Index Cond: (id = pgm.user_id)
  • Filter: (id <> 8)
  • Heap Fetches: 20
  • Buffers: shared hit=83
13. 7.040 7.040 ↓ 0.0 0 20

Index Scan using index_user_event_blocks_on_suggested_user_id on user_event_blocks user_event_blocks_1 (cost=0.29..0.31 rows=1 width=4) (actual time=0.352..0.352 rows=0 loops=20)

  • Index Cond: (suggested_user_id = u.id)
  • Filter: (user_id = 8)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=25 read=17
14.          

CTE _connections

15. 1.792 1.941 ↓ 2.9 887 1

Bitmap Heap Scan on user_connection uc (cost=7.38..317.99 rows=307 width=4) (actual time=0.226..1.941 rows=887 loops=1)

  • Recheck Cond: ('{8}'::integer[] <@ ARRAY[user_a_id, user_b_id])
  • Heap Blocks: exact=694
  • Buffers: shared hit=698
16. 0.149 0.149 ↓ 2.9 887 1

Bitmap Index Scan on "index_user_connection_on_ARRAY_user_a_id_user_b_id_gin__int_ops" (cost=0.00..7.31 rows=307 width=0) (actual time=0.149..0.149 rows=887 loops=1)

  • Index Cond: ('{8}'::integer[] <@ ARRAY[user_a_id, user_b_id])
  • Buffers: shared hit=4
17.          

CTE _combined

18. 0.211 13.153 ↑ 2.4 953 1

Sort (cost=244.27..249.92 rows=2,258 width=12) (actual time=13.088..13.153 rows=953 loops=1)

  • Sort Key: (3)
  • Sort Method: quicksort Memory: 69kB
  • Buffers: shared hit=1,543 read=17
19. 0.477 12.942 ↑ 2.4 953 1

HashAggregate (cost=95.92..118.50 rows=2,258 width=12) (actual time=12.785..12.942 rows=953 loops=1)

  • Group Key: _group_members.id, (3)
  • Buffers: shared hit=1,543 read=17
20. 0.110 12.465 ↑ 2.4 953 1

Append (cost=0.00..84.63 rows=2,258 width=12) (actual time=0.032..12.465 rows=953 loops=1)

  • Buffers: shared hit=1,543 read=17
21. 7.338 7.338 ↑ 1.0 20 1

CTE Scan on _group_members (cost=0.00..0.40 rows=20 width=12) (actual time=0.031..7.338 rows=20 loops=1)

  • Buffers: shared hit=147 read=17
22. 0.008 2.640 ↑ 42.9 45 1

Subquery Scan on *SELECT* 2 (cost=0.00..62.73 rows=1,930 width=12) (actual time=2.585..2.640 rows=45 loops=1)

  • Buffers: shared hit=694
23. 2.632 2.632 ↑ 42.9 45 1

CTE Scan on _commenters (cost=0.00..38.60 rows=1,930 width=8) (actual time=2.583..2.632 rows=45 loops=1)

  • Buffers: shared hit=694
24. 0.001 0.016 ↑ 1.0 1 1

Subquery Scan on *SELECT* 3 (cost=0.00..0.03 rows=1 width=12) (actual time=0.016..0.016 rows=1 loops=1)

  • Buffers: shared hit=4
25. 0.015 0.015 ↑ 1.0 1 1

CTE Scan on _author (cost=0.00..0.02 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=1)

  • Buffers: shared hit=4
26. 0.131 2.361 ↓ 2.9 887 1

Subquery Scan on *SELECT* 4 (cost=0.00..9.98 rows=307 width=12) (actual time=0.228..2.361 rows=887 loops=1)

  • Buffers: shared hit=698
27. 2.230 2.230 ↓ 2.9 887 1

CTE Scan on _connections (cost=0.00..6.14 rows=307 width=8) (actual time=0.227..2.230 rows=887 loops=1)

  • Buffers: shared hit=698
28. 309.189 1,697.336 ↑ 42,278.1 20 1

Sort (cost=71,868.17..73,982.07 rows=845,561 width=359) (actual time=1,697.334..1,697.336 rows=20 loops=1)

  • Sort Key: _combined.source_order
  • Sort Method: top-N heapsort Memory: 32kB
  • Buffers: shared hit=838,242 read=17
29. 188.728 1,388.147 ↓ 1.0 852,401 1

Merge Left Join (cost=208.18..49,368.09 rows=845,561 width=359) (actual time=13.665..1,388.147 rows=852,401 loops=1)

  • Merge Cond: (users.id = _combined.user_id)
  • Buffers: shared hit=838,242 read=17
30. 297.121 1,185.567 ↓ 1.0 852,399 1

Merge Anti Join (cost=37.24..47,056.76 rows=845,561 width=355) (actual time=0.054..1,185.567 rows=852,399 loops=1)

  • Merge Cond: (users.id = user_event_blocks.suggested_user_id)
  • Buffers: shared hit=836,699
31. 888.392 888.392 ↓ 1.0 852,439 1

Index Scan using index_users_active on users (cost=0.42..44,905.08 rows=845,609 width=355) (actual time=0.011..888.392 rows=852,439 loops=1)

  • Filter: (id <> 8)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=836,695
32. 0.028 0.054 ↑ 1.2 60 1

Sort (cost=36.81..36.99 rows=72 width=4) (actual time=0.041..0.054 rows=60 loops=1)

  • Sort Key: user_event_blocks.suggested_user_id
  • Sort Method: quicksort Memory: 27kB
  • Buffers: shared hit=4
33. 0.026 0.026 ↑ 1.2 60 1

Index Scan using index_user_event_blocks_on_user_id on user_event_blocks (cost=0.29..34.59 rows=72 width=4) (actual time=0.012..0.026 rows=60 loops=1)

  • Index Cond: (user_id = 8)
  • Buffers: shared hit=4
34. 0.479 13.852 ↑ 2.4 953 1

Sort (cost=170.94..176.58 rows=2,258 width=12) (actual time=13.606..13.852 rows=953 loops=1)

  • Sort Key: _combined.user_id
  • Sort Method: quicksort Memory: 69kB
  • Buffers: shared hit=1,543 read=17
35. 13.373 13.373 ↑ 2.4 953 1

CTE Scan on _combined (cost=0.00..45.16 rows=2,258 width=12) (actual time=13.091..13.373 rows=953 loops=1)

  • Buffers: shared hit=1,543 read=17
Planning time : 1.638 ms
Execution time : 1,697.864 ms