explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A26K

Settings
# exclusive inclusive rows x rows loops node
1. 5.255 40,672.850 ↓ 1,378.0 1,378 1

Sort (cost=946.65..946.65 rows=1 width=1,784) (actual time=40,672.765..40,672.850 rows=1,378 loops=1)

  • Sort Key: candidate_links.article_date DESC, candidate_links.article_id DESC, candidate_links.journal_assignment_package_id DESC, (CASE WHEN (candidate_lin
  • Sort Method: quicksort Memory: 1419kB
  • _id = pj.platform_id) OR ((al.content_platform_id = 0) AND (p.url_transform_rule_id IS NOT NULL)))
2. 0.977 40,667.595 ↓ 1,378.0 1,378 1

Subquery Scan on candidate_links (cost=946.64..946.65 rows=1 width=1,784) (actual time=40,666.527..40,667.595 rows=1,378 loops=1)

  • Filter: (candidate_links.article_direct_link_usable OR (candidate_links.article_url_platform_id <> 0) OR (candidate_links.article_applicable_url_tran
3. 4.117 40,666.618 ↓ 1,378.0 1,378 1

Sort (cost=946.64..946.64 rows=1 width=1,784) (actual time=40,666.522..40,666.618 rows=1,378 loops=1)

  • Sort Key: a.date, a.id
  • Sort Method: quicksort Memory: 1419kB
4.          

Initplan (for Sort)

5. 0.015 0.015 ↑ 1.0 1 1

Index Scan using libraries_pkey on libraries (cost=0.06..4.06 rows=1 width=4) (actual time=0.012..0.015 rows=1 loops=1)

  • Index Cond: (id = 222)
6. 0.074 0.074 ↑ 1.0 1 1

Index Scan using libraries_pkey on libraries libraries_1 (cost=0.06..4.06 rows=1 width=1) (actual time=0.053..0.074 rows=1 loops=1)

  • Index Cond: (id = 222)
7. 11.477 40,662.412 ↓ 1,378.0 1,378 1

WindowAgg (cost=938.46..938.52 rows=1 width=1,784) (actual time=40,654.157..40,662.412 rows=1,378 loops=1)

8. 2.167 40,650.935 ↓ 1,378.0 1,378 1

Sort (cost=938.46..938.46 rows=1 width=1,717) (actual time=40,650.813..40,650.935 rows=1,378 loops=1)

  • Sort Key: a.date DESC, a.id DESC
  • Sort Method: quicksort Memory: 1359kB
9. 1.601 40,648.768 ↓ 1,378.0 1,378 1

WindowAgg (cost=938.45..938.46 rows=1 width=1,717) (actual time=40,647.085..40,648.768 rows=1,378 loops=1)

10. 8.120 40,647.167 ↓ 1,378.0 1,378 1

Sort (cost=938.45..938.45 rows=1 width=1,709) (actual time=40,647.075..40,647.167 rows=1,378 loops=1)

  • Sort Key: i.id
  • Sort Method: quicksort Memory: 1307kB
11. 2.682 40,639.047 ↓ 1,378.0 1,378 1

Result (cost=557.88..938.45 rows=1 width=1,709) (actual time=13.531..40,639.047 rows=1,378 loops=1)

  • One-Time Filter: $3
12. 6.230 40,636.365 ↓ 1,378.0 1,378 1

Nested Loop Left Join (cost=557.88..938.45 rows=1 width=1,709) (actual time=13.452..40,636.365 rows=1,378 loops=1)

  • Join Filter: (al.content_platform_id = 0)
  • Filter: (((al.content_platform_id = 0) AND ((j.proxy_required)::text = 'no'::text)) OR (al.content_platform
13. 4.539 40,628.757 ↓ 1,378.0 1,378 1

Nested Loop Left Join (cost=557.85..938.41 rows=1 width=1,705) (actual time=13.444..40,628.757 rows=1,378 loops=1)

14. 5.965 40,620.084 ↓ 1,378.0 1,378 1

Nested Loop Left Join (cost=557.83..938.38 rows=1 width=1,701) (actual time=13.438..40,620.084 rows=1,378 loops=1)

15. 7.320 40,601.717 ↓ 1,378.0 1,378 1

Nested Loop (cost=557.74..938.28 rows=1 width=1,629) (actual time=13.419..40,601.717 rows=1,378 loops=1)

  • Join Filter: ((al.content_platform_id = pj.platform_id) OR (al.content_platform_id = 0))
16. 5.933 40,572.349 ↓ 1,378.0 1,378 1

Nested Loop (cost=557.63..932.87 rows=1 width=1,561) (actual time=13.370..40,572.349 rows=1,378 loops=1)

17. 4.888 40,551.258 ↓ 1,378.0 1,378 1

Nested Loop (cost=557.51..928.75 rows=1 width=1,540) (actual time=13.327..40,551.258 rows=1,378 loops=1)

18. 149.980 40,525.700 ↓ 1,378.0 1,378 1

Nested Loop (cost=557.40..924.63 rows=1 width=1,623) (actual time=13.268..40,525.700 rows=1,378 loops=1)

19. 2.335 65.086 ↓ 1,378.0 1,378 1

Nested Loop (cost=557.32..920.38 rows=1 width=1,500) (actual time=11.061..65.086 rows=1,378 loops=1)

20. 0.088 10.899 ↑ 1.0 1 1

Bitmap Heap Scan on issues i (cost=557.20..563.20 rows=1 width=43) (actual time=10.881..10.899 rows=1 loops=1)

  • Recheck Cond: ((((volume)::text = ''::text) AND (journal_id = 274)) OR (volume IS NULL))
  • Filter: ((suppressed IS NULL) AND (((issue_number)::text = ''::text) OR (issue_number IS NULL)) AND (journal_id = 274))
  • Heap Blocks: exact=10
21. 0.001 10.811 ↓ 0.0 0 1

BitmapOr (cost=557.20..557.20 rows=3 width=0) (actual time=10.811..10.811 rows=0 loops=1)

22. 10.789 10.789 ↓ 9.0 27 1

Bitmap Index Scan on unique_volume_issue_number (cost=0.00..555.11 rows=3 width=0) (actual time=10.788..10.789 rows=27 loops=1)

  • Index Cond: (((volume)::text = ''::text) AND (journal_id = 274))
23. 0.021 0.021 ↓ 0.0 0 1

Bitmap Index Scan on issue_volume_btree (cost=0.00..2.09 rows=1 width=0) (actual time=0.021..0.021 rows=0 loops=1)

  • Index Cond: (volume IS NULL)
24. 51.852 51.852 ↓ 172.2 1,378 1

Index Scan using articles_issue_id on articles a (cost=0.11..357.16 rows=8 width=1,461) (actual time=0.176..51.852 rows=1,378 loops=1)

  • Index Cond: (issue_id = i.id)
  • Filter: ((suppressed IS NULL) AND (date > (now() - '1 year'::interval)))
  • Rows Removed by Filter: 8655
25. 40,310.634 40,310.634 ↑ 1.0 1 1,378

Index Scan using journals_pkey on journals j (cost=0.09..4.09 rows=1 width=86) (actual time=0.028..29.253 rows=1 loops=1,378)

  • Index Cond: (id = 274)
26. 20.670 20.670 ↑ 1.0 1 1,378

Index Scan using all_journals_by_library_library_journal_idx on all_journals_by_library ajbl (cost=0.11..4.12 rows=1 width=31) (actual time=0.013..0.015 rows=1 loops=1,378)

  • Index Cond: ((library_id = 222) AND (journal_id = 274))
27. 15.158 15.158 ↑ 1.0 1 1,378

Index Scan using package_journals_pkey on package_journals pj (cost=0.11..4.12 rows=1 width=29) (actual time=0.011..0.011 rows=1 loops=1,378)

  • Index Cond: (id = ajbl.package_journal_id)
  • Filter: (end_date >= now())
28. 22.048 22.048 ↑ 1.0 1 1,378

Index Scan using article_links_article_id_btree on article_links al (cost=0.11..5.41 rows=1 width=68) (actual time=0.016..0.016 rows=1 loops=1,378)

  • Index Cond: (article_id = a.id)
  • Filter: ((content_platform_id = 0) OR (alternatives: SubPlan 2 or hashed SubPlan 3))
29.          

SubPlan (for Index Scan)

30. 0.000 0.000 ↓ 0.0 0

Seq Scan on platforms (cost=0.00..1.29 rows=1 width=0) (never executed)

  • Filter: (enabled AND (id = al.content_platform_id))
31. 0.000 0.000 ↓ 0.0 0

Seq Scan on platforms platforms_1 (cost=0.00..1.25 rows=77 width=4) (never executed)

  • Filter: enabled
32. 12.402 12.402 ↓ 0.0 0 1,378

Index Scan using unique_article_and_platform on article_pdf_links apl (cost=0.09..0.10 rows=1 width=84) (actual time=0.009..0.009 rows=0 loops=1,378)

  • Index Cond: ((al.article_id = article_id) AND (al.content_platform_id = platform_id))
  • Filter: (NOT deleted)
33. 4.134 4.134 ↓ 0.0 0 1,378

Index Scan using platforms_id_btree on platforms p2 (cost=0.03..0.03 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=1,378)

  • Index Cond: (id = pj.platform_id)
  • Filter: (enabled AND (url_transform_rule_id IS NOT NULL) AND (url_transform_rule_id <> 0))
34. 1.378 1.378 ↓ 0.0 0 1,378

Index Scan using platforms_id_btree on platforms p (cost=0.03..0.03 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1,378)

  • Index Cond: (id = pj.platform_id)
  • Filter: (enabled AND (url_transform_rule_id IS NOT NULL) AND (url_transform_rule_id <> 0))
Planning time : 13.371 ms
Execution time : 40,675.729 ms