explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l6x2J

Settings
# exclusive inclusive rows x rows loops node
1. 206.453 4,728.877 ↓ 1.9 169,974 1

Nested Loop Left Join (cost=160,081.85..1,885,915.11 rows=91,465 width=40) (actual time=599.367..4,728.877 rows=169,974 loops=1)

  • JIT:
  • Functions: 64
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 5.270 ms, Inlining 11.883 ms, Optimization 190.178 ms, Emission 133.338 ms, Total 340.668 ms
2.          

CTE affinity_scores

3. 335.943 379.857 ↑ 1.0 10 1

Limit (cost=79,329.64..79,329.67 rows=10 width=40) (actual time=379.855..379.857 rows=10 loops=1)

4. 0.022 43.914 ↑ 142.5 10 1

Sort (cost=79,329.64..79,333.20 rows=1,425 width=40) (actual time=43.913..43.914 rows=10 loops=1)

  • Sort Key: ((((count(*)))::numeric / ((count(*)))::numeric)) DESC
  • Sort Method: quicksort Memory: 25kB
5. 0.019 43.892 ↑ 118.8 12 1

Nested Loop (cost=79,117.18..79,298.85 rows=1,425 width=40) (actual time=43.875..43.892 rows=12 loops=1)

  • Join Filter: ((((count(*)))::numeric / ((count(*)))::numeric) < '1'::numeric)
  • Rows Removed by Join Filter: 1
6. 0.010 0.084 ↑ 1.0 1 1

Aggregate (cost=5,694.10..5,694.11 rows=1 width=8) (actual time=0.084..0.084 rows=1 loops=1)

7. 0.074 0.074 ↑ 695.9 10 1

Index Only Scan using usr_id_idx on faves faves_2 (cost=0.56..5,676.71 rows=6,959 width=0) (actual time=0.070..0.074 rows=10 loops=1)

  • Index Cond: (user_id = 1)
  • Heap Fetches: 10
8. 0.011 43.789 ↑ 328.8 13 1

Unique (cost=73,423.08..73,465.82 rows=4,274 width=48) (actual time=43.778..43.789 rows=13 loops=1)

9. 0.009 43.778 ↑ 328.8 13 1

Sort (cost=73,423.08..73,433.76 rows=4,274 width=48) (actual time=43.777..43.778 rows=13 loops=1)

  • Sort Key: faves_3.user_id, (count(*)), (array_agg(faves_3.submission_id))
  • Sort Method: quicksort Memory: 26kB
10. 0.042 43.769 ↑ 328.8 13 1

GroupAggregate (cost=72,063.92..73,165.33 rows=4,274 width=48) (actual time=43.762..43.769 rows=13 loops=1)

  • Group Key: faves_3.user_id
11. 0.000 43.727 ↑ 4,763.5 22 1

Sort (cost=72,063.92..72,325.92 rows=104,798 width=16) (actual time=43.725..43.727 rows=22 loops=1)

  • Sort Key: faves_3.user_id
  • Sort Method: quicksort Memory: 26kB
12. 43.771 43.938 ↑ 4,763.5 22 1

Gather (cost=1,077.06..63,325.21 rows=104,798 width=16) (actual time=0.518..43.938 rows=22 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
13. 0.007 0.167 ↑ 6,238.0 7 3 / 3

Nested Loop (cost=77.06..51,845.41 rows=43,666 width=16) (actual time=0.091..0.167 rows=7 loops=3)

14. 0.072 0.077 ↑ 966.7 3 3 / 3

Parallel Bitmap Heap Scan on faves faves_4 (cost=76.50..7,521.20 rows=2,900 width=8) (actual time=0.077..0.077 rows=3 loops=3)

  • Recheck Cond: (user_id = 1)
  • Heap Blocks: exact=1
15. 0.005 0.005 ↑ 695.9 10 1 / 3

Bitmap Index Scan on usr_id_idx (cost=0.00..74.76 rows=6,959 width=0) (actual time=0.014..0.014 rows=10 loops=1)

  • Index Cond: (user_id = 1)
16. 0.083 0.083 ↑ 7.5 2 10 / 3

Index Scan using subm_id_idx on faves faves_3 (cost=0.56..15.13 rows=15 width=16) (actual time=0.016..0.025 rows=2 loops=10)

  • Index Cond: (submission_id = faves_4.submission_id)
17. 41.103 613.022 ↓ 1.9 169,974 1

Sort (cost=80,733.72..80,962.39 rows=91,465 width=48) (actual time=599.256..613.022 rows=169,974 loops=1)

  • Sort Key: (count(*)) DESC
  • Sort Method: quicksort Memory: 19,628kB
18. 81.428 571.919 ↓ 1.9 169,974 1

GroupAggregate (cost=71,138.62..73,196.58 rows=91,465 width=48) (actual time=475.100..571.919 rows=169,974 loops=1)

  • Group Key: faves.submission_id
19. 61.028 490.491 ↓ 1.9 173,409 1

Sort (cost=71,138.62..71,367.28 rows=91,465 width=16) (actual time=475.059..490.491 rows=173,409 loops=1)

  • Sort Key: faves.submission_id
  • Sort Method: quicksort Memory: 14,273kB
20. 16.293 429.463 ↓ 1.9 173,409 1

Nested Loop (cost=0.79..63,601.48 rows=91,465 width=16) (actual time=379.938..429.463 rows=173,409 loops=1)

21. 0.028 379.890 ↑ 1.0 10 1

HashAggregate (cost=0.23..0.33 rows=10 width=8) (actual time=379.872..379.890 rows=10 loops=1)

  • Group Key: affinity_scores.user_id
22. 379.862 379.862 ↑ 1.0 10 1

CTE Scan on affinity_scores (cost=0.00..0.20 rows=10 width=8) (actual time=379.857..379.862 rows=10 loops=1)

23. 33.280 33.280 ↓ 1.9 17,341 10

Index Scan using usr_id_idx on faves (cost=0.56..6,268.65 rows=9,147 width=16) (actual time=0.029..3.328 rows=17,341 loops=10)

  • Index Cond: (user_id = affinity_scores.user_id)
24. 339.948 3,569.454 ↑ 1.0 1 169,974

Aggregate (cost=18.46..18.47 rows=1 width=8) (actual time=0.021..0.021 rows=1 loops=169,974)

25. 3,229.506 3,229.506 ↑ 1.0 15 169,974

Index Only Scan using subm_id_idx on faves faves_1 (cost=0.56..18.42 rows=15 width=0) (actual time=0.005..0.019 rows=15 loops=169,974)

  • Index Cond: (submission_id = faves.submission_id)
  • Heap Fetches: 2,524,702
26.          

SubPlan (for Nested Loop Left Join)

27. 169.974 339.948 ↑ 1.0 1 169,974

Aggregate (cost=0.35..0.36 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=169,974)

28. 169.974 169.974 ↑ 7.0 1 169,974

CTE Scan on affinity_scores affinity_scores_1 (cost=0.00..0.33 rows=7 width=32) (actual time=0.001..0.001 rows=1 loops=169,974)

  • Filter: (user_id = ANY ((array_agg(faves.user_id))))
  • Rows Removed by Filter: 9
Planning time : 0.700 ms
Execution time : 4,744.722 ms