explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 73KH

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.054 42,355.002 ↑ 1.0 31 1

Nested Loop (cost=1,889,331.43..1,889,597.23 rows=31 width=205) (actual time=41,956.496..42,355.002 rows=31 loops=1)

  • Functions: 56
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 6.697 ms, Inlining 134.719 ms, Optimization 169.441 ms, Emission 123.227 ms, Total 434.083 ms
2. 184.224 42,349.027 ↑ 1.0 31 1

Limit (cost=1,889,330.86..1,889,330.94 rows=31 width=25) (actual time=41,956.424..42,349.027 rows=31 loops=1)

3. 812.563 42,164.803 ↑ 3,227.3 31 1

Sort (cost=1,889,330.86..1,889,580.98 rows=100,046 width=25) (actual time=41,772.203..42,164.803 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,509.805 41,352.240 ↓ 60.7 6,070,922 1

Finalize GroupAggregate (cost=1,861,005.78..1,886,352.40 rows=100,046 width=25) (actual time=34,029.112..41,352.240 rows=6,070,922 loops=1)

  • Group Key: mention_profiles.post_profile_id
5. 3,647.494 39,842.435 ↓ 49.9 9,987,439 1

Gather Merge (cost=1,861,005.78..1,884,351.48 rows=200,092 width=25) (actual time=34,029.085..39,842.435 rows=9,987,439 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 17,923.687 36,194.941 ↓ 33.3 3,329,146 3 / 3

Sort (cost=1,860,005.76..1,860,255.87 rows=100,046 width=25) (actual time=33,999.275..36,194.941 rows=3,329,146 loops=3)

  • Sort Key: mention_profiles.post_profile_id DESC
  • Sort Method: external merge Disk: 129,736kB
  • Worker 0: Sort Method: external merge Disk: 130,440kB
  • Worker 1: Sort Method: external merge Disk: 131,536kB
7. 7,849.315 18,271.254 ↓ 33.3 3,329,146 3 / 3

Partial HashAggregate (cost=1,850,696.32..1,851,696.78 rows=100,046 width=25) (actual time=17,528.850..18,271.254 rows=3,329,146 loops=3)

  • Group Key: mention_profiles.post_profile_id
8. 4,614.613 10,421.939 ↓ 1.3 24,716,765 3 / 3

Parallel Hash Join (cost=533,450.30..1,758,043.15 rows=18,530,634 width=21) (actual time=3,631.283..10,421.939 rows=24,716,765 loops=3)

  • Hash Cond: (mention_profiles.research_output_id = research_outputs.id)
9. 2,192.893 2,192.893 ↑ 1.3 29,881,235 3 / 3

Parallel Seq Scan on mention_profiles (cost=0.00..1,126,536.60 rows=37,354,760 width=29) (actual time=0.014..2,192.893 rows=29,881,235 loops=3)

10. 1,969.275 3,614.433 ↓ 1.1 6,408,614 3 / 3

Parallel Hash (cost=462,117.57..462,117.57 rows=5,706,618 width=8) (actual time=3,614.431..3,614.433 rows=6,408,614 loops=3)

  • Buckets: 33,554,432 (originally 16777216) Batches: 1 (originally 1) Memory Usage: 1,145,376kB
11. 1,645.158 1,645.158 ↓ 1.1 6,408,614 3 / 3

Parallel Index Only Scan using research_outputs_16 on research_outputs (cost=0.56..462,117.57 rows=5,706,618 width=8) (actual time=82.782..1,645.158 rows=6,408,614 loops=3)

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

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

  • Index Cond: (id = mention_profiles.post_profile_id)
Execution time : 42,451.512 ms