explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JdNw

Settings
# exclusive inclusive rows x rows loops node
1. 5.308 123.817 ↓ 1,373.0 1,373 1

Sort (cost=826.15..826.16 rows=1 width=1,803) (actual time=123.740..123.817 rows=1,373 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: 1356kB
2. 1.125 118.509 ↓ 1,373.0 1,373 1

Subquery Scan on candidate_links (cost=826.15..826.15 rows=1 width=1,803) (actual time=117.303..118.509 rows=1,373 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.339 117.384 ↓ 1,373.0 1,373 1

Sort (cost=826.15..826.15 rows=1 width=1,803) (actual time=117.297..117.384 rows=1,373 loops=1)

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

Initplan (for Sort)

5. 0.014 0.014 ↑ 1.0 1 1

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

  • Index Cond: (id = 222)
6. 0.030 0.030 ↑ 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.030..0.030 rows=1 loops=1)

  • Index Cond: (id = 222)
7. 8.908 113.001 ↓ 1,373.0 1,373 1

WindowAgg (cost=817.96..818.02 rows=1 width=1,803) (actual time=104.991..113.001 rows=1,373 loops=1)

8. 2.158 104.093 ↓ 1,373.0 1,373 1

Sort (cost=817.96..817.97 rows=1 width=1,732) (actual time=103.952..104.093 rows=1,373 loops=1)

  • Sort Key: a.date DESC, a.id DESC
  • Sort Method: quicksort Memory: 1242kB
9. 2.547 101.935 ↓ 1,373.0 1,373 1

WindowAgg (cost=817.96..817.96 rows=1 width=1,732) (actual time=100.348..101.935 rows=1,373 loops=1)

10. 2.736 99.388 ↓ 1,373.0 1,373 1

Sort (cost=817.96..817.96 rows=1 width=1,724) (actual time=99.299..99.388 rows=1,373 loops=1)

  • Sort Key: i.id
  • Sort Method: quicksort Memory: 1176kB
11. 0.857 96.652 ↓ 1,373.0 1,373 1

Result (cost=386.33..817.96 rows=1 width=1,724) (actual time=6.497..96.652 rows=1,373 loops=1)

  • One-Time Filter: $3
12. 2.764 95.795 ↓ 1,373.0 1,373 1

Nested Loop Left Join (cost=386.33..817.96 rows=1 width=1,724) (actual time=6.462..95.795 rows=1,373 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
  • -> Nested Loop Left Join (cost=386.28..817.89 rows=1 width=1720) (actual time=6.452..92.519 rows=1373 loo
13. 1.461 88.912 ↓ 1,373.0 1,373 1

Nested Loop Left Join (cost=386.22..817.83 rows=1 width=1,716) (actual time=6.433..88.912 rows=1,373 loops=1)

14. 2.080 84.705 ↓ 1,373.0 1,373 1

Nested Loop (cost=386.14..817.74 rows=1 width=1,647) (actual time=6.419..84.705 rows=1,373 loops=1)

  • Join Filter: ((al.content_platform_id = pj.platform_id) OR (al.content_platform_id = 0))
15. 0.949 77.133 ↓ 1,373.0 1,373 1

Nested Loop (cost=386.03..809.56 rows=1 width=1,579) (actual time=6.399..77.133 rows=1,373 loops=1)

16. 1.412 72.065 ↓ 1,373.0 1,373 1

Nested Loop (cost=385.94..805.47 rows=1 width=1,558) (actual time=6.344..72.065 rows=1,373 loops=1)

17. 43.107 66.534 ↓ 1,373.0 1,373 1

Nested Loop (cost=385.85..801.37 rows=1 width=1,642) (actual time=6.303..66.534 rows=1,373 loops=1)

18. 0.472 19.308 ↓ 1,373.0 1,373 1

Nested Loop (cost=385.77..797.13 rows=1 width=1,520) (actual time=4.756..19.308 rows=1,373 loops=1)

19. 0.008 4.727 ↑ 1.0 1 1

Bitmap Heap Scan on issues i (cost=385.65..389.65 rows=1 width=43) (actual time=4.725..4.727 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=1
20. 0.000 4.719 ↓ 0.0 0 1

BitmapOr (cost=385.65..385.65 rows=2 width=0) (actual time=4.719..4.719 rows=0 loops=1)

21. 4.699 4.699 ↑ 2.0 1 1

Bitmap Index Scan on unique_volume_issue_number (cost=0.00..383.57 rows=2 width=0) (actual time=4.699..4.699 rows=1 loops=1)

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

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

  • Index Cond: (volume IS NULL)
23. 14.109 14.109 ↓ 152.6 1,373 1

Index Scan using articles_issue_id on articles a (cost=0.11..407.45 rows=9 width=1,481) (actual time=0.028..14.109 rows=1,373 loops=1)

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

Index Scan using journals_pkey on journals j (cost=0.08..4.09 rows=1 width=85) (actual time=0.003..0.003 rows=1 loops=1,373)

  • Index Cond: (id = 274)
25. 4.119 4.119 ↑ 1.0 1 1,373

Index Scan using all_journals_by_library_library_journal_idx on all_journals_by_library ajbl (cost=0.09..4.09 rows=1 width=30) (actual time=0.003..0.003 rows=1 loops=1,373)

  • Index Cond: ((library_id = 222) AND (journal_id = 274))
26. 4.119 4.119 ↑ 1.0 1 1,373

Index Scan using package_journals_pkey on package_journals pj (cost=0.09..4.09 rows=1 width=29) (actual time=0.003..0.003 rows=1 loops=1,373)

  • Index Cond: (id = ajbl.package_journal_id)
  • Filter: (end_date >= now())
27. 5.492 5.492 ↑ 1.0 1 1,373

Index Scan using article_links_article_id_btree on article_links al (cost=0.11..8.18 rows=1 width=68) (actual time=0.004..0.004 rows=1 loops=1,373)

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

SubPlan (for Index Scan)

29. 0.000 0.000 ↓ 0.0 0

Index Scan using platforms_id_btree on platforms (cost=0.05..4.06 rows=1 width=0) (never executed)

  • Index Cond: (id = al.content_platform_id)
  • Filter: enabled
30. 0.000 0.000 ↓ 0.0 0

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

  • Filter: enabled
31. 2.746 2.746 ↓ 0.0 0 1,373

Index Scan using unique_article_and_platform on article_pdf_links apl (cost=0.08..0.09 rows=1 width=81) (actual time=0.002..0.002 rows=0 loops=1,373)

  • Index Cond: ((al.article_id = article_id) AND (al.content_platform_id = platform_id))
  • Filter: (NOT deleted)
32. 2.746 2.746 ↓ 0.0 0 1,373

Index Scan using platforms_id_btree on platforms p2 (cost=0.05..0.06 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=1,373)

  • Index Cond: (id = pj.platform_id)
  • Filter: (enabled AND (url_transform_rule_id IS NOT NULL) AND (url_transform_rule_id <> 0))
  • Rows Removed by Filter: 1
33. 1.373 1.373 ↓ 0.0 0 1,373

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

  • Index Cond: (id = pj.platform_id)
  • Filter: (enabled AND (url_transform_rule_id IS NOT NULL) AND (url_transform_rule_id <> 0))
  • Rows Removed by Filter: 1
Planning time : 6.728 ms
Execution time : 126.659 ms