explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sVYy : Optimization for: Optimization for: plan #Zsh1; plan #92F4

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.032 3,498.392 ↑ 1.0 3 1

Nested Loop (cost=1,899,118.40..1,899,147.08 rows=3 width=88) (actual time=3,498.367..3,498.392 rows=3 loops=1)

  • Functions: 61
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 14.117 ms, Inlining 191.847 ms, Optimization 447.857 ms, Emission 267.879 ms, Total 921.700 ms
2. 337.337 3,498.312 ↑ 1.0 3 1

Limit (cost=1,899,110.13..1,899,110.13 rows=3 width=20) (actual time=3,498.312..3,498.312 rows=3 loops=1)

3. 0.078 3,160.975 ↑ 55.7 3 1

Sort (cost=1,899,110.13..1,899,110.54 rows=167 width=20) (actual time=3,160.974..3,160.975 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,160.897 ↑ 1.1 157 1

Finalize GroupAggregate (cost=1,899,064.82..1,899,107.97 rows=167 width=20) (actual time=3,160.700..3,160.897 rows=157 loops=1)

  • Group Key: research_output_field_of_research_counts.code
5. 202.335 3,334.689 ↓ 1.4 458 1

Gather Merge (cost=1,899,064.82..1,899,103.79 rows=334 width=20) (actual time=3,160.670..3,334.689 rows=458 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 0.196 3,132.354 ↑ 1.1 153 3 / 3

Sort (cost=1,898,064.80..1,898,065.22 rows=167 width=20) (actual time=3,132.346..3,132.354 rows=153 loops=3)

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

Partial HashAggregate (cost=1,898,056.96..1,898,058.63 rows=167 width=20) (actual time=3,132.139..3,132.158 rows=153 loops=3)

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

Parallel Hash Join (cost=1,598,408.92..1,896,091.52 rows=262,059 width=8) (actual time=1,054.821..3,077.795 rows=286,785 loops=3)

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

Parallel Seq Scan on research_output_field_of_research_counts (cost=0.00..268,517.63 rows=11,110,463 width=16) (actual time=0.019..869.571 rows=8,888,453 loops=3)

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

  • Buckets: 1048576 Batches: 1 Memory Usage: 32512kB
11. 942.152 1,001.884 ↑ 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=499.999..1,001.884 rows=206,719 loops=3)

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

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

Bitmap Heap Scan on fields_of_research (cost=8.28..12.29 rows=1 width=52) (actual time=0.016..0.016 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,680.083 ms