explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rAcF

Settings
# exclusive inclusive rows x rows loops node
1. 1.426 31,554.380 ↑ 1.0 50 1

Limit (cost=1,107,592.08..1,107,607.83 rows=50 width=4,140) (actual time=31,550.781..31,554.380 rows=50 loops=1)

2. 2.050 31,552.954 ↑ 1,492.0 50 1

Unique (cost=1,107,592.08..1,131,091.08 rows=74,600 width=4,140) (actual time=31,550.779..31,552.954 rows=50 loops=1)

3. 27,694.116 31,550.904 ↑ 753.5 99 1

Sort (cost=1,107,592.08..1,107,778.58 rows=74,600 width=4,140) (actual time=31,550.777..31,550.904 rows=99 loops=1)

  • Sort Key: books_book.is_readable_from DESC NULLS LAST, books_book.id DESC, books_book.seo_meta_title, books_book.seo_meta_description, books_book.seo_meta_canonical, books_book.seo_meta_robots, books_book.seo_og_title, books_book.seo_og_description, books_book.type, books_book.audio_type, books_book.name, books_book.lang, books_book.language_id, books_book.slug, books_book.annotation, books_book.annotation_short, books_book.description, books_book.search_aliases, books_book.first_impression_dt, books_book.written_dt, books_book.pro_to_std_dt, books_book.partner_planned_dt, books_book.activated_at, books_book.deactivated_at, books_book.created_at, books_book.updated_at, books_book.last_imported_at, books_book.main_author_id, books_book.main_actor_id, books_book.active, books_book.is_readable, books_book.is_readable_in_countries, books_book.is_readable_till, books_book.ean, books_book.default_cover, books_book.rightholder_id, books_book.copyright_type, books_book.adult, books_book.status, books_book.price, books_book.litres_id, books_book.litres_book_id, books_book.litres_file_id, books_book.is_protected, books_book.is_selfpub, books_book.is_synced, books_book.connected_book_id, books_book.seconds, books_book.bytes, books_book.weight, books_book.author_weight, books_book.editor_weight, books_book.cover_color, books_book.read_count, books_book.file_count, books_book.file_preview_count, books_book.corporate_id, books_book.user_id, books_book.subscription_id, books_book.search_trap, books_book.interesting_facts, books_book.rating_score, books_book.rating_count, books_book.review_rating, books_book.reviews_count, books_book.citations_count, books_book.overall_rating, books_book.daily_rating, books_book.weekly_rating, books_book.monthly_rating, books_book.total_rating, books_book.factors_rating, books_book.summary, books_book.summary_meta_title, books_book.summary_meta_description, books_book.planned_dt, authors_author.id, authors_author.cyrillic_ordering, authors_author.first_name, authors_author.last_name, authors_author.cover_name, authors_author.middle_name, authors_author.genitive_full_name, authors_author.localized_first_name, authors_author.localized_last_name, authors_author.localized_cover_name, authors_author.localized_genitive_full_name, authors_author.slug, authors_author.search_aliases, authors_author.counters, authors_author.country, authors_author.description, authors_author.description_url, authors_author.seo_description, authors_author.photo, authors_author.rating_score, authors_author.rating_votes, authors_author.read_count, authors_author.created_by_id, authors_author.weight, authors_author."order", authors_author.active, authors_author.active_book_count, authors_author.available_book_count, authors_author.reviews_count, authors_author.citations_count, authors_author.litres_id, authors_author.main_niche_id, authors_author.created_at, authors_author.updated_at, books_rightholder.id, books_rightholder.created, books_rightholder.modified, books_rightholder.name, books_rightholder.description, books_rightholder.photo, books_rightholder.slug, books_rightholder.is_author, books_rightholder.preview_pages, books_rightholder.book_count, books_rightholder.counters, books_rightholder.litres_id, payments_subscriptiontype.id, payments_subscriptiontype.type, payments_subscriptiontype.description
  • Sort Method: external merge Disk: 470,800kB
4. 326.193 3,856.788 ↓ 2.3 173,848 1

Nested Loop Left Join (cost=222,951.30..968,708.37 rows=74,600 width=4,140) (actual time=1,660.235..3,856.788 rows=173,848 loops=1)

  • Join Filter: (books_book.subscription_id = payments_subscriptiontype.id)
  • Rows Removed by Join Filter: 415,925
5. 40.242 3,530.595 ↓ 2.3 173,848 1

Gather (cost=222,951.30..964,767.29 rows=74,600 width=4,114) (actual time=1,660.201..3,530.595 rows=173,848 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 82.311 3,490.353 ↓ 1.9 57,949 3 / 3

Hash Left Join (cost=221,951.30..956,307.29 rows=31,083 width=4,114) (actual time=1,798.908..3,490.353 rows=57,949 loops=3)

  • Hash Cond: (books_book.rightholder_id = books_rightholder.id)
7. 882.458 3,344.567 ↓ 1.9 57,949 3 / 3

Hash Left Join (cost=214,419.39..948,693.78 rows=31,083 width=3,884) (actual time=1,734.892..3,344.567 rows=57,949 loops=3)

  • Hash Cond: (books_book.main_author_id = authors_author.id)
8. 87.029 1,519.726 ↓ 1.9 57,949 3 / 3

Hash Join (cost=93,683.82..767,656.61 rows=31,083 width=2,753) (actual time=791.792..1,519.726 rows=57,949 loops=3)

  • Hash Cond: (books_book.id = t4.book_id)
9. 102.886 1,058.396 ↑ 1.5 69,650 3 / 3

Hash Join (cost=75,424.66..744,331.29 rows=102,818 width=2,757) (actual time=415.971..1,058.396 rows=69,650 loops=3)

  • Hash Cond: (books_book.id = regions_regionbook.book_id)
10. 628.580 648.994 ↑ 2.2 69,651 3 / 3

Parallel Bitmap Heap Scan on books_book (cost=52,373.01..720,885.06 rows=150,315 width=2,753) (actual time=106.594..648.994 rows=69,651 loops=3)

  • Recheck Cond: (file_count > 0)
  • Filter: ((NOT is_selfpub) OR (is_selfpub IS NULL))
  • Rows Removed by Filter: 31,965
  • Heap Blocks: exact=48,244
11. 20.414 20.414 ↑ 1.4 311,413 1 / 3

Bitmap Index Scan on books_book_active_novelty_file_count_idx (cost=0.00..52,282.82 rows=442,148 width=0) (actual time=61.242..61.242 rows=311,413 loops=1)

12. 142.168 306.516 ↓ 1.0 496,613 3 / 3

Hash (cost=16,849.08..16,849.08 rows=496,206 width=4) (actual time=306.516..306.516 rows=496,613 loops=3)

  • Buckets: 524,288 Batches: 1 Memory Usage: 21,556kB
13. 164.348 164.348 ↓ 1.0 496,613 3 / 3

Seq Scan on regions_regionbook (cost=0.00..16,849.08 rows=496,206 width=4) (actual time=0.023..164.348 rows=496,613 loops=3)

  • Filter: (region_id = 1)
  • Rows Removed by Filter: 36,205
14. 82.058 374.301 ↓ 1.2 264,076 3 / 3

Hash (cost=15,517.86..15,517.86 rows=219,304 width=4) (actual time=374.301..374.301 rows=264,076 loops=3)

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 13,380kB
15. 292.243 292.243 ↓ 1.2 264,076 3 / 3

Seq Scan on regions_regionbook t4 (cost=0.00..15,517.86 rows=219,304 width=4) (actual time=0.035..292.243 rows=264,076 loops=3)

  • Filter: (is_active AND is_readable)
  • Rows Removed by Filter: 268,742
16. 506.587 942.383 ↑ 1.4 200,107 3 / 3

Hash (cost=78,192.70..78,192.70 rows=276,070 width=1,131) (actual time=942.383..942.383 rows=200,107 loops=3)

  • Buckets: 65,536 Batches: 8 Memory Usage: 13,559kB
17. 435.796 435.796 ↑ 1.4 200,107 3 / 3

Seq Scan on authors_author (cost=0.00..78,192.70 rows=276,070 width=1,131) (actual time=0.049..435.796 rows=200,107 loops=3)

18. 23.008 63.475 ↑ 1.8 27,430 3 / 3

Hash (cost=6,908.07..6,908.07 rows=49,907 width=230) (actual time=63.475..63.475 rows=27,430 loops=3)

  • Buckets: 65,536 Batches: 1 Memory Usage: 7,729kB
19. 40.467 40.467 ↑ 1.8 27,430 3 / 3

Seq Scan on books_rightholder (cost=0.00..6,908.07 rows=49,907 width=230) (actual time=0.047..40.467 rows=27,430 loops=3)

20. 0.000 0.000 ↑ 1.3 3 173,848

Materialize (cost=0.00..1.06 rows=4 width=26) (actual time=0.000..0.000 rows=3 loops=173,848)

21. 0.019 0.019 ↑ 1.0 4 1

Seq Scan on payments_subscriptiontype (cost=0.00..1.04 rows=4 width=26) (actual time=0.018..0.019 rows=4 loops=1)

Planning time : 22.917 ms
Execution time : 31,566.896 ms