explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XuMW

Settings
# exclusive inclusive rows x rows loops node
1. 0.053 41,715.871 ↑ 1.0 31 1

Nested Loop (cost=1,880,154.00..1,880,419.81 rows=31 width=204) (actual time=41,221.224..41,715.871 rows=31 loops=1)

  • Functions: 56
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 6.483 ms, Inlining 124.356 ms, Optimization 171.376 ms, Emission 124.248 ms, Total 426.463 ms
2. 188.071 41,715.353 ↑ 1.0 31 1

Limit (cost=1,880,153.44..1,880,153.52 rows=31 width=25) (actual time=41,221.163..41,715.353 rows=31 loops=1)

3. 825.830 41,527.282 ↑ 3,313.3 31 1

Sort (cost=1,880,153.44..1,880,410.22 rows=102,713 width=25) (actual time=41,033.095..41,527.282 rows=31 loops=1)

  • Sort Key: (sum(mention_profiles.mention_count)) DESC, mention_profiles.post_profile_id DESC
  • Sort Method: top-N heapsort Memory: 28kB
4. 1,534.960 40,701.452 ↓ 59.1 6,070,912 1

Finalize GroupAggregate (cost=1,851,073.27..1,877,095.57 rows=102,713 width=25) (actual time=33,152.414..40,701.452 rows=6,070,912 loops=1)

  • Group Key: mention_profiles.post_profile_id
5. 3,977.538 39,166.492 ↓ 48.6 9,988,884 1

Gather Merge (cost=1,851,073.27..1,875,041.31 rows=205,426 width=25) (actual time=33,152.374..39,166.492 rows=9,988,884 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 17,840.582 35,188.954 ↓ 32.4 3,329,628 3 / 3

Sort (cost=1,850,073.25..1,850,330.03 rows=102,713 width=25) (actual time=33,004.754..35,188.954 rows=3,329,628 loops=3)

  • Sort Key: mention_profiles.post_profile_id DESC
  • Sort Method: external merge Disk: 129,352kB
  • Worker 0: Sort Method: external merge Disk: 131,680kB
  • Worker 1: Sort Method: external merge Disk: 130,736kB
7. 7,835.175 17,348.372 ↓ 32.4 3,329,628 3 / 3

Partial HashAggregate (cost=1,840,496.16..1,841,523.29 rows=102,713 width=25) (actual time=16,609.593..17,348.372 rows=3,329,628 loops=3)

  • Group Key: mention_profiles.post_profile_id
8. 4,550.997 9,513.197 ↓ 1.4 24,716,364 3 / 3

Parallel Hash Join (cost=524,993.16..1,749,366.52 rows=18,225,928 width=21) (actual time=2,810.344..9,513.197 rows=24,716,364 loops=3)

  • Hash Cond: (mention_profiles.research_output_id = research_outputs.id)
9. 2,168.667 2,168.667 ↑ 1.2 29,878,616 3 / 3

Parallel Seq Scan on mention_profiles (cost=0.00..1,126,348.60 rows=37,342,760 width=29) (actual time=0.014..2,168.667 rows=29,878,616 loops=3)

10. 1,747.689 2,793.533 ↓ 1.1 6,404,890 3 / 3

Parallel Hash (cost=454,724.19..454,724.19 rows=5,621,518 width=8) (actual time=2,793.532..2,793.533 rows=6,404,890 loops=3)

  • Buckets: 33,554,432 (originally 16777216) Batches: 1 (originally 1) Memory Usage: 1,144,960kB
11. 1,045.844 1,045.844 ↓ 1.1 6,404,890 3 / 3

Parallel Index Only Scan using research_outputs_16 on research_outputs (cost=0.56..454,724.19 rows=5,621,518 width=8) (actual time=77.561..1,045.844 rows=6,404,890 loops=3)

  • Index Cond: ((output_type = 'article'::text) AND (books = false))
  • Heap Fetches: 0
12. 0.465 0.465 ↑ 1.0 1 31

Index Scan using post_profiles_constraint_0 on post_profiles (cost=0.56..8.58 rows=1 width=196) (actual time=0.015..0.015 rows=1 loops=31)

  • Index Cond: (id = mention_profiles.post_profile_id)
Planning time : 6.906 ms
Execution time : 41,813.961 ms