explain.depesz.com

PostgreSQL's explain analyze made readable

Result: q1VMa

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

Insert on player_running_calculations (cost=5,665,884.78..14,142,732.50 rows=84,667,783 width=128) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* (cost=5,665,884.78..14,142,732.50 rows=84,667,783 width=128) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Hash Join (cost=5,665,884.78..11,391,029.55 rows=84,667,783 width=326) (actual rows= loops=)

  • Hash Cond: (top50.user_id = s.user_id)
4.          

CTE top50

5. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=1,767,869.20..1,799,166.82 rows=130,129 width=6) (actual rows= loops=)

  • Group Key: sub1.user_id
6. 0.000 0.000 ↓ 0.0

Sort (cost=1,767,869.20..1,775,368.29 rows=2,999,633 width=6) (actual rows= loops=)

  • Sort Key: sub1.user_id
7. 0.000 0.000 ↓ 0.0

Subquery Scan on sub1 (cost=1,228,153.88..1,363,137.37 rows=2,999,633 width=6) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

WindowAgg (cost=1,228,153.88..1,333,141.04 rows=2,999,633 width=29) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Sort (cost=1,228,153.88..1,235,652.97 rows=2,999,633 width=27) (actual rows= loops=)

  • Sort Key: prt_1.type_id, (((prt_1.value)::double precision / bcr.conversion_rate)) DESC
10. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=19,089.38..761,906.05 rows=2,999,633 width=27) (actual rows= loops=)

  • Hash Cond: ((prt_1.user_id)::numeric = ua.userid)
11. 0.000 0.000 ↓ 0.0

Seq Scan on player_running_totals prt_1 (cost=0.00..625,416.84 rows=2,959,466 width=22) (actual rows= loops=)

  • Filter: (((type_id)::text = ANY ('{deposit,cash_hold}'::text[])) AND (last_update = '2020-09-09 00:00:00'::timestamp without time zone))
12. 0.000 0.000 ↓ 0.0

Hash (cost=16,492.39..16,492.39 rows=149,359 width=14) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=3,039.28..16,492.39 rows=149,359 width=14) (actual rows= loops=)

  • Hash Cond: ((ua.currency)::bpchar = bcr.currency)
14. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on user_accounts ua (cost=2,993.96..16,053.94 rows=149,359 width=10) (actual rows= loops=)

  • Recheck Cond: (typeid = '1'::numeric)
15. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_ua_typeid (cost=0.00..2,956.62 rows=149,359 width=0) (actual rows= loops=)

  • Index Cond: (typeid = '1'::numeric)
16. 0.000 0.000 ↓ 0.0

Hash (cost=25.70..25.70 rows=1,570 width=24) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Seq Scan on fixed_currency_rates bcr (cost=0.00..25.70 rows=1,570 width=24) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

CTE Scan on top50 (cost=0.00..2,602.58 rows=130,129 width=6) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Hash (cost=3,858,991.35..3,858,991.35 rows=130,129 width=356) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Subquery Scan on s (cost=13,657.33..3,858,991.35 rows=130,129 width=356) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=13,657.33..3,857,690.06 rows=130,129 width=356) (actual rows= loops=)

  • Group Key: prt.user_id
22. 0.000 0.000 ↓ 0.0

Merge Join (cost=13,657.33..2,940,843.70 rows=15,922,523 width=22) (actual rows= loops=)

  • Merge Cond: (prt.user_id = top50_1.user_id)
23. 0.000 0.000 ↓ 0.0

Index Scan using pk_player_running_totals on player_running_totals prt (cost=0.56..2,648,542.78 rows=15,922,523 width=22) (actual rows= loops=)

  • Filter: (last_update = '2020-09-09 00:00:00'::timestamp without time zone)
24. 0.000 0.000 ↓ 0.0

Sort (cost=13,656.77..13,982.09 rows=130,129 width=4) (actual rows= loops=)

  • Sort Key: top50_1.user_id
25. 0.000 0.000 ↓ 0.0

CTE Scan on top50 top50_1 (cost=0.00..2,602.58 rows=130,129 width=4) (actual rows= loops=)