explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 92F4 : Optimization for: plan #Zsh1

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.065 3,537.995 ↑ 1.0 3 1

Nested Loop (cost=1,899,120.04..1,899,148.72 rows=3 width=88) (actual time=3,537.936..3,537.995 rows=3 loops=1)

  • Functions: 61
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 15.076 ms, Inlining 197.068 ms, Optimization 468.375 ms, Emission 256.267 ms, Total 936.785 ms
2. 336.857 3,537.852 ↑ 1.0 3 1

Limit (cost=1,899,111.76..1,899,111.77 rows=3 width=20) (actual time=3,537.851..3,537.852 rows=3 loops=1)

3. 0.077 3,200.995 ↑ 56.0 3 1

Sort (cost=1,899,111.76..1,899,112.18 rows=168 width=20) (actual time=3,200.995..3,200.995 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,200.918 ↑ 1.1 157 1

Finalize GroupAggregate (cost=1,899,066.19..1,899,109.59 rows=168 width=20) (actual time=3,200.541..3,200.918 rows=157 loops=1)

  • Group Key: research_output_field_of_research_counts.code
5. 207.649 3,379.968 ↓ 1.4 460 1

Gather Merge (cost=1,899,066.19..1,899,105.39 rows=336 width=20) (actual time=3,200.491..3,379.968 rows=460 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 0.201 3,172.319 ↑ 1.1 153 3 / 3

Sort (cost=1,898,066.16..1,898,066.58 rows=168 width=20) (actual time=3,172.309..3,172.319 rows=153 loops=3)

  • Sort Key: research_output_field_of_research_counts.code
  • Sort Method: quicksort Memory: 36kB
  • Worker 0: Sort Method: quicksort Memory: 37kB
  • Worker 1: Sort Method: quicksort Memory: 37kB
7. 53.215 3,172.118 ↑ 1.1 153 3 / 3

Partial HashAggregate (cost=1,898,058.27..1,898,059.95 rows=168 width=20) (actual time=3,172.096..3,172.118 rows=153 loops=3)

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

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

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

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

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

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

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

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

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

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

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