explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2GiE

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

Sort (cost=152,367,341.20..152,374,205.47 rows=2,745,708 width=40) (actual rows= loops=)

  • Sort Key: (((((count(*)))::numeric / ((count(*)))::numeric) * (SubPlan 1))) DESC
  • JIT:
  • Functions: 27
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
2. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,238,257.15..152,029,730.39 rows=2,745,708 width=40) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Sort (cost=2,238,239.80..2,245,104.07 rows=2,745,708 width=48) (actual rows= loops=)

  • Sort Key: (count(*)) DESC
4. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=1,741,691.68..1,895,130.89 rows=2,745,708 width=48) (actual rows= loops=)

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

Sort (cost=1,741,691.68..1,771,471.14 rows=11,911,786 width=16) (actual rows= loops=)

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

Nested Loop (cost=25.57..222,470.04 rows=11,911,786 width=16) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

HashAggregate (cost=25.00..27.00 rows=200 width=8) (actual rows= loops=)

  • Group Key: affinity_scores.user_id
8. 0.000 0.000 ↓ 0.0

Seq Scan on affinity_scores (cost=0.00..22.00 rows=1,200 width=8) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Index Scan using usr_id_idx on faves (cost=0.56..1,012.96 rows=9,926 width=16) (actual rows= loops=)

  • Index Cond: (user_id = affinity_scores.user_id)
10. 0.000 0.000 ↓ 0.0

Aggregate (cost=17.34..17.35 rows=1 width=8) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Index Only Scan using subm_id_idx on faves faves_1 (cost=0.56..17.31 rows=14 width=0) (actual rows= loops=)

  • Index Cond: (submission_id = faves.submission_id)
12.          

SubPlan (for Nested Loop Left Join)

13. 0.000 0.000 ↓ 0.0

Aggregate (cost=37.15..37.16 rows=1 width=32) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Seq Scan on affinity_scores affinity_scores_1 (cost=0.00..37.00 rows=59 width=32) (actual rows= loops=)

  • Filter: (user_id = ANY ((array_agg(faves.user_id))))