explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TAFc

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 7,635.275 ↑ 1.0 20 1

Limit (cost=88,398.23..88,398.28 rows=20 width=359) (actual time=7,635.271..7,635.275 rows=20 loops=1)

  • Buffers: shared hit=836,271 read=40,701 written=124
2.          

CTE _commenters

3. 0.005 47.156 ↑ 1.0 20 1

Limit (cost=4,724.04..4,725.44 rows=20 width=355) (actual time=46.005..47.156 rows=20 loops=1)

  • Buffers: shared hit=10,210 read=694 written=21
4. 1.063 47.151 ↑ 99.5 20 1

Unique (cost=4,724.04..4,863.34 rows=1,990 width=355) (actual time=46.002..47.151 rows=20 loops=1)

  • Buffers: shared hit=10,210 read=694 written=21
5. 13.145 46.088 ↑ 1.7 1,175 1

Sort (cost=4,724.04..4,729.02 rows=1,990 width=355) (actual time=46.000..46.088 rows=1,175 loops=1)

  • Sort Key: u.id, u.fb_id, u.random_key_int, u.first_name, u.last_name, u.email, u.birthday, u.age_range_min, u.age_range_max, u.age_range_updated_at, u.work_status_id, u.fb_updated_time, u.completed_reg_at, u.created_at, u.updated_at, u.gender, u.google_id, u.email_status, u.account_status, u.birthday_exact, u.firebase_id, u.firebase_metadata, u.home_screen, u.apple_user_id, u.sendgrid_id, u.guidelines_version, u.guidelines_accepted_at
  • Sort Method: quicksort Memory: 994kB
  • Buffers: shared hit=10,210 read=694 written=21
6. 1.447 32.943 ↓ 1.3 2,545 1

Gather (cost=1,022.28..4,615.00 rows=1,990 width=355) (actual time=5.992..32.943 rows=2,545 loops=1)

  • Workers Planned: 3
  • Workers Launched: 0
  • Buffers: shared hit=10,180 read=694 written=21
7. 2.649 31.496 ↓ 4.0 2,545 1

Nested Loop (cost=22.28..3,416.00 rows=642 width=355) (actual time=5.140..31.496 rows=2,545 loops=1)

  • Buffers: shared hit=10,180 read=694 written=21
8. 20.115 23.757 ↓ 4.0 2,545 1

Parallel Bitmap Heap Scan on comment c (cost=21.85..2,009.88 rows=642 width=4) (actual time=5.110..23.757 rows=2,545 loops=1)

  • Recheck Cond: (post_id = 758,082)
  • Heap Blocks: exact=684
  • Buffers: shared read=694 written=21
9. 3.642 3.642 ↓ 1.3 2,545 1

Bitmap Index Scan on index_comment_on_post_id (cost=0.00..21.36 rows=1,990 width=0) (actual time=3.642..3.642 rows=2,545 loops=1)

  • Index Cond: (post_id = 758,082)
  • Buffers: shared read=10
10. 5.090 5.090 ↑ 1.0 1 2,545

Index Scan using users_pkey on users u (cost=0.43..2.19 rows=1 width=355) (actual time=0.002..0.002 rows=1 loops=2,545)

  • Index Cond: (id = c.author_id)
  • Buffers: shared hit=10,180
11.          

CTE _author

12. 0.007 3.375 ↑ 1.0 1 1

Nested Loop (cost=0.85..4.89 rows=1 width=355) (actual time=3.374..3.375 rows=1 loops=1)

  • Buffers: shared hit=4 read=4
13. 3.353 3.353 ↑ 1.0 1 1

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

  • Index Cond: (id = 758,082)
  • Buffers: shared read=4
14. 0.015 0.015 ↑ 1.0 1 1

Index Scan using users_pkey on users u_1 (cost=0.43..2.45 rows=1 width=355) (actual time=0.015..0.015 rows=1 loops=1)

  • Index Cond: (id = p.author_id)
  • Buffers: shared hit=4
15.          

CTE _group_members

16. 2.502 2,776.967 ↑ 1.0 6,093 1

Gather (cost=1,066.92..10,301.61 rows=6,202 width=355) (actual time=7.384..2,776.967 rows=6,093 loops=1)

  • Workers Planned: 4
  • Workers Launched: 0
  • Buffers: shared hit=17,346 read=11,317 written=66
17. 8.968 2,774.465 ↓ 3.9 6,093 1

Nested Loop (cost=66.92..8,681.41 rows=1,550 width=355) (actual time=6.517..2,774.465 rows=6,093 loops=1)

  • Buffers: shared hit=17,346 read=11,317 written=66
18. 178.341 182.065 ↓ 3.9 6,093 1

Parallel Bitmap Heap Scan on pages_group_memberships pgm (cost=66.50..5,767.88 rows=1,550 width=8) (actual time=4.936..182.065 rows=6,093 loops=1)

  • Recheck Cond: (group_id = 8,401)
  • Heap Blocks: exact=4,252
  • Buffers: shared read=4,272 written=26
19. 3.724 3.724 ↑ 1.0 6,093 1

Bitmap Index Scan on index_pages_group_memberships_on_group_id (cost=0.00..64.95 rows=6,202 width=0) (actual time=3.724..3.724 rows=6,093 loops=1)

  • Index Cond: (group_id = 8,401)
  • Buffers: shared read=20
20. 2,583.432 2,583.432 ↑ 1.0 1 6,093

Index Scan using users_pkey on users u_2 (cost=0.43..1.88 rows=1 width=355) (actual time=0.424..0.424 rows=1 loops=6,093)

  • Index Cond: (id = pgm.user_id)
  • Buffers: shared hit=17,346 read=7,045 written=40
21.          

CTE _connections

22. 23.895 24.567 ↓ 2.9 887 1

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

  • Recheck Cond: ('{8}'::integer[] <@ ARRAY[user_a_id, user_b_id])
  • Heap Blocks: exact=694
  • Buffers: shared hit=1 read=697 written=8
23. 0.672 0.672 ↓ 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.672..0.672 rows=887 loops=1)

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

CTE _combined

25. 1.382 2,873.425 ↓ 1.1 7,001 1

Sort (cost=740.27..756.59 rows=6,530 width=8) (actual time=2,873.053..2,873.425 rows=7,001 loops=1)

  • Sort Key: (3)
  • Sort Method: quicksort Memory: 521kB
  • Buffers: shared hit=27,561 read=12,712 written=95
26. 6.528 2,872.043 ↓ 1.1 7,001 1

HashAggregate (cost=261.20..326.50 rows=6,530 width=8) (actual time=2,870.523..2,872.043 rows=7,001 loops=1)

  • Group Key: _group_members.id, (3)
  • Buffers: shared hit=27,561 read=12,712 written=95
27. 1.068 2,865.515 ↓ 1.1 7,001 1

Append (cost=0.00..228.55 rows=6,530 width=8) (actual time=7.391..2,865.515 rows=7,001 loops=1)

  • Buffers: shared hit=27,561 read=12,712 written=95
28. 2,789.062 2,789.062 ↑ 1.0 6,093 1

CTE Scan on _group_members (cost=0.00..124.04 rows=6,202 width=8) (actual time=7.390..2,789.062 rows=6,093 loops=1)

  • Buffers: shared hit=17,346 read=11,317 written=66
29. 47.168 47.168 ↑ 1.0 20 1

CTE Scan on _commenters (cost=0.00..0.40 rows=20 width=8) (actual time=46.010..47.168 rows=20 loops=1)

  • Buffers: shared hit=10,210 read=694 written=21
30. 3.380 3.380 ↑ 1.0 1 1

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

  • Buffers: shared hit=4 read=4
31. 24.837 24.837 ↓ 2.9 887 1

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

  • Buffers: shared hit=1 read=697 written=8
32. 347.511 7,635.271 ↑ 42,278.1 20 1

Sort (cost=72,291.71..74,405.62 rows=845,561 width=359) (actual time=7,635.270..7,635.271 rows=20 loops=1)

  • Sort Key: _combined.source_order
  • Sort Method: top-N heapsort Memory: 32kB
  • Buffers: shared hit=836,271 read=40,701 written=124
33. 206.023 7,287.760 ↓ 1.0 852,436 1

Merge Left Join (cost=581.61..49,791.64 rows=845,561 width=359) (actual time=2,883.399..7,287.760 rows=852,436 loops=1)

  • Merge Cond: (users.id = _combined.user_id)
  • Buffers: shared hit=836,271 read=40,701 written=124
34. 337.434 4,203.230 ↓ 1.0 852,399 1

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

  • Merge Cond: (users.id = user_event_blocks.suggested_user_id)
  • Buffers: shared hit=808,710 read=27,989 written=29
35. 3,863.851 3,863.851 ↓ 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=4.987..3,863.851 rows=852,439 loops=1)

  • Filter: (id <> 8)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=808,710 read=27,985 written=29
36. 0.057 1.945 ↑ 1.2 60 1

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

  • Sort Key: user_event_blocks.suggested_user_id
  • Sort Method: quicksort Memory: 27kB
  • Buffers: shared read=4
37. 1.888 1.888 ↑ 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=1.445..1.888 rows=60 loops=1)

  • Index Cond: (user_id = 8)
  • Buffers: shared read=4
38. 3.672 2,878.507 ↓ 1.1 7,001 1

Sort (cost=544.37..560.69 rows=6,530 width=8) (actual time=2,876.496..2,878.507 rows=7,001 loops=1)

  • Sort Key: _combined.user_id
  • Sort Method: quicksort Memory: 521kB
  • Buffers: shared hit=27,561 read=12,712 written=95
39. 2,874.835 2,874.835 ↓ 1.1 7,001 1

CTE Scan on _combined (cost=0.00..130.60 rows=6,530 width=8) (actual time=2,873.055..2,874.835 rows=7,001 loops=1)

  • Buffers: shared hit=27,561 read=12,712 written=95
Planning time : 34.524 ms
Execution time : 7,637.249 ms