explain.depesz.com

PostgreSQL's explain analyze made readable

Result: odes : Optimization for: plan #Zsh1

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.022 3,568.939 ↑ 1.0 3 1

Nested Loop (cost=1,899,118.04..1,899,146.72 rows=3 width=88) (actual time=3,568.922..3,568.939 rows=3 loops=1)

  • Functions: 61
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 13.799 ms, Inlining 190.626 ms, Optimization 435.732 ms, Emission 239.343 ms, Total 879.500 ms
2. 336.274 3,568.872 ↑ 1.0 3 1

Limit (cost=1,899,109.76..1,899,109.77 rows=3 width=20) (actual time=3,568.871..3,568.872 rows=3 loops=1)

3. 0.053 3,232.598 ↑ 56.0 3 1

Sort (cost=1,899,109.76..1,899,110.18 rows=168 width=20) (actual time=3,232.598..3,232.598 rows=3 loops=1)

  • Sort Key: (sum(research_output_field_of_research_counts.total_mentions)) DESC
  • Sort Method: top-N heapsort Memory: 25kB
4. 0.000 3,232.545 ↑ 1.1 157 1

Finalize GroupAggregate (cost=1,899,064.19..1,899,107.59 rows=168 width=20) (actual time=3,232.372..3,232.545 rows=157 loops=1)

  • Group Key: research_output_field_of_research_counts.code
5. 171.466 3,375.736 ↓ 1.4 457 1

Gather Merge (cost=1,899,064.19..1,899,103.39 rows=336 width=20) (actual time=3,232.346..3,375.736 rows=457 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 0.195 3,204.270 ↑ 1.1 152 3 / 3

Sort (cost=1,898,064.16..1,898,064.58 rows=168 width=20) (actual time=3,204.261..3,204.270 rows=152 loops=3)

  • Sort Key: research_output_field_of_research_counts.code
  • Sort Method: quicksort Memory: 36kB
  • Worker 0: Sort Method: quicksort Memory: 36kB
  • Worker 1: Sort Method: quicksort Memory: 36kB
7. 55.239 3,204.075 ↑ 1.1 152 3 / 3

Partial HashAggregate (cost=1,898,056.27..1,898,057.95 rows=168 width=20) (actual time=3,204.056..3,204.075 rows=152 loops=3)

  • Group Key: research_output_field_of_research_counts.code
8. 1,414.138 3,148.836 ↓ 1.1 286,785 3 / 3

Parallel Hash Join (cost=1,598,408.92..1,896,090.82 rows=262,061 width=8) (actual time=1,060.158..3,148.836 rows=286,785 loops=3)

  • Hash Cond: (research_output_field_of_research_counts.research_output_id = research_outputs.id)
9. 677.168 677.168 ↑ 1.2 8,888,453 3 / 3

Parallel Seq Scan on research_output_field_of_research_counts (cost=0.00..268,516.66 rows=11,110,566 width=16) (actual time=0.011..677.168 rows=8,888,453 loops=3)

10. 50.053 1,057.530 ↑ 1.3 206,719 3 / 3

Parallel Hash (cost=1,595,071.41..1,595,071.41 rows=267,001 width=8) (actual time=1,057.530..1,057.530 rows=206,719 loops=3)

  • Buckets: 1048576 Batches: 1 Memory Usage: 32480kB
11. 945.802 1,007.477 ↑ 1.3 206,719 3 / 3

Parallel Bitmap Heap Scan on research_outputs (cost=5,910.22..1,595,071.41 rows=267,001 width=8) (actual time=493.402..1,007.477 rows=206,719 loops=3)

  • Recheck Cond: (publisher_ids && '{4924dd2b-aeb4-494a-bfc1-4913b1fd9067}'::text[])
  • Heap Blocks: exact=361655
12. 61.675 61.675 ↑ 1.0 620,156 1 / 3

Bitmap Index Scan on research_outputs_24 (cost=0.00..5,750.02 rows=640,802 width=0) (actual time=185.025..185.025 rows=620,156 loops=1)

  • Index Cond: (publisher_ids && '{4924dd2b-aeb4-494a-bfc1-4913b1fd9067}'::text[])
13. 0.015 0.045 ↑ 1.0 1 3

Bitmap Heap Scan on fields_of_research (cost=8.28..12.29 rows=1 width=52) (actual time=0.015..0.015 rows=1 loops=3)

  • Recheck Cond: ((code)::text = research_output_field_of_research_counts.code)
  • Heap Blocks: exact=3
14. 0.030 0.030 ↑ 1.0 1 3

Bitmap Index Scan on fields_of_research_code_index (cost=0.00..8.28 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=3)

  • Index Cond: ((code)::text = research_output_field_of_research_counts.code)
Execution time : 3,719.623 ms