explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oi2a

Settings
# exclusive inclusive rows x rows loops node
1. 8,564.797 18,926.782 ↑ 13.5 763 1

HashAggregate (cost=64,193.98..64,297.04 rows=10,306 width=172) (actual time=18,926.261..18,926.782 rows=763 loops=1)

  • Group Key: a.id, a.created_at, a.updated_at, a.first_name, a.last_name, a.cover_name, a.genitive_full_name, a.photo, a.book_count, a.is_visible, a.litres_ext_id
2. 3,523.338 10,361.985 ↓ 460.1 5,291,426 1

Hash Join (cost=42,961.80..63,877.71 rows=11,501 width=172) (actual time=217.832..10,361.985 rows=5,291,426 loops=1)

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

Hash Join (cost=42,845.43..62,096.34 rows=413,330 width=176) (actual time=206.525..6,838.503 rows=18,217,829 loops=1)

4. 0.000 0.144 ↑ 1.0 130 1

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

  • Hash Cond: (t6.author_id = a.id)
  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
5. 169.327 169.327 ↓ 1.0 69,967 1

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

6. 0.099 0.099 ↑ 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.017..0.099 rows=130 loops=1)

7. 0.000 206.398 ↓ 1.1 35,346 1

Hash (cost=42,457.88..42,457.88 rows=30,893 width=176) (actual time=206.398..206.398 rows=35,346 loops=1)

  • Merge Cond: (t6.book_id = t7.id)
  • Index Cond: (niche_id = 29)
  • Buckets: 65536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 8454kB
8. 120.883 120.883 ↓ 1.1 69,967 1

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

9. 13.952 13.952 ↓ 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.011..13.952 rows=32,491 loops=1)

10. 0.000 158.465 ↓ 1.1 35,346 1

Hash Right Join (cost=13,470.82..42,457.88 rows=30,893 width=176) (actual time=72.964..158.465 rows=35,346 loops=1)

  • Heap Fetches: 9485
  • Hash Cond: (books_book_authors.author_id = a.id)
11. 96.443 96.443 ↓ 1.0 61,741 1

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

12. 125.076 125.076 ↓ 1.1 35,375 1

Hash Right Join (cost=12,528.98..41,086.64 rows=32,127 width=4) (actual time=60.172..125.076 rows=35,375 loops=1)

13. 0.000 12.766 ↑ 1.0 4,955 1

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

  • Merge Cond: (t7.id = books_book_genres.book_id)
  • Hash Cond: (segments_segmentbook.book_id = books_book.id)
  • Buckets: 8192 Batches: 1 Memory Usage: 1123kB
14. 45.299 45.299 ↓ 1.0 31,297 1

Seq Scan on segments_segmentbook (cost=0.00..28,149.12 rows=31,012 width=4) (actual time=0.007..45.299 rows=31,297 loops=1)

15. 38.147 38.147 ↑ 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.006..38.147 rows=28,909 loops=1)

16. 29.460 29.460 ↓ 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.010..29.460 rows=61,741 loops=1)

17. 8.970 8.970 ↑ 1.0 4,955 1

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

18. 3.081 60.074 ↓ 1.0 32,491 1

Hash (cost=12,127.40..12,127.40 rows=32,127 width=8) (actual time=60.074..60.074 rows=32,491 loops=1)

  • Heap Fetches: 22840
  • Heap Fetches: 10435
  • Hash Cond: (a.id = segments_segmentauthor.author_id)
  • Buckets: 32768 Batches: 1 Memory Usage: 1526kB
19. 1.772 1.772 ↓ 1.0 5,162 1

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

20. 51.321 51.321 ↓ 1.0 32,491 1

Merge Left Join (cost=0.70..12,127.40 rows=32,127 width=8) (actual time=0.017..51.321 rows=32,491 loops=1)

21. 0.000 3.900 ↑ 1.0 4,955 1

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

  • Merge Cond: (books_book_authors.book_id = books_book.id)
  • Buckets: 8192 Batches: 1 Memory Usage: 239kB
22. 2.225 2.225 ↑ 1.0 4,955 1

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

23. 20.780 20.780 ↑ 1.0 32,491 1

Index Only Scan using books_book_pkey on books_book (cost=0.41..12,393.17 rows=34,093 width=4) (actual time=0.005..20.780 rows=32,491 loops=1)

24. 9.964 9.964 ↓ 1.0 32,491 1

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

  • Rows Removed by Filter: 654
  • Heap Fetches: 9485
  • Heap Fetches: 12099
  • Filter: ((segment)::text = 'zvukislov'::text)