explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VPlf

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.006 6,749.270 ↑ 1.0 15 1

Limit (cost=299,373.11..299,373.52 rows=15 width=20) (actual time=6,749.257..6,749.270 rows=15 loops=1)

2. 0.002 6,749.264 ↑ 2.0 15 1

Merge Append (cost=299,373.11..299,373.93 rows=30 width=20) (actual time=6,749.256..6,749.264 rows=15 loops=1)

  • Sort Key: posts.created_at DESC
3. 0.001 6,744.265 ↑ 1.0 15 1

Limit (cost=297,907.69..297,907.84 rows=15 width=20) (actual time=6,744.257..6,744.265 rows=15 loops=1)

4. 0.004 6,744.264 ↑ 28,583.8 15 1

Unique (cost=297,907.69..302,195.26 rows=428,757 width=20) (actual time=6,744.257..6,744.264 rows=15 loops=1)

5. 214.063 6,744.260 ↑ 28,583.8 15 1

Sort (cost=297,907.69..298,979.59 rows=428,757 width=20) (actual time=6,744.256..6,744.260 rows=15 loops=1)

  • Sort Key: posts.created_at DESC, posts.id, posts.updated_at
  • Sort Method: external merge Disk: 7016kB
6. 2,387.729 6,530.197 ↑ 2.0 210,805 1

Hash Right Join (cost=242,830.60..249,002.40 rows=428,757 width=20) (actual time=4,136.180..6,530.197 rows=210,805 loops=1)

  • Hash Cond: (share_visibilities.shareable_id = posts.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: 15053
7. 18.793 18.793 ↓ 1.0 87,683 1

Seq Scan on share_visibilities (cost=0.00..2,274.80 rows=87,575 width=14) (actual time=0.024..18.793 rows=87,683 loops=1)

  • Filter: ((shareable_type)::text = 'Post'::text)
  • Rows Removed by Filter: 32861
8. 554.759 4,123.675 ↑ 2.0 212,510 1

Hash (cost=234,840.87..234,840.87 rows=435,178 width=21) (actual time=4,123.675..4,123.675 rows=212,510 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 2001kB
9. 276.547 3,568.916 ↑ 2.0 212,510 1

Hash Join (cost=1,722.22..234,840.87 rows=435,178 width=21) (actual time=2.508..3,568.916 rows=212,510 loops=1)

  • Hash Cond: (posts.author_id = people.id)
10. 3,290.201 3,290.201 ↑ 1.0 3,515,896 1

Seq Scan on posts (cost=0.00..223,877.44 rows=3,515,896 width=25) (actual time=0.057..3,290.201 rows=3,515,896 loops=1)

  • Filter: (((type)::text = ANY ('{StatusMessage,Reshare}'::text[])) AND (created_at < '2018-12-29 20:20:37'::timestamp without time zone))
11. 0.034 2.168 ↑ 1.0 385 1

Hash (cost=1,717.17..1,717.17 rows=404 width=4) (actual time=2.168..2.168 rows=385 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
12. 0.036 2.134 ↑ 1.0 385 1

Unique (cost=1,711.11..1,713.13 rows=404 width=4) (actual time=2.088..2.134 rows=385 loops=1)

13. 0.097 2.098 ↑ 1.0 401 1

Sort (cost=1,711.11..1,712.12 rows=404 width=4) (actual time=2.088..2.098 rows=401 loops=1)

  • Sort Key: people.id
  • Sort Method: quicksort Memory: 43kB
14. 0.122 2.001 ↑ 1.0 401 1

Nested Loop (cost=94.20..1,693.62 rows=404 width=4) (actual time=0.214..2.001 rows=401 loops=1)

15. 0.469 1.077 ↑ 1.0 401 1

Hash Join (cost=93.91..253.14 rows=404 width=4) (actual time=0.207..1.077 rows=401 loops=1)

  • Hash Cond: (contacts.id = aspect_memberships.contact_id)
16. 0.417 0.417 ↑ 1.0 5,717 1

Seq Scan on contacts (cost=0.00..105.17 rows=5,717 width=8) (actual time=0.009..0.417 rows=5,717 loops=1)

17. 0.038 0.191 ↑ 1.0 401 1

Hash (cost=88.86..88.86 rows=404 width=4) (actual time=0.191..0.191 rows=401 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
18. 0.120 0.153 ↑ 1.0 401 1

Bitmap Heap Scan on aspect_memberships (cost=42.26..88.86 rows=404 width=4) (actual time=0.043..0.153 rows=401 loops=1)

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

Bitmap Index Scan on index_aspect_memberships_on_aspect_id_and_contact_id (cost=0.00..42.16 rows=404 width=0) (actual time=0.033..0.033 rows=401 loops=1)

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

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

  • Index Cond: (id = contacts.person_id)
  • Heap Fetches: 401
21. 0.001 4.997 ↑ 15.0 1 1

Limit (cost=1,465.40..1,465.55 rows=15 width=20) (actual time=4.997..4.997 rows=1 loops=1)

22. 0.002 4.996 ↑ 525.0 1 1

Unique (cost=1,465.40..1,470.65 rows=525 width=20) (actual time=4.996..4.996 rows=1 loops=1)

23. 1.221 4.994 ↑ 525.0 1 1

Sort (cost=1,465.40..1,466.72 rows=525 width=20) (actual time=4.994..4.994 rows=1 loops=1)

  • Sort Key: posts_1.created_at DESC, posts_1.id, posts_1.updated_at
  • Sort Method: quicksort Memory: 375kB
24. 3.773 3.773 ↓ 6.8 3,570 1

Index Scan using index_posts_on_person_id on posts posts_1 (cost=0.43..1,441.68 rows=525 width=20) (actual time=0.037..3.773 rows=3,570 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))