explain.depesz.com

PostgreSQL's explain analyze made readable

Result: T44H

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 58,423.371 ↑ 1.0 20 1

Limit (cost=3,950,671.77..3,950,671.82 rows=20 width=1,353) (actual time=58,423.365..58,423.371 rows=20 loops=1)

2. 226.570 58,423.368 ↑ 10,911.1 20 1

Sort (cost=3,950,671.77..3,951,217.33 rows=218,223 width=1,353) (actual time=58,423.363..58,423.368 rows=20 loops=1)

  • Sort Key: reviews_review.rating_votes DESC, (CASE WHEN (reviews_review.user_id = 7963) THEN 1 ELSE 0 END) DESC
  • Sort Method: top-N heapsort Memory: 73kB
3. 110.567 58,196.798 ↑ 2.4 90,514 1

Hash Left Join (cost=2,556,377.87..3,944,864.94 rows=218,223 width=1,353) (actual time=33,559.558..58,196.798 rows=90,514 loops=1)

  • Hash Cond: (books_book.main_author_id = authors_author.id)
4. 1,291.833 57,871.798 ↑ 2.4 90,514 1

Hash Join (cost=2,545,336.44..3,930,303.21 rows=218,223 width=1,223) (actual time=33,344.356..57,871.798 rows=90,514 loops=1)

  • Hash Cond: (reviews_review.book_id = books_book.id)
5. 10,160.888 51,588.873 ↑ 2.4 90,514 1

Hash Right Join (cost=1,268,995.97..2,356,109.62 rows=218,223 width=382) (actual time=28,352.828..51,588.873 rows=90,514 loops=1)

  • Hash Cond: (account_userprofile.user_id = auth_user.id)
6. 13,079.385 13,079.385 ↑ 1.0 13,435,680 1

Seq Scan on account_userprofile (cost=0.00..579,935.49 rows=13,446,649 width=105) (actual time=0.032..13,079.385 rows=13,435,680 loops=1)

7. 207.219 28,348.600 ↑ 2.4 90,514 1

Hash (cost=1,258,169.18..1,258,169.18 rows=218,223 width=277) (actual time=28,348.600..28,348.600 rows=90,514 loops=1)

  • Buckets: 262144 Batches: 2 Memory Usage: 27806kB
8. 6,467.062 28,141.381 ↑ 2.4 90,514 1

Hash Join (cost=881,841.88..1,258,169.18 rows=218,223 width=277) (actual time=21,171.490..28,141.381 rows=90,514 loops=1)

  • Hash Cond: (reviews_review.user_id = auth_user.id)
9. 536.521 612.067 ↑ 2.4 90,514 1

Bitmap Heap Scan on reviews_review (cost=13,023.66..101,029.05 rows=218,223 width=136) (actual time=102.584..612.067 rows=90,514 loops=1)

  • Recheck Cond: ((show AND ((comment <> ''::text) OR (comment IS NULL))) OR (user_id = 7963))
  • Filter: ((partner_id IS NULL) AND (comment IS NOT NULL) AND ((NOT is_livelib) OR (is_livelib IS NULL)) AND (comment <> ''::text))
  • Rows Removed by Filter: 95415
  • Heap Blocks: exact=69280
10. 0.003 75.546 ↓ 0.0 0 1

BitmapOr (cost=13,023.66..13,023.66 rows=222,226 width=0) (actual time=75.546..75.546 rows=0 loops=1)

11. 75.517 75.517 ↑ 1.0 215,717 1

Bitmap Index Scan on reviews_review_rating_votes_book_id_comment_idx (cost=0.00..12,909.75 rows=222,177 width=0) (actual time=75.517..75.517 rows=215,717 loops=1)

12. 0.026 0.026 ↓ 0.0 0 1

Bitmap Index Scan on reviews_rating_votes_user_last (cost=0.00..4.79 rows=48 width=0) (actual time=0.026..0.026 rows=0 loops=1)

  • Index Cond: (user_id = 7963)
13. 14,570.181 21,062.252 ↓ 1.0 13,435,680 1

Hash (cost=428,578.32..428,578.32 rows=13,337,432 width=141) (actual time=21,062.252..21,062.252 rows=13,435,680 loops=1)

  • Buckets: 524288 Batches: 64 Memory Usage: 41034kB
14. 6,492.071 6,492.071 ↓ 1.0 13,435,680 1

Seq Scan on auth_user (cost=0.00..428,578.32 rows=13,337,432 width=141) (actual time=0.020..6,492.071 rows=13,435,680 loops=1)

15. 1,029.585 4,991.092 ↑ 8.2 313,240 1

Hash (cost=971,763.43..971,763.43 rows=2,560,643 width=841) (actual time=4,991.092..4,991.092 rows=313,240 loops=1)

  • Buckets: 131072 Batches: 64 Memory Usage: 5397kB
16. 3,961.507 3,961.507 ↑ 8.2 313,240 1

Seq Scan on books_book (cost=0.00..971,763.43 rows=2,560,643 width=841) (actual time=0.009..3,961.507 rows=313,240 loops=1)

17. 91.760 214.433 ↓ 1.0 103,552 1

Hash (cost=9,748.97..9,748.97 rows=103,397 width=134) (actual time=214.433..214.433 rows=103,552 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 18188kB
18. 122.673 122.673 ↓ 1.0 103,552 1

Seq Scan on authors_author (cost=0.00..9,748.97 rows=103,397 width=134) (actual time=0.017..122.673 rows=103,552 loops=1)