explain.depesz.com

PostgreSQL's explain analyze made readable

Result: N8PrU

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 33,544.817 ↑ 1.0 20 1

Limit (cost=104,841.52..104,841.57 rows=20 width=194) (actual time=33,544.814..33,544.817 rows=20 loops=1)

2. 0.126 33,544.814 ↑ 257.6 20 1

Sort (cost=104,841.52..104,854.40 rows=5,153 width=194) (actual time=33,544.813..33,544.814 rows=20 loops=1)

  • Sort Key: (avg(CASE WHEN (books_book.is_active AND ((segments_segmentbook.segment)::text = 'zvukislov'::text)) THEN books_book.rating ELSE NULL::double precision END)) DESC
  • Sort Method: top-N heapsort Memory: 31kB
3. 0.535 33,544.688 ↑ 37.3 138 1

HashAggregate (cost=104,652.87..104,704.40 rows=5,153 width=194) (actual time=33,544.585..33,544.688 rows=138 loops=1)

  • Group Key: avg(CASE WHEN (books_book.is_active AND ((segments_segmentbook.segment)::text = 'zvukislov'::text)) THEN books_book.rating ELSE NULL::double precision END), authors_author.id, authors_author.created_at, authors_author.updated_at, authors_author.first_name, authors_author.last_name, authors_author.cover_name, authors_author.genitive_full_name, authors_author.photo, authors_author.book_count, authors_author.is_visible, authors_author.litres_ext_id, count(DISTINCT CASE WHEN (books_book.is_active AND ((segments_segmentbook.segment)::text = 'zvukislov'::text)) THEN books_book_authors.book_id ELSE NULL::integer END)
4. 9,078.353 33,544.153 ↑ 37.3 138 1

GroupAggregate (cost=104,107.69..104,485.40 rows=5,153 width=194) (actual time=19,336.427..33,544.153 rows=138 loops=1)

  • Group Key: authors_author.id
  • Filter: ((count(DISTINCT CASE WHEN (books_book.is_active AND ((segments_segmentbook.segment)::text = 'zvukislov'::text)) THEN books_book_authors.book_id ELSE NULL::integer END) >= 2) AND (avg(CASE WHEN (books_book.is_active AND ((segments_segmentbook.segment)::text = 'zvukislov'::text)) THEN books_book.rating ELSE NULL::double precision END) IS NOT NULL))
  • Rows Removed by Filter: 625
5. 12,732.694 24,465.800 ↓ 460.1 5,291,426 1

Sort (cost=104,107.69..104,136.45 rows=11,501 width=194) (actual time=19,335.996..24,465.800 rows=5,291,426 loops=1)

  • Sort Key: authors_author.id
  • Sort Method: external merge Disk: 723024kB
6. 3,777.240 11,733.106 ↓ 460.1 5,291,426 1

Hash Join (cost=68,373.65..103,331.98 rows=11,501 width=194) (actual time=584.645..11,733.106 rows=5,291,426 loops=1)

  • Hash Cond: (books_book_genres.genre_id = catalog_genre.id)
7. 7,230.797 7,955.654 ↓ 44.1 18,217,829 1

Hash Right Join (cost=68,257.28..101,550.62 rows=413,330 width=198) (actual time=583.479..7,955.654 rows=18,217,829 loops=1)

  • Hash Cond: (books_book_authors.author_id = authors_author.id)
8. 44.732 455.201 ↓ 1.1 35,375 1

Hash Right Join (cost=51,135.70..79,693.35 rows=32,127 width=26) (actual time=313.492..455.201 rows=35,375 loops=1)

  • Hash Cond: (segments_segmentbook.book_id = books_book.id)
9. 97.233 97.233 ↓ 1.0 31,297 1

Seq Scan on segments_segmentbook (cost=0.00..28,149.12 rows=31,012 width=13) (actual time=0.013..97.233 rows=31,297 loops=1)

10. 14.644 313.236 ↓ 1.0 32,491 1

Hash (cost=50,734.11..50,734.11 rows=32,127 width=21) (actual time=313.236..313.236 rows=32,491 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2160kB
11. 23.652 298.592 ↓ 1.0 32,491 1

Hash Left Join (cost=49,755.09..50,734.11 rows=32,127 width=21) (actual time=270.500..298.592 rows=32,491 loops=1)

  • Hash Cond: (books_book_authors.book_id = books_book.id)
12. 4.905 4.905 ↓ 1.0 32,491 1

Seq Scan on books_book_authors (cost=0.00..537.27 rows=32,127 width=8) (actual time=0.006..4.905 rows=32,491 loops=1)

13. 13.819 270.035 ↑ 1.2 28,909 1

Hash (cost=49,328.93..49,328.93 rows=34,093 width=13) (actual time=270.035..270.035 rows=28,909 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 1981kB
14. 256.216 256.216 ↑ 1.2 28,909 1

Seq Scan on books_book (cost=0.00..49,328.93 rows=34,093 width=13) (actual time=0.013..256.216 rows=28,909 loops=1)

15. 66.218 269.656 ↓ 1.1 69,930 1

Hash (cost=16,292.89..16,292.89 rows=66,296 width=176) (actual time=269.656..269.656 rows=69,930 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 16261kB
16. 42.064 203.438 ↓ 1.1 69,930 1

Hash Join (cost=943.22..16,292.89 rows=66,296 width=176) (actual time=13.767..203.438 rows=69,930 loops=1)

  • Hash Cond: (t6.author_id = authors_author.id)
17. 32.331 147.698 ↓ 1.0 69,967 1

Merge Join (cost=1.39..14,429.56 rows=68,944 width=8) (actual time=0.058..147.698 rows=69,967 loops=1)

  • Merge Cond: (t6.book_id = t7.id)
18. 12.928 12.928 ↓ 1.0 32,491 1

Index Only Scan using books_book_authors_book_id_author_id_key on books_book_authors t6 (cost=0.29..918.19 rows=32,127 width=8) (actual time=0.015..12.928 rows=32,491 loops=1)

  • Heap Fetches: 9480
19. 22.230 102.439 ↓ 1.1 69,967 1

Materialize (cost=1.07..14,667.26 rows=61,012 width=12) (actual time=0.040..102.439 rows=69,967 loops=1)

20. 26.032 80.209 ↓ 1.0 61,741 1

Merge Join (cost=1.07..14,514.73 rows=61,012 width=12) (actual time=0.036..80.209 rows=61,741 loops=1)

  • Merge Cond: (t7.id = books_book_genres.book_id)
21. 28.497 28.497 ↑ 1.2 28,909 1

Index Only Scan using books_book_pkey on books_book t7 (cost=0.41..12,393.17 rows=34,093 width=4) (actual time=0.009..28.497 rows=28,909 loops=1)

  • Heap Fetches: 8159
22. 25.680 25.680 ↓ 1.0 61,741 1

Index Only Scan using books_book_genres_book_id_genre_id_key on books_book_genres (cost=0.29..1,775.47 rows=61,012 width=8) (actual time=0.023..25.680 rows=61,741 loops=1)

  • Heap Fetches: 22813
23. 3.863 13.676 ↑ 1.0 4,955 1

Hash (cost=879.89..879.89 rows=4,955 width=176) (actual time=13.676..13.676 rows=4,955 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 1123kB
24. 3.782 9.813 ↑ 1.0 4,955 1

Hash Join (cost=237.05..879.89 rows=4,955 width=176) (actual time=4.450..9.813 rows=4,955 loops=1)

  • Hash Cond: (authors_author.id = segments_segmentauthor.author_id)
25. 1.611 1.611 ↓ 1.0 5,162 1

Seq Scan on authors_author (cost=0.00..567.53 rows=5,153 width=172) (actual time=0.004..1.611 rows=5,162 loops=1)

26. 1.600 4.420 ↑ 1.0 4,955 1

Hash (cost=175.11..175.11 rows=4,955 width=4) (actual time=4.420..4.420 rows=4,955 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 239kB
27. 2.820 2.820 ↑ 1.0 4,955 1

Seq Scan on segments_segmentauthor (cost=0.00..175.11 rows=4,955 width=4) (actual time=0.196..2.820 rows=4,955 loops=1)

  • Filter: ((segment)::text = 'zvukislov'::text)
  • Rows Removed by Filter: 654
28. 0.044 0.212 ↑ 1.0 130 1

Hash (cost=114.74..114.74 rows=130 width=4) (actual time=0.212..0.212 rows=130 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
29. 0.168 0.168 ↑ 1.0 130 1

Index Scan using catalog_genre_25420ed4 on catalog_genre (cost=0.41..114.74 rows=130 width=4) (actual time=0.020..0.168 rows=130 loops=1)

  • Index Cond: (niche_id = 29)