explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mQvN

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 6,443.469 ↑ 1.0 20 1

Limit (cost=97,342.69..97,342.74 rows=20 width=363) (actual time=6,443.467..6,443.469 rows=20 loops=1)

  • Buffers: shared hit=836,256 read=40,702
2.          

CTE _commenters

3. 3.748 40.240 ↑ 44.2 45 1

HashAggregate (cost=4,749.33..4,769.23 rows=1,990 width=355) (actual time=40.190..40.240 rows=45 loops=1)

  • Group 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
  • Buffers: shared hit=10,180 read=694
4. 30.579 36.492 ↓ 1.3 2,545 1

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

  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=10,180 read=694
5. 0.361 5.913 ↑ 1.0 636 4 / 4

Nested Loop (cost=22.28..3,416.00 rows=642 width=355) (actual time=0.766..5.913 rows=636 loops=4)

  • Buffers: shared hit=10,180 read=694
6. 3.736 4.280 ↑ 1.0 636 4 / 4

Parallel Bitmap Heap Scan on comment c (cost=21.85..2,009.88 rows=642 width=4) (actual time=0.758..4.280 rows=636 loops=4)

  • Recheck Cond: (post_id = 758,082)
  • Heap Blocks: exact=684
  • Buffers: shared read=694
7. 0.544 0.544 ↓ 1.3 2,545 1 / 4

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

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

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
9.          

CTE _author

10. 0.006 3.766 ↑ 1.0 1 1

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

  • Buffers: shared hit=4 read=4
11. 3.752 3.752 ↑ 1.0 1 1

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

  • Index Cond: (id = 758,082)
  • Buffers: shared read=4
12. 0.008 0.008 ↑ 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.008..0.008 rows=1 loops=1)

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

CTE _group_members

14. 40.818 462.743 ↑ 1.0 6,093 1

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

  • Workers Planned: 4
  • Workers Launched: 4
  • Buffers: shared hit=17,361 read=11,318
15. 1.355 421.925 ↑ 1.3 1,219 5 / 5

Nested Loop (cost=66.92..8,681.41 rows=1,550 width=355) (actual time=3.021..421.925 rows=1,219 loops=5)

  • Buffers: shared hit=17,361 read=11,318
16. 32.280 33.055 ↑ 1.3 1,219 5 / 5

Parallel Bitmap Heap Scan on pages_group_memberships pgm (cost=66.50..5,767.88 rows=1,550 width=8) (actual time=1.203..33.055 rows=1,219 loops=5)

  • Recheck Cond: (group_id = 8,401)
  • Heap Blocks: exact=898
  • Buffers: shared read=4,272
17. 0.775 0.775 ↑ 1.0 6,093 1 / 5

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

  • Index Cond: (group_id = 8,401)
  • Buffers: shared read=20
18. 387.515 387.515 ↑ 1.0 1 6,093 / 5

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

  • Index Cond: (id = pgm.user_id)
  • Buffers: shared hit=17,361 read=7,046
19.          

CTE _connections

20. 23.935 24.595 ↓ 2.9 887 1

Bitmap Heap Scan on user_connection uc (cost=7.38..317.99 rows=307 width=4) (actual time=1.614..24.595 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
21. 0.660 0.660 ↓ 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.660..0.660 rows=887 loops=1)

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

CTE _combined

23. 1.215 541.498 ↑ 1.2 7,026 1

Sort (cost=979.76..1,001.01 rows=8,500 width=8) (actual time=541.119..541.498 rows=7,026 loops=1)

  • Sort Key: (3)
  • Sort Method: quicksort Memory: 522kB
  • Buffers: shared hit=27,546 read=12,713
24. 4.585 540.283 ↑ 1.2 7,026 1

HashAggregate (cost=340.00..425.00 rows=8,500 width=8) (actual time=538.830..540.283 rows=7,026 loops=1)

  • Group Key: _group_members.id, (3)
  • Buffers: shared hit=27,546 read=12,713
25. 0.720 535.698 ↑ 1.2 7,026 1

Append (cost=0.00..297.50 rows=8,500 width=8) (actual time=8.323..535.698 rows=7,026 loops=1)

  • Buffers: shared hit=27,546 read=12,713
26. 466.036 466.036 ↑ 1.0 6,093 1

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

  • Buffers: shared hit=17,361 read=11,318
27. 40.287 40.287 ↑ 44.2 45 1

CTE Scan on _commenters (cost=0.00..39.80 rows=1,990 width=8) (actual time=40.198..40.287 rows=45 loops=1)

  • Buffers: shared hit=10,180 read=694
28. 3.770 3.770 ↑ 1.0 1 1

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

  • Buffers: shared hit=4 read=4
29. 24.885 24.885 ↓ 2.9 887 1

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

  • Buffers: shared hit=1 read=697
30. 293.222 6,443.465 ↑ 42,273.8 20 1

Sort (cost=80,947.96..83,061.65 rows=845,476 width=363) (actual time=6,443.464..6,443.465 rows=20 loops=1)

  • Sort Key: _combined.source_order, (similarity(concat_ws(' '::text, users.first_name, users.last_name), 'a'::text))
  • Sort Method: top-N heapsort Memory: 32kB
  • Buffers: shared hit=836,256 read=40,702
31. 1,455.511 6,150.243 ↑ 1.2 718,129 1

Merge Left Join (cost=762.00..58,450.15 rows=845,476 width=363) (actual time=548.157..6,150.243 rows=718,129 loops=1)

  • Merge Cond: (users.id = _combined.user_id)
  • Buffers: shared hit=836,256 read=40,702
32. 277.470 4,148.513 ↑ 1.2 718,079 1

Merge Anti Join (cost=37.24..51,284.60 rows=845,476 width=355) (actual time=3.809..4,148.513 rows=718,079 loops=1)

  • Merge Cond: (users.id = user_event_blocks.suggested_user_id)
  • Buffers: shared hit=808,710 read=27,989
33. 3,869.167 3,869.167 ↑ 1.2 718,113 1

Index Scan using index_users_active on users (cost=0.42..49,133.13 rows=845,524 width=355) (actual time=1.960..3,869.167 rows=718,113 loops=1)

  • Filter: ((id <> 8) AND (concat_ws(' '::text, first_name, last_name) ~~* '%a%'::text))
  • Rows Removed by Filter: 134,327
  • Buffers: shared hit=808,710 read=27,985
34. 0.052 1.876 ↑ 1.2 60 1

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

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

  • Index Cond: (user_id = 8)
  • Buffers: shared read=4
36. 3.411 546.219 ↑ 1.2 7,026 1

Sort (cost=724.76..746.01 rows=8,500 width=8) (actual time=544.319..546.219 rows=7,026 loops=1)

  • Sort Key: _combined.user_id
  • Sort Method: quicksort Memory: 522kB
  • Buffers: shared hit=27,546 read=12,713
37. 542.808 542.808 ↑ 1.2 7,026 1

CTE Scan on _combined (cost=0.00..170.00 rows=8,500 width=8) (actual time=541.123..542.808 rows=7,026 loops=1)

  • Buffers: shared hit=27,546 read=12,713
Planning time : 24.073 ms
Execution time : 6,462.884 ms