explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dw1I

Settings
# exclusive inclusive rows x rows loops node
1. 216.745 6,133.920 ↓ 1.9 169,974 1

Nested Loop Left Join (cost=151,156.00..1,885,694.15 rows=91,829 width=40) (actual time=491.160..6,133.920 rows=169,974 loops=1)

2.          

CTE affinity_scores

3. 348.348 389.526 ↑ 1.0 10 1

Limit (cost=79,717.64..79,717.67 rows=10 width=40) (actual time=389.520..389.526 rows=10 loops=1)

4. 0.038 41.178 ↑ 142.5 10 1

Sort (cost=79,717.64..79,721.20 rows=1,425 width=40) (actual time=41.177..41.178 rows=10 loops=1)

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

Nested Loop (cost=79,505.18..79,686.85 rows=1,425 width=40) (actual time=41.126..41.140 rows=12 loops=1)

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

Aggregate (cost=5,716.44..5,716.45 rows=1 width=8) (actual time=0.077..0.077 rows=1 loops=1)

7. 0.064 0.064 ↑ 698.7 10 1

Index Only Scan using usr_id_idx on faves faves_2 (cost=0.56..5,698.97 rows=6,987 width=0) (actual time=0.061..0.064 rows=10 loops=1)

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

Unique (cost=73,788.74..73,831.48 rows=4,274 width=48) (actual time=41.023..41.039 rows=13 loops=1)

9. 0.016 41.023 ↑ 328.8 13 1

Sort (cost=73,788.74..73,799.43 rows=4,274 width=48) (actual time=41.022..41.023 rows=13 loops=1)

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

GroupAggregate (cost=72,421.19..73,530.99 rows=4,274 width=48) (actual time=40.995..41.007 rows=13 loops=1)

  • Group Key: faves_3.user_id
11. 0.000 40.934 ↑ 4,801.7 22 1

Sort (cost=72,421.19..72,685.28 rows=105,638 width=16) (actual time=40.932..40.934 rows=22 loops=1)

  • Sort Key: faves_3.user_id
  • Sort Method: quicksort Memory: 26kB
12. 40.955 41.115 ↑ 4,801.7 22 1

Gather (cost=1,077.28..63,606.34 rows=105,638 width=16) (actual time=0.569..41.115 rows=22 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
13. 0.005 0.160 ↑ 6,288.0 7 3 / 3

Nested Loop (cost=77.28..52,042.54 rows=44,016 width=16) (actual time=0.083..0.160 rows=7 loops=3)

14. 0.070 0.072 ↑ 970.3 3 3 / 3

Parallel Bitmap Heap Scan on faves faves_4 (cost=76.71..7,550.78 rows=2,911 width=8) (actual time=0.072..0.072 rows=3 loops=3)

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

Bitmap Index Scan on usr_id_idx (cost=0.00..74.97 rows=6,987 width=0) (actual time=0.006..0.006 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.013..0.025 rows=2 loops=10)

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

GroupAggregate (cost=71,419.87..73,486.03 rows=91,829 width=48) (actual time=491.055..647.981 rows=169,974 loops=1)

  • Group Key: faves.submission_id
18. 76.988 523.570 ↓ 1.9 173,409 1

Sort (cost=71,419.87..71,649.45 rows=91,829 width=16) (actual time=491.006..523.570 rows=173,409 loops=1)

  • Sort Key: faves.submission_id
  • Sort Method: quicksort Memory: 14,273kB
19. 15.632 446.582 ↓ 1.9 173,409 1

Nested Loop (cost=0.79..63,850.10 rows=91,829 width=16) (actual time=389.610..446.582 rows=173,409 loops=1)

20. 0.028 389.560 ↑ 1.0 10 1

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

  • Group Key: affinity_scores.user_id
21. 389.532 389.532 ↑ 1.0 10 1

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

22. 41.390 41.390 ↓ 1.9 17,341 10

Index Scan using usr_id_idx on faves (cost=0.56..6,293.15 rows=9,183 width=16) (actual time=0.041..4.139 rows=17,341 loops=10)

  • Index Cond: (user_id = affinity_scores.user_id)
23. 169.974 4,929.246 ↑ 1.0 1 169,974

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

24. 4,759.272 4,759.272 ↑ 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.007..0.028 rows=15 loops=169,974)

  • Index Cond: (submission_id = faves.submission_id)
  • Heap Fetches: 2,528,653
25.          

SubPlan (for Nested Loop Left Join)

26. 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)

27. 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 : 1.236 ms