explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Glwm : Optimization for: Optimization for: plan #VPlf; plan #Q9Ba

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.005 72.341 ↑ 1.0 15 1

Limit (cost=56,322.85..56,323.26 rows=15 width=20) (actual time=72.331..72.341 rows=15 loops=1)

2. 0.003 72.336 ↑ 1.2 15 1

Merge Append (cost=56,322.85..56,323.34 rows=18 width=20) (actual time=72.330..72.336 rows=15 loops=1)

  • Sort Key: posts.created_at DESC
3. 0.000 71.851 ↑ 1.0 15 1

Limit (cost=55,925.95..55,926.10 rows=15 width=20) (actual time=71.847..71.851 rows=15 loops=1)

4. 0.006 71.851 ↑ 167.7 15 1

Unique (cost=55,925.95..55,951.10 rows=2,515 width=20) (actual time=71.846..71.851 rows=15 loops=1)

5. 0.922 71.845 ↑ 167.7 15 1

Sort (cost=55,925.95..55,932.24 rows=2,515 width=20) (actual time=71.845..71.845 rows=15 loops=1)

  • Sort Key: posts.created_at DESC, posts.id, posts.updated_at
  • Sort Method: quicksort Memory: 379kB
6. 1.208 70.923 ↓ 1.4 3,617 1

Nested Loop Left Join (cost=2,341.51..55,783.90 rows=2,515 width=20) (actual time=13.287..70.923 rows=3,617 loops=1)

  • 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: 433
7. 10.042 66.093 ↓ 1.4 3,622 1

Hash Join (cost=2,341.09..53,899.14 rows=2,553 width=21) (actual time=13.278..66.093 rows=3,622 loops=1)

  • Hash Cond: (posts.author_id = people.id)
8. 43.964 53.860 ↓ 9.7 169,805 1

Bitmap Heap Scan on posts (cost=376.61..51,888.46 rows=17,579 width=25) (actual time=11.025..53.860 rows=169,805 loops=1)

  • Recheck Cond: ((created_at >= '2018-11-29 20:20:37'::timestamp without time zone) AND (created_at <= '2018-12-29 20:20:37'::timestamp without time zone))
  • Filter: ((type)::text = ANY ('{StatusMessage,Reshare}'::text[]))
  • Heap Blocks: exact=9979
9. 9.896 9.896 ↓ 9.7 169,805 1

Bitmap Index Scan on index_posts_on_created_at (cost=0.00..372.22 rows=17,579 width=0) (actual time=9.895..9.896 rows=169,805 loops=1)

  • Index Cond: ((created_at >= '2018-11-29 20:20:37'::timestamp without time zone) AND (created_at <= '2018-12-29 20:20:37'::timestamp without time zone))
10. 0.039 2.191 ↑ 1.1 441 1

Hash (cost=1,958.55..1,958.55 rows=474 width=4) (actual time=2.191..2.191 rows=441 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
11. 0.159 2.152 ↑ 1.1 441 1

HashAggregate (cost=1,949.07..1,953.81 rows=474 width=4) (actual time=2.110..2.152 rows=441 loops=1)

  • Group Key: people.id
12. 0.080 1.993 ↑ 1.0 471 1

Nested Loop (cost=98.18..1,947.89 rows=474 width=4) (actual time=0.232..1.993 rows=471 loops=1)

13. 0.409 0.971 ↑ 1.0 471 1

Hash Join (cost=97.88..257.82 rows=474 width=4) (actual time=0.222..0.971 rows=471 loops=1)

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

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

15. 0.041 0.202 ↑ 1.0 471 1

Hash (cost=91.96..91.96 rows=474 width=4) (actual time=0.202..0.202 rows=471 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
16. 0.126 0.161 ↑ 1.0 471 1

Bitmap Heap Scan on aspect_memberships (cost=43.11..91.96 rows=474 width=4) (actual time=0.042..0.161 rows=471 loops=1)

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

Bitmap Index Scan on index_aspect_memberships_on_aspect_id_and_contact_id (cost=0.00..42.99 rows=474 width=0) (actual time=0.035..0.035 rows=471 loops=1)

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

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=471)

  • Index Cond: (id = contacts.person_id)
  • Heap Fetches: 471
19. 3.622 3.622 ↓ 0.0 0 3,622

Index Scan using index_post_visibilities_on_post_id on share_visibilities (cost=0.42..0.71 rows=2 width=14) (actual time=0.001..0.001 rows=0 loops=3,622)

  • Index Cond: (shareable_id = posts.id)
  • Filter: ((shareable_type)::text = 'Post'::text)
20. 0.000 0.482 ↓ 0.0 0 1

Limit (cost=396.89..396.92 rows=3 width=20) (actual time=0.482..0.482 rows=0 loops=1)

21. 0.000 0.482 ↓ 0.0 0 1

Unique (cost=396.89..396.92 rows=3 width=20) (actual time=0.482..0.482 rows=0 loops=1)

22. 0.013 0.482 ↓ 0.0 0 1

Sort (cost=396.89..396.89 rows=3 width=20) (actual time=0.482..0.482 rows=0 loops=1)

  • Sort Key: posts_1.created_at DESC, posts_1.id, posts_1.updated_at
  • Sort Method: quicksort Memory: 25kB
23. 0.006 0.469 ↓ 0.0 0 1

Bitmap Heap Scan on posts posts_1 (cost=384.84..396.86 rows=3 width=20) (actual time=0.469..0.469 rows=0 loops=1)

  • Recheck Cond: ((author_id = 1) AND (created_at >= '2018-12-29 20:20:37'::timestamp without time zone) AND (created_at <= '2018-12-29 20:20:37'::timestamp without time zone))
  • Filter: ((type)::text = ANY ('{StatusMessage,Reshare}'::text[]))
24. 0.067 0.463 ↓ 0.0 0 1

BitmapAnd (cost=384.84..384.84 rows=3 width=0) (actual time=0.463..0.463 rows=0 loops=1)

25. 0.390 0.390 ↓ 6.8 3,570 1

Bitmap Index Scan on index_posts_on_person_id (cost=0.00..12.37 rows=525 width=0) (actual time=0.390..0.390 rows=3,570 loops=1)

  • Index Cond: (author_id = 1)
26. 0.006 0.006 ↓ 0.0 0 1

Bitmap Index Scan on index_posts_on_created_at (cost=0.00..372.22 rows=17,579 width=0) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: ((created_at >= '2018-12-29 20:20:37'::timestamp without time zone) AND (created_at <= '2018-12-29 20:20:37'::timestamp without time zone))