explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0lhF

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

Nested Loop Left Join (cost=150,521.64..1,878,750.86 rows=91,495 width=40) (actual time=461.244..4,555.745 rows=169,974 loops=1)

2.          

CTE affinity_scores

3. 327.325 365.686 ↑ 1.0 10 1

Limit (cost=79,346.78..79,346.80 rows=10 width=40) (actual time=365.683..365.686 rows=10 loops=1)

4. 0.023 38.361 ↑ 142.5 10 1

Sort (cost=79,346.78..79,350.34 rows=1,425 width=40) (actual time=38.360..38.361 rows=10 loops=1)

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

Nested Loop (cost=79,134.31..79,315.98 rows=1,425 width=40) (actual time=38.321..38.338 rows=12 loops=1)

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

Aggregate (cost=5,695.76..5,695.77 rows=1 width=8) (actual time=0.064..0.065 rows=1 loops=1)

7. 0.056 0.056 ↑ 696.1 10 1

Index Only Scan using usr_id_idx on faves faves_2 (cost=0.56..5,678.35 rows=6,961 width=0) (actual time=0.053..0.056 rows=10 loops=1)

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

Unique (cost=73,438.56..73,481.30 rows=4,274 width=48) (actual time=38.236..38.248 rows=13 loops=1)

9. 0.011 38.236 ↑ 328.8 13 1

Sort (cost=73,438.56..73,449.24 rows=4,274 width=48) (actual time=38.235..38.236 rows=13 loops=1)

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

GroupAggregate (cost=72,078.76..73,180.81 rows=4,274 width=48) (actual time=38.214..38.225 rows=13 loops=1)

  • Group Key: faves_3.user_id
11. 0.000 38.182 ↑ 4,766.5 22 1

Sort (cost=72,078.76..72,340.92 rows=104,862 width=16) (actual time=38.180..38.182 rows=22 loops=1)

  • Sort Key: faves_3.user_id
  • Sort Method: quicksort Memory: 26kB
12. 38.273 38.390 ↑ 4,766.5 22 1

Gather (cost=1,077.08..63,334.25 rows=104,862 width=16) (actual time=0.459..38.390 rows=22 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
13. 0.004 0.117 ↑ 6,241.7 7 3 / 3

Nested Loop (cost=77.08..51,848.05 rows=43,692 width=16) (actual time=0.082..0.117 rows=7 loops=3)

14. 0.071 0.073 ↑ 966.7 3 3 / 3

Parallel Bitmap Heap Scan on faves faves_4 (cost=76.51..7,523.39 rows=2,900 width=8) (actual time=0.073..0.073 rows=3 loops=3)

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

Bitmap Index Scan on usr_id_idx (cost=0.00..74.77 rows=6,961 width=0) (actual time=0.005..0.005 rows=10 loops=1)

  • Index Cond: (user_id = 1)
16. 0.040 0.040 ↑ 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.009..0.012 rows=2 loops=10)

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

GroupAggregate (cost=71,156.37..73,215.01 rows=91,495 width=48) (actual time=461.147..610.132 rows=169,974 loops=1)

  • Group Key: faves.submission_id
18. 79.451 493.084 ↓ 1.9 173,409 1

Sort (cost=71,156.37..71,385.11 rows=91,495 width=16) (actual time=461.091..493.084 rows=173,409 loops=1)

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

Nested Loop (cost=0.79..63,616.54 rows=91,495 width=16) (actual time=365.742..413.633 rows=173,409 loops=1)

20. 0.024 365.716 ↑ 1.0 10 1

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

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

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

22. 31.450 31.450 ↓ 1.9 17,341 10

Index Scan using usr_id_idx on faves (cost=0.56..6,270.13 rows=9,149 width=16) (actual time=0.020..3.145 rows=17,341 loops=10)

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

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

24. 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.004..0.019 rows=15 loops=169,974)

  • Index Cond: (submission_id = faves.submission_id)
  • Heap Fetches: 2,525,187
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 : 0.504 ms