explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qPPS

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 2,118.518 ↑ 1.0 20 1

Limit (cost=88,889.94..88,889.99 rows=20 width=359) (actual time=2,118.514..2,118.518 rows=20 loops=1)

  • Buffers: shared hit=867,397 read=9,545
2.          

CTE _commenters

3. 3.377 27.856 ↑ 44.2 45 1

HashAggregate (cost=4,749.33..4,769.23 rows=1,990 width=355) (actual time=27.826..27.856 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. 1.459 24.479 ↓ 1.3 2,545 1

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

  • Workers Planned: 3
  • Workers Launched: 0
  • Buffers: shared hit=10,180 read=694
5. 0.657 23.020 ↓ 4.0 2,545 1

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

  • Buffers: shared hit=10,180 read=694
6. 15.124 17.273 ↓ 4.0 2,545 1

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

  • Recheck Cond: (post_id = 758,082)
  • Heap Blocks: exact=684
  • Buffers: shared read=694
7. 2.149 2.149 ↓ 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=2.149..2.149 rows=2,545 loops=1)

  • Index Cond: (post_id = 758,082)
  • Buffers: shared read=10
8. 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
9.          

CTE _author

10. 0.005 3.142 ↑ 1.0 1 1

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

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

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

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

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

CTE _group_members

14. 1.709 383.586 ↑ 1.0 6,093 1

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

  • Workers Planned: 4
  • Workers Launched: 0
  • Buffers: shared hit=22,961 read=5,702
15. 8.038 381.877 ↓ 3.9 6,093 1

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

  • Buffers: shared hit=22,961 read=5,702
16. 96.603 99.654 ↓ 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.327..99.654 rows=6,093 loops=1)

  • Recheck Cond: (group_id = 8,401)
  • Heap Blocks: exact=4,252
  • Buffers: shared read=4,272
17. 3.051 3.051 ↑ 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.051..3.051 rows=6,093 loops=1)

  • Index Cond: (group_id = 8,401)
  • Buffers: shared read=20
18. 274.185 274.185 ↑ 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.045..0.045 rows=1 loops=6,093)

  • Index Cond: (id = pgm.user_id)
  • Buffers: shared hit=22,961 read=1,430
19.          

CTE _connections

20. 25.006 25.686 ↓ 2.9 887 1

Bitmap Heap Scan on user_connection uc (cost=7.38..317.99 rows=307 width=4) (actual time=1.560..25.686 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.680 0.680 ↓ 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.680..0.680 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. 0.919 453.604 ↑ 1.2 7,026 1

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

  • Sort Key: (3)
  • Sort Method: quicksort Memory: 522kB
  • Buffers: shared hit=33,146 read=7,097
24. 4.446 452.685 ↑ 1.2 7,026 1

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

  • Group Key: _group_members.id, (3)
  • Buffers: shared hit=33,146 read=7,097
25. 0.906 448.239 ↑ 1.2 7,026 1

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

  • Buffers: shared hit=33,146 read=7,097
26. 390.344 390.344 ↑ 1.0 6,093 1

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

  • Buffers: shared hit=22,961 read=5,702
27. 27.892 27.892 ↑ 44.2 45 1

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

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

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

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

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

  • Buffers: shared hit=1 read=697
30. 272.304 2,118.514 ↑ 42,278.1 20 1

Sort (cost=72,495.22..74,609.12 rows=845,561 width=359) (actual time=2,118.513..2,118.514 rows=20 loops=1)

  • Sort Key: _combined.source_order
  • Sort Method: top-N heapsort Memory: 32kB
  • Buffers: shared hit=867,397 read=9,545
31. 167.158 1,846.210 ↓ 1.0 852,461 1

Merge Left Join (cost=762.00..49,995.14 rows=845,561 width=359) (actual time=459.451..1,846.210 rows=852,461 loops=1)

  • Merge Cond: (users.id = _combined.user_id)
  • Buffers: shared hit=867,397 read=9,545
32. 263.827 1,221.545 ↓ 1.0 852,399 1

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

  • Merge Cond: (users.id = user_event_blocks.suggested_user_id)
  • Buffers: shared hit=834,251 read=2,448
33. 956.049 956.049 ↓ 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=1.766..956.049 rows=852,439 loops=1)

  • Filter: (id <> 8)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=834,251 read=2,444
34. 0.025 1.669 ↑ 1.2 60 1

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

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

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

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

  • Sort Key: _combined.user_id
  • Sort Method: quicksort Memory: 522kB
  • Buffers: shared hit=33,146 read=7,097
37. 454.667 454.667 ↑ 1.2 7,026 1

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

  • Buffers: shared hit=33,146 read=7,097
Planning time : 19.702 ms
Execution time : 2,120.104 ms