explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Zsh1

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.026 3,557.803 ↑ 1.0 3 1

Nested Loop (cost=1,899,122.03..1,899,150.70 rows=3 width=88) (actual time=3,557.783..3,557.803 rows=3 loops=1)

  • Functions: 61
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 13.364 ms, Inlining 190.744 ms, Optimization 441.631 ms, Emission 238.881 ms, Total 884.620 ms
2. 331.039 3,557.726 ↑ 1.0 3 1

Limit (cost=1,899,113.75..1,899,113.76 rows=3 width=20) (actual time=3,557.725..3,557.726 rows=3 loops=1)

3. 0.053 3,226.687 ↑ 56.0 3 1

Sort (cost=1,899,113.75..1,899,114.17 rows=168 width=20) (actual time=3,226.687..3,226.687 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,226.634 ↑ 1.1 157 1

Finalize GroupAggregate (cost=1,899,068.18..1,899,111.58 rows=168 width=20) (actual time=3,226.415..3,226.634 rows=157 loops=1)

  • Group Key: research_output_field_of_research_counts.code
5. 191.560 3,389.453 ↓ 1.4 459 1

Gather Merge (cost=1,899,068.18..1,899,107.38 rows=336 width=20) (actual time=3,226.388..3,389.453 rows=459 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 0.204 3,197.893 ↑ 1.1 153 3 / 3

Sort (cost=1,898,068.15..1,898,068.57 rows=168 width=20) (actual time=3,197.886..3,197.893 rows=153 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: 37kB
7. 52.817 3,197.689 ↑ 1.1 153 3 / 3

Partial HashAggregate (cost=1,898,060.26..1,898,061.94 rows=168 width=20) (actual time=3,197.669..3,197.689 rows=153 loops=3)

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

Parallel Hash Join (cost=1,598,408.92..1,896,094.75 rows=262,068 width=8) (actual time=1,121.365..3,144.872 rows=286,785 loops=3)

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

Parallel Seq Scan on research_output_field_of_research_counts (cost=0.00..268,519.78 rows=11,110,878 width=16) (actual time=0.012..637.642 rows=8,888,453 loops=3)

10. 50.802 1,118.599 ↑ 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,118.598..1,118.599 rows=206,719 loops=3)

  • Buckets: 1048576 Batches: 1 Memory Usage: 32480kB
11. 1,007.903 1,067.797 ↑ 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=490.338..1,067.797 rows=206,719 loops=3)

  • Recheck Cond: (publisher_ids && '{4924dd2b-aeb4-494a-bfc1-4913b1fd9067}'::text[])
  • Heap Blocks: exact=336152
12. 59.894 59.894 ↑ 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=179.681..179.681 rows=620,156 loops=1)

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

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

  • Recheck Cond: ((code)::text = research_output_field_of_research_counts.code)
  • Heap Blocks: exact=3
14. 0.033 0.033 ↑ 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.011..0.011 rows=1 loops=3)

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