explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZLsR

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 32,920.080 ↑ 1.0 20 1

Limit (cost=104,652.87..104,653.07 rows=20 width=194) (actual time=32,920.061..32,920.080 rows=20 loops=1)

2. 0.419 32,920.074 ↑ 257.6 20 1

HashAggregate (cost=104,652.87..104,704.40 rows=5,153 width=194) (actual time=32,920.059..32,920.074 rows=20 loops=1)

  • Group Key: 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, avg(CASE WHEN (books_book.is_active AND ((segments_segmentbook.segment)::text = 'zvukislov'::text)) THEN books_book.rating ELSE NULL::double precision END), 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)
3. 9,018.390 32,919.655 ↑ 37.3 138 1

GroupAggregate (cost=104,107.69..104,485.40 rows=5,153 width=194) (actual time=18,921.850..32,919.655 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
4. 12,524.688 23,901.265 ↓ 460.1 5,291,426 1

Sort (cost=104,107.69..104,136.45 rows=11,501 width=194) (actual time=18,921.439..23,901.265 rows=5,291,426 loops=1)

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

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

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

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

  • Hash Cond: (books_book_authors.author_id = authors_author.id)
7. 42.592 226.543 ↓ 1.1 35,375 1

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

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

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

9. 14.852 136.009 ↓ 1.0 32,491 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 2160kB
10. 22.568 121.157 ↓ 1.0 32,491 1

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

  • Hash Cond: (books_book_authors.book_id = books_book.id)
11. 4.814 4.814 ↓ 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.814 rows=32,491 loops=1)

12. 12.730 93.775 ↑ 1.2 28,909 1

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

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

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

14. 65.047 256.864 ↓ 1.1 69,930 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 16261kB
15. 41.351 191.817 ↓ 1.1 69,930 1

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

  • Hash Cond: (t6.author_id = authors_author.id)
16. 32.008 137.641 ↓ 1.0 69,967 1

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

  • Merge Cond: (t6.book_id = t7.id)
17. 11.821 11.821 ↓ 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.014..11.821 rows=32,491 loops=1)

  • Heap Fetches: 9480
18. 22.954 93.812 ↓ 1.1 69,967 1

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

19. 25.613 70.858 ↓ 1.0 61,741 1

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

  • Merge Cond: (t7.id = books_book_genres.book_id)
20. 21.091 21.091 ↑ 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.007..21.091 rows=28,909 loops=1)

  • Heap Fetches: 8261
21. 24.154 24.154 ↓ 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.012..24.154 rows=61,741 loops=1)

  • Heap Fetches: 22813
22. 3.774 12.825 ↑ 1.0 4,955 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 1123kB
23. 3.730 9.051 ↑ 1.0 4,955 1

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

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

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

25. 1.578 3.775 ↑ 1.0 4,955 1

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

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

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

  • Filter: ((segment)::text = 'zvukislov'::text)
  • Rows Removed by Filter: 654
27. 0.047 0.150 ↑ 1.0 130 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
28. 0.103 0.103 ↑ 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.019..0.103 rows=130 loops=1)

  • Index Cond: (niche_id = 29)