explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Q9Ba : Optimization for: plan #VPlf

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.866 1,268.118 ↑ 1.0 15 1

Limit (cost=288,253.05..288,253.50 rows=15 width=20) (actual time=1,267.241..1,268.118 rows=15 loops=1)

2. 0.005 1,267.252 ↑ 2.0 15 1

Merge Append (cost=288,253.05..288,253.95 rows=30 width=20) (actual time=1,267.240..1,267.252 rows=15 loops=1)

  • Sort Key: posts.created_at DESC
3. 0.002 1,261.637 ↑ 1.1 14 1

Limit (cost=286,792.90..286,793.05 rows=15 width=20) (actual time=1,261.629..1,261.637 rows=14 loops=1)

4. 0.006 1,261.635 ↑ 25,745.8 14 1

Unique (cost=286,792.90..290,397.31 rows=360,441 width=20) (actual time=1,261.628..1,261.635 rows=14 loops=1)

5. 141.549 1,261.629 ↑ 25,745.8 14 1

Sort (cost=286,792.90..287,694.00 rows=360,441 width=20) (actual time=1,261.627..1,261.629 rows=14 loops=1)

  • Sort Key: posts.created_at DESC, posts.id, posts.updated_at
  • Sort Method: external merge Disk: 7024kB
6. 0.000 1,120.080 ↑ 1.7 210,820 1

Gather (cost=8,982.19..246,133.27 rows=360,441 width=20) (actual time=53.340..1,120.080 rows=210,820 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 43.366 1,142.027 ↑ 2.1 70,273 3

Parallel Hash Left Join (cost=7,982.19..209,089.17 rows=150,184 width=20) (actual time=45.323..1,142.027 rows=70,273 loops=3)

  • Hash Cond: (posts.id = share_visibilities.shareable_id)
  • Filter: (((share_visibilities.user_id = 1) AND ((share_visibilities.shareable_type)::text = 'Post'::text) AND (NOT share_visibilities.hidden)) OR posts.public)
  • Rows Removed by Filter: 5018
8. 207.698 1,060.253 ↑ 2.2 70,842 3

Hash Join (cost=1,726.13..198,615.18 rows=152,508 width=21) (actual time=6.136..1,060.253 rows=70,842 loops=3)

  • Hash Cond: (posts.author_id = people.id)
9. 846.531 846.531 ↑ 1.0 1,171,835 3

Parallel Seq Scan on posts (cost=0.00..193,675.27 rows=1,222,699 width=25) (actual time=0.058..846.531 rows=1,171,835 loops=3)

  • Filter: (((type)::text = ANY ('{StatusMessage,Reshare}'::text[])) AND (created_at < '2018-12-29 20:20:37'::timestamp without time zone))
  • Rows Removed by Filter: 1473
10. 0.161 6.024 ↑ 1.1 385 3

Hash (cost=1,721.07..1,721.07 rows=405 width=4) (actual time=6.024..6.024 rows=385 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
11. 0.142 5.863 ↑ 1.1 385 3

Unique (cost=1,714.99..1,717.02 rows=405 width=4) (actual time=5.671..5.863 rows=385 loops=3)

12. 0.346 5.721 ↑ 1.0 401 3

Sort (cost=1,714.99..1,716.00 rows=405 width=4) (actual time=5.670..5.721 rows=401 loops=3)

  • Sort Key: people.id
  • Sort Method: quicksort Memory: 43kB
  • Worker 0: Sort Method: quicksort Memory: 43kB
  • Worker 1: Sort Method: quicksort Memory: 43kB
13. 2.387 5.375 ↑ 1.0 401 3

Nested Loop (cost=94.23..1,697.45 rows=405 width=4) (actual time=0.571..5.375 rows=401 loops=3)

14. 1.413 2.983 ↑ 1.0 401 3

Hash Join (cost=93.94..253.24 rows=405 width=4) (actual time=0.522..2.983 rows=401 loops=3)

  • Hash Cond: (contacts.id = aspect_memberships.contact_id)
15. 1.097 1.097 ↑ 1.0 5,720 3

Seq Scan on contacts (cost=0.00..105.20 rows=5,720 width=8) (actual time=0.025..1.097 rows=5,720 loops=3)

16. 0.151 0.473 ↑ 1.0 401 3

Hash (cost=88.88..88.88 rows=405 width=4) (actual time=0.473..0.473 rows=401 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
17. 0.250 0.322 ↑ 1.0 401 3

Bitmap Heap Scan on aspect_memberships (cost=42.26..88.88 rows=405 width=4) (actual time=0.092..0.322 rows=401 loops=3)

  • Recheck Cond: (aspect_id = ANY ('{1,2,3,4,5,14,15,16,17,59,101}'::integer[]))
  • Heap Blocks: exact=37
18. 0.072 0.072 ↑ 1.0 401 3

Bitmap Index Scan on index_aspect_memberships_on_aspect_id_and_contact_id (cost=0.00..42.16 rows=405 width=0) (actual time=0.072..0.072 rows=401 loops=3)

  • Index Cond: (aspect_id = ANY ('{1,2,3,4,5,14,15,16,17,59,101}'::integer[]))
19. 0.005 0.005 ↑ 1.0 1 1,203

Index Only Scan using people_pkey on people (cost=0.29..3.57 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1,203)

  • Index Cond: (id = contacts.person_id)
  • Heap Fetches: 1203
20. 13.269 38.408 ↑ 1.3 29,250 3

Parallel Hash (cost=5,796.12..5,796.12 rows=36,795 width=14) (actual time=38.408..38.408 rows=29,250 loops=3)

  • Buckets: 131072 Batches: 1 Memory Usage: 5184kB
21. 25.139 25.139 ↑ 1.3 29,250 3

Parallel Index Only Scan using shareable_and_hidden_and_user_id on share_visibilities (cost=0.42..5,796.12 rows=36,795 width=14) (actual time=0.048..25.139 rows=29,250 loops=3)

  • Index Cond: (shareable_type = 'Post'::text)
  • Heap Fetches: 87751
22. 0.002 5.610 ↑ 7.5 2 1

Limit (cost=1,460.15..1,460.30 rows=15 width=20) (actual time=5.608..5.610 rows=2 loops=1)

23. 0.002 5.608 ↑ 218.5 2 1

Unique (cost=1,460.15..1,464.52 rows=437 width=20) (actual time=5.607..5.608 rows=2 loops=1)

24. 1.336 5.606 ↑ 218.5 2 1

Sort (cost=1,460.15..1,461.24 rows=437 width=20) (actual time=5.606..5.606 rows=2 loops=1)

  • Sort Key: posts_1.created_at DESC, posts_1.id, posts_1.updated_at
  • Sort Method: quicksort Memory: 375kB
25. 4.270 4.270 ↓ 8.2 3,571 1

Index Scan using index_posts_on_person_id on posts posts_1 (cost=0.43..1,440.98 rows=437 width=20) (actual time=0.038..4.270 rows=3,571 loops=1)

  • Index Cond: (author_id = 1)
  • Filter: (((type)::text = ANY ('{StatusMessage,Reshare}'::text[])) AND (created_at < '2018-12-29 20:20:37'::timestamp without time zone))
  • Rows Removed by Filter: 3