explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FZ5W

Settings
# exclusive inclusive rows x rows loops node
1. 308.771 82,424.032 ↑ 1.0 500 1

Limit (cost=141,528,692.95..141,528,694.20 rows=500 width=64) (actual time=82,423.960..82,424.032 rows=500 loops=1)

  • JIT:
  • Functions: 40
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 3.161 ms, Inlining 58.906 ms, Optimization 156.142 ms, Emission 93.418 ms, Total 311.627 ms
2. 831.211 82,115.261 ↑ 5,211.9 500 1

Sort (cost=141,528,692.95..141,535,207.79 rows=2,605,934 width=64) (actual time=82,115.222..82,115.261 rows=500 loops=1)

  • Sort Key: (((((count(*)))::numeric / ((count(*)))::numeric) * (SubPlan 1))) DESC
  • Sort Method: top-N heapsort Memory: 145kB
3. 3,273.425 81,284.050 ↑ 1.0 2,534,532 1

Nested Loop Left Join (cost=2,091,956.99..141,398,842.07 rows=2,605,934 width=64) (actual time=5,624.657..81,284.050 rows=2,534,532 loops=1)

4. 804.896 7,043.729 ↑ 1.0 2,534,532 1

Hash Anti Join (cost=2,091,940.76..2,157,629.33 rows=2,605,934 width=48) (actual time=5,623.136..7,043.729 rows=2,534,532 loops=1)

  • Hash Cond: (faves_1.submission_id = faves.submission_id)
5. 1,296.850 6,238.572 ↑ 1.0 2,534,993 1

Sort (cost=2,086,922.28..2,093,454.28 rows=2,612,797 width=48) (actual time=5,622.848..6,238.572 rows=2,534,993 loops=1)

  • Sort Key: (count(*)) DESC
  • Sort Method: external merge Disk: 151,264kB
6. 1,545.765 4,941.722 ↑ 1.0 2,534,993 1

GroupAggregate (cost=1,616,694.46..1,761,356.93 rows=2,612,797 width=48) (actual time=2,656.360..4,941.722 rows=2,534,993 loops=1)

  • Group Key: faves_1.submission_id
7. 2,104.032 3,395.957 ↑ 2.5 4,421,650 1

Sort (cost=1,616,694.46..1,644,695.09 rows=11,200,250 width=16) (actual time=2,656.320..3,395.957 rows=4,421,650 loops=1)

  • Sort Key: faves_1.submission_id
  • Sort Method: external merge Disk: 112,568kB
8. 393.797 1,291.925 ↑ 2.5 4,421,650 1

Nested Loop (cost=25.57..193,199.20 rows=11,200,250 width=16) (actual time=0.194..1,291.925 rows=4,421,650 loops=1)

9. 0.402 0.428 ↑ 2.0 100 1

HashAggregate (cost=25.00..27.00 rows=200 width=8) (actual time=0.055..0.428 rows=100 loops=1)

  • Group Key: affinity_scores.user_id
10. 0.026 0.026 ↑ 12.0 100 1

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

11. 897.700 897.700 ↓ 4.7 44,216 100

Index Scan using usr_id_idx on faves faves_1 (cost=0.56..872.52 rows=9,334 width=16) (actual time=0.038..8.977 rows=44,216 loops=100)

  • Index Cond: (user_id = affinity_scores.user_id)
12. 0.082 0.261 ↑ 10.0 685 1

Hash (cost=4,932.69..4,932.69 rows=6,863 width=8) (actual time=0.261..0.261 rows=685 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 91kB
13. 0.179 0.179 ↑ 10.0 685 1

Index Scan using usr_id_idx on faves (cost=0.56..4,932.69 rows=6,863 width=8) (actual time=0.061..0.179 rows=685 loops=1)

  • Index Cond: (user_id = 1,003,706)
14. 2,534.532 38,017.980 ↑ 1.0 1 2,534,532

Aggregate (cost=16.22..16.23 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=2,534,532)

15. 35,483.448 35,483.448 ↑ 1.9 7 2,534,532

Index Only Scan using subm_id_idx on faves faves_2 (cost=0.56..16.19 rows=13 width=0) (actual time=0.004..0.014 rows=7 loops=2,534,532)

  • Index Cond: (submission_id = faves_1.submission_id)
  • Heap Fetches: 18,634,977
16.          

SubPlan (for Nested Loop Left Join)

17. 2,534.532 32,948.916 ↑ 1.0 1 2,534,532

Aggregate (cost=37.15..37.16 rows=1 width=32) (actual time=0.013..0.013 rows=1 loops=2,534,532)

18. 30,414.384 30,414.384 ↑ 29.5 2 2,534,532

Seq Scan on affinity_scores affinity_scores_1 (cost=0.00..37.00 rows=59 width=32) (actual time=0.005..0.012 rows=2 loops=2,534,532)

  • Filter: (user_id = ANY ((array_agg(faves_1.user_id))))
  • Rows Removed by Filter: 98
Planning time : 3.356 ms
Execution time : 82,457.174 ms