explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Hvy

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Limit (cost=591,938,133,237.41..591,938,133,239.91 rows=1,000 width=88) (actual rows= loops=)

  • JIT:
  • Functions: 55
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
2. 0.000 0.000 ↓ 0.0

Sort (cost=591,938,133,237.41..610,727,167,981.02 rows=7,515,613,897,444 width=88) (actual rows= loops=)

  • Sort Key: (((((count(a.user_id)) / gg.total_count))::numeric * (sum(((a.common_favs_count)::numeric / ((count(*)))::numeric))))) DESC
3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=183,624,085.95..179,865,129,406.51 rows=7,515,613,897,444 width=88) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=183,624,085.38..193,539,898.06 rows=2,741,462 width=48) (actual rows= loops=)

  • Group Key: faves.submission_id
5. 0.000 0.000 ↓ 0.0

Sort (cost=183,624,085.38..185,035,734.58 rows=564,659,680 width=32) (actual rows= loops=)

  • Sort Key: faves.submission_id
6. 0.000 0.000 ↓ 0.0

Hash Join (cost=85,066,236.65..85,716,963.76 rows=564,659,680 width=32) (actual rows= loops=)

  • Hash Cond: (faves_1.user_id = a.user_id)
7. 0.000 0.000 ↓ 0.0

Hash Join (cost=324,520.94..891,039.77 rows=31,413,673 width=32) (actual rows= loops=)

  • Hash Cond: (faves.user_id = faves_1.user_id)
8. 0.000 0.000 ↓ 0.0

Seq Scan on faves (cost=0.00..483,960.73 rows=31,413,673 width=16) (actual rows= loops=)

  • Filter: (submission_id IS NOT NULL)
9. 0.000 0.000 ↓ 0.0

Hash (cost=324,476.00..324,476.00 rows=3,595 width=16) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Finalize GroupAggregate (cost=323,073.95..324,440.05 rows=3,595 width=16) (actual rows= loops=)

  • Group Key: faves_1.user_id
11. 0.000 0.000 ↓ 0.0

Gather Merge (cost=323,073.95..324,350.18 rows=10,785 width=16) (actual rows= loops=)

  • Workers Planned: 3
12. 0.000 0.000 ↓ 0.0

Sort (cost=322,073.91..322,082.90 rows=3,595 width=16) (actual rows= loops=)

  • Sort Key: faves_1.user_id
13. 0.000 0.000 ↓ 0.0

Partial HashAggregate (cost=321,825.64..321,861.59 rows=3,595 width=16) (actual rows= loops=)

  • Group Key: faves_1.user_id
14. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on faves faves_1 (cost=0.00..271,158.43 rows=10,133,443 width=8) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Hash (cost=84,741,670.78..84,741,670.78 rows=3,595 width=16) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Subquery Scan on a (cost=84,728,100.38..84,741,670.78 rows=3,595 width=16) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=84,728,100.38..84,741,634.83 rows=3,595 width=48) (actual rows= loops=)

  • Group Key: faves_2.user_id
18. 0.000 0.000 ↓ 0.0

Sort (cost=84,728,100.38..84,732,599.88 rows=1,799,800 width=8) (actual rows= loops=)

  • Sort Key: faves_2.user_id
19. 0.000 0.000 ↓ 0.0

Merge Semi Join (cost=1.12..84,526,692.96 rows=1,799,800 width=8) (actual rows= loops=)

  • Merge Cond: (faves_2.submission_id = faves_3.submission_id)
20. 0.000 0.000 ↓ 0.0

Index Scan using subm_id_idx on faves faves_2 (cost=0.56..748,835.69 rows=31,413,673 width=16) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Index Scan using subm_id_idx on faves faves_3 (cost=0.56..83,680,932.41 rows=157,068 width=8) (actual rows= loops=)

  • Filter: (user_id = (SubPlan 1))
22.          

SubPlan (for Index Scan)

23. 0.000 0.000 ↓ 0.0

Index Only Scan using users_pkey on users (cost=0.42..2.64 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (username = 'testuser'::text)
24. 0.000 0.000 ↓ 0.0

Materialize (cost=0.56..985,149.61 rows=2,741,462 width=8) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Subquery Scan on gg (cost=0.56..960,733.30 rows=2,741,462 width=8) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=0.56..933,318.68 rows=2,741,462 width=16) (actual rows= loops=)

  • Group Key: faves_4.submission_id
27. 0.000 0.000 ↓ 0.0

Index Only Scan using subm_id_idx on faves faves_4 (cost=0.56..748,835.69 rows=31,413,673 width=8) (actual rows= loops=)