explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3DA1

Settings
# exclusive inclusive rows x rows loops node
1. 1,820.442 47,452.343 ↑ 2.2 3,255,506 1

Hash Full Join (cost=2,512,426.53..3,895,405.57 rows=7,191,762 width=224) (actual time=35,394.945..47,452.343 rows=3,255,506 loops=1)

  • Hash Cond: ((COALESCE(COALESCE((traffic_stats.date)::timestamp without time zone, (date_trunc('week'::text, players.created_at))), t3.date) = (date_trunc('week'::text, withdraws.created_at))) AND (COALESCE(COALESCE(traffic_stats.project_id, player_versions.project_id), t3.project_id) = withdraws.project_id) AND (COALESCE(COALESCE(traffic_stats.pp_user_id, player_versions.pp_user_id), t3.pp_user_id) = withdraws.pp_user_id) AND (COALESCE(COALESCE(traffic_stats.refcode_id, player_versions.refcode_id), t3.refcode_id) = withdraws.refcode_id) AND (COALESCE(COALESCE((COALESCE(traffic_stats.subaccount_id, 0)), (COALESCE(players.subaccount_id, 0))), t3.subaccount_id) = (COALESCE(withdraws.subaccount_id, 0))) AND (COALESCE(COALESCE((COALESCE(traffic_stats.rotator_id, 0)), (COALESCE(players.rotator_id, 0))), t3.rotator_id) = (COALESCE(players_2.rotator_id, 0))) AND (COALESCE(COALESCE((COALESCE(traffic_stats.mirror_id, 0)), (COALESCE(players.mirror_id, 0))), t3.mirror_id) = (COALESCE(players_2.mirror_id, 0))))
2. 1,565.030 44,323.996 ↑ 2.2 3,255,230 1

Hash Full Join (cost=2,462,207.49..3,622,241.91 rows=7,191,762 width=210) (actual time=34,086.293..44,323.996 rows=3,255,230 loops=1)

  • Hash Cond: ((COALESCE((traffic_stats.date)::timestamp without time zone, (date_trunc('week'::text, players.created_at))) = t3.date) AND (COALESCE(traffic_stats.project_id, player_versions.project_id) = t3.project_id) AND (COALESCE(traffic_stats.pp_user_id, player_versions.pp_user_id) = t3.pp_user_id) AND (COALESCE(traffic_stats.refcode_id, player_versions.refcode_id) = t3.refcode_id) AND (COALESCE((COALESCE(traffic_stats.subaccount_id, 0)), (COALESCE(players.subaccount_id, 0))) = t3.subaccount_id) AND (COALESCE((COALESCE(traffic_stats.rotator_id, 0)), (COALESCE(players.rotator_id, 0))) = t3.rotator_id) AND (COALESCE((COALESCE(traffic_stats.mirror_id, 0)), (COALESCE(players.mirror_id, 0))) = t3.mirror_id))
3. 1,541.656 29,483.252 ↑ 2.2 3,217,459 1

Hash Full Join (cost=1,145,771.71..2,100,840.91 rows=7,191,762 width=91) (actual time=20,805.770..29,483.252 rows=3,217,459 loops=1)

  • Hash Cond: ((traffic_stats.project_id = player_versions.project_id) AND (traffic_stats.pp_user_id = player_versions.pp_user_id) AND (traffic_stats.refcode_id = player_versions.refcode_id) AND ((COALESCE(traffic_stats.subaccount_id, 0)) = (COALESCE(players.subaccount_id, 0))) AND ((COALESCE(traffic_stats.rotator_id, 0)) = (COALESCE(players.rotator_id, 0))) AND ((COALESCE(traffic_stats.mirror_id, 0)) = (COALESCE(players.mirror_id, 0))))
  • Join Filter: (traffic_stats.date = (date_trunc('week'::text, players.created_at)))
  • Rows Removed by Join Filter: 2886009
4. 1,715.887 21,274.722 ↑ 2.3 3,179,233 1

Finalize GroupAggregate (cost=915,046.07..1,595,030.38 rows=7,191,762 width=44) (actual time=14,138.174..21,274.722 rows=3,179,233 loops=1)

  • Group Key: traffic_stats.date, traffic_stats.project_id, traffic_stats.pp_user_id, traffic_stats.refcode_id, (COALESCE(traffic_stats.subaccount_id, 0)), (COALESCE(traffic_stats.rotator_id, 0)), (COALESCE(traffic_stats.mirror_id, 0))
5. 0.000 19,558.835 ↑ 1.9 6,002,733 1

Gather Merge (cost=915,046.07..1,292,499.00 rows=11,530,688 width=44) (actual time=14,138.126..19,558.835 rows=6,002,733 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
6. 3,288.230 75,897.735 ↑ 2.4 1,200,547 5

Partial GroupAggregate (cost=914,946.02..1,008,632.86 rows=2,882,672 width=44) (actual time=14,065.806..15,179.547 rows=1,200,547 loops=5)

  • Group Key: traffic_stats.date, traffic_stats.project_id, traffic_stats.pp_user_id, traffic_stats.refcode_id, (COALESCE(traffic_stats.subaccount_id, 0)), (COALESCE(traffic_stats.rotator_id, 0)), (COALESCE(traffic_stats.mirror_id, 0))
7. 23,061.530 72,609.505 ↑ 1.3 2,300,269 5

Sort (cost=914,946.02..922,152.70 rows=2,882,672 width=40) (actual time=14,065.767..14,521.901 rows=2,300,269 loops=5)

  • Sort Key: traffic_stats.date, traffic_stats.pp_user_id, traffic_stats.refcode_id, (COALESCE(traffic_stats.subaccount_id, 0)), (COALESCE(traffic_stats.rotator_id, 0)), (COALESCE(traffic_stats.mirror_id, 0))
  • Sort Method: external merge Disk: 122904kB
8. 49,547.975 49,547.975 ↑ 1.3 2,300,269 5

Parallel Seq Scan on traffic_stats (cost=0.00..601,033.28 rows=2,882,672 width=40) (actual time=0.126..9,909.595 rows=2,300,269 loops=5)

  • Filter: ((date >= '2009-08-08'::date) AND (date <= '2019-08-08'::date) AND (project_id = 4))
  • Rows Removed by Filter: 18540969
9. 16.801 6,666.874 ↑ 1.5 58,164 1

Hash (cost=228,551.81..228,551.81 rows=86,953 width=47) (actual time=6,666.874..6,666.874 rows=58,164 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5974kB
10. 241.496 6,650.073 ↑ 1.5 58,164 1

HashAggregate (cost=226,595.36..227,682.28 rows=86,953 width=47) (actual time=6,633.815..6,650.073 rows=58,164 loops=1)

  • Group Key: date_trunc('week'::text, players.created_at), player_versions.project_id, player_versions.pp_user_id, player_versions.refcode_id, COALESCE(players.subaccount_id, 0), COALESCE(players.rotator_id, 0), COALESCE(players.mirror_id, 0)
11. 185.980 6,408.577 ↓ 6.5 566,725 1

Hash Anti Join (cost=113,175.35..224,856.30 rows=86,953 width=39) (actual time=3,242.150..6,408.577 rows=566,725 loops=1)

  • Hash Cond: (players.id = player_debts.player_id)
  • Join Filter: (((players.created_at)::date >= (player_debts.opened_at)::date) AND ((player_debts.closed_at IS NULL) OR ((players.created_at)::date <= (player_debts.closed_at)::date)))
  • Rows Removed by Join Filter: 879
12. 1,989.874 6,220.094 ↓ 6.5 566,725 1

Hash Join (cost=112,965.46..222,026.71 rows=86,987 width=43) (actual time=3,239.577..6,220.094 rows=566,725 loops=1)

  • Hash Cond: (player_versions.master_id = players.id)
  • Join Filter: ((player_versions.valid_from <= players.created_at) AND (player_versions.valid_to > players.created_at))
  • Rows Removed by Join Filter: 1312183
13. 215.321 1,000.907 ↓ 1.2 1,889,740 1

Gather (cost=100.00..77,592.26 rows=1,568,220 width=39) (actual time=1.083..1,000.907 rows=1,889,740 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
14. 785.586 785.586 ↑ 1.0 377,948 5

Parallel Seq Scan on player_versions (cost=0.00..61,810.07 rows=392,055 width=39) (actual time=0.139..785.586 rows=377,948 loops=5)

  • Filter: (((expired_at IS NULL) OR (expired_at > '2019-08-08 09:32:36.543703'::timestamp without time zone)) AND (created_at <= '2019-08-08 09:32:36.543755'::timestamp without time zone) AND (project_id = 4))
  • Rows Removed by Filter: 1809311
15. 1,333.063 3,229.313 ↓ 2.0 5,068,889 1

Hash (cost=79,842.28..79,842.28 rows=2,523,559 width=24) (actual time=3,229.313..3,229.313 rows=5,068,889 loops=1)

  • Buckets: 2097152 (originally 2097152) Batches: 4 (originally 2) Memory Usage: 114689kB
16. 901.591 1,896.250 ↓ 2.0 5,068,889 1

Gather (cost=100.00..79,842.28 rows=2,523,559 width=24) (actual time=0.430..1,896.250 rows=5,068,889 loops=1)

  • Workers Planned: 4
  • Workers Launched: 3
17. 994.659 994.659 ↓ 2.0 1,267,222 4

Parallel Seq Scan on players (cost=0.00..54,506.69 rows=630,890 width=24) (actual time=0.026..994.659 rows=1,267,222 loops=4)

  • Filter: ((test IS NOT TRUE) AND (created_at >= '2009-08-08 00:00:00'::timestamp without time zone) AND (created_at <= '2019-08-08 23:59:59.999999'::timestamp without time zone) AND (zeroing = 'false'::text) AND (NOT COALESCE(((banned_from_pp_at)::date < (created_at)::date), false)))
  • Rows Removed by Filter: 1700
18. 1.029 2.503 ↑ 1.0 8,897 1

Hash (cost=98.67..98.67 rows=8,897 width=20) (actual time=2.503..2.503 rows=8,897 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 576kB
19. 1.474 1.474 ↑ 1.0 8,897 1

Seq Scan on player_debts (cost=0.00..98.67 rows=8,897 width=20) (actual time=0.011..1.474 rows=8,897 loops=1)

20. 62.483 13,275.714 ↑ 11.3 52,852 1

Hash (cost=1,300,049.69..1,300,049.69 rows=595,858 width=119) (actual time=13,275.714..13,275.714 rows=52,852 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 13922kB
21. 7.680 13,213.231 ↑ 11.3 52,852 1

Subquery Scan on t3 (cost=382,166.83..1,300,049.69 rows=595,858 width=119) (actual time=4,527.133..13,213.231 rows=52,852 loops=1)

22. 715.605 13,205.551 ↑ 11.3 52,852 1

GroupAggregate (cost=382,166.83..1,294,091.11 rows=595,858 width=119) (actual time=4,527.129..13,205.551 rows=52,852 loops=1)

  • Group Key: (date_trunc('week'::text, deposits.created_at)), deposits.project_id, deposits.pp_user_id, deposits.refcode_id, (COALESCE(deposits.subaccount_id, 0)), (COALESCE(players_1.rotator_id, 0)), (COALESCE(players_1.mirror_id, 0))
23. 1,072.729 4,724.196 ↓ 1.3 776,575 1

Sort (cost=382,166.83..383,656.47 rows=595,858 width=57) (actual time=4,526.628..4,724.196 rows=776,575 loops=1)

  • Sort Key: (date_trunc('week'::text, deposits.created_at)), deposits.pp_user_id, deposits.refcode_id, (COALESCE(deposits.subaccount_id, 0)), (COALESCE(players_1.rotator_id, 0)), (COALESCE(players_1.mirror_id, 0))
  • Sort Method: quicksort Memory: 127931kB
24. 0.000 3,651.467 ↓ 1.3 776,575 1

Gather (cost=310.31..325,010.31 rows=595,858 width=57) (actual time=3.522..3,651.467 rows=776,575 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
25. 1,263.161 3,738.887 ↓ 1.0 155,315 5

Nested Loop (cost=210.31..317,834.50 rows=148,964 width=57) (actual time=2.806..3,738.887 rows=155,315 loops=5)

26. 86.127 2,475.722 ↑ 1.1 266,619 5

Hash Anti Join (cost=209.88..170,335.89 rows=298,396 width=41) (actual time=2.775..2,475.722 rows=266,619 loops=5)

  • Hash Cond: (deposits.player_id = player_debts_1.player_id)
  • Join Filter: (((deposits.created_at)::date >= (player_debts_1.opened_at)::date) AND ((player_debts_1.closed_at IS NULL) OR ((deposits.created_at)::date <= (player_debts_1.closed_at)::date)))
  • Rows Removed by Join Filter: 27129
27. 2,386.991 2,386.991 ↑ 1.2 268,213 5

Parallel Seq Scan on deposits (cost=0.00..161,752.20 rows=315,811 width=41) (actual time=0.043..2,386.991 rows=268,213 loops=5)

  • Filter: ((created_at >= '2009-08-08 00:00:00'::timestamp without time zone) AND (created_at <= '2019-08-08 23:59:59.999999'::timestamp without time zone) AND (project_id = 4))
  • Rows Removed by Filter: 2154887
28. 1.315 2.604 ↑ 1.0 8,897 5

Hash (cost=98.67..98.67 rows=8,897 width=20) (actual time=2.604..2.604 rows=8,897 loops=5)

  • Buckets: 16384 Batches: 1 Memory Usage: 576kB
29. 1.289 1.289 ↑ 1.0 8,897 5

Seq Scan on player_debts player_debts_1 (cost=0.00..98.67 rows=8,897 width=20) (actual time=0.008..1.289 rows=8,897 loops=5)

30. 0.004 0.004 ↑ 1.0 1 1,333,096

Index Scan using players_pkey on players players_1 (cost=0.43..0.48 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=1,333,096)

  • Index Cond: (id = deposits.player_id)
  • Filter: ((test IS NOT TRUE) AND (zeroing = 'false'::text) AND (NOT COALESCE(((banned_from_pp_at)::date < (deposits.created_at)::date), false)))
  • Rows Removed by Filter: 0
31.          

SubPlan (forGroupAggregate)

32. 5,436.025 5,436.025 ↑ 104.0 1 776,575

Index Scan using deposits_player_created_at_idx on deposits d_1 (cost=0.43..32.31 rows=104 width=0) (actual time=0.007..0.007 rows=1 loops=776,575)

  • Index Cond: (player_id = deposits.player_id)
  • Filter: (created_at < deposits.created_at)
  • Rows Removed by Filter: 1
33. 2,329.725 2,329.725 ↑ 104.0 1 776,575

Index Scan using deposits_player_created_at_idx on deposits d (cost=0.43..32.31 rows=104 width=0) (actual time=0.003..0.003 rows=1 loops=776,575)

  • Index Cond: (player_id = deposits.player_id)
  • Filter: (created_at < deposits.created_at)
  • Rows Removed by Filter: 1
34. 9.316 1,307.905 ↑ 3.5 21,005 1

Hash (cost=48,175.38..48,175.38 rows=74,315 width=79) (actual time=1,307.905..1,307.905 rows=21,005 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 2972kB
35. 76.925 1,298.589 ↑ 3.5 21,005 1

HashAggregate (cost=46,317.51..47,432.23 rows=74,315 width=79) (actual time=1,286.344..1,298.589 rows=21,005 loops=1)

  • Group Key: (date_trunc('week'::text, withdraws.created_at)), withdraws.project_id, withdraws.pp_user_id, withdraws.refcode_id, (COALESCE(withdraws.subaccount_id, 0)), (COALESCE(players_2.rotator_id, 0)), (COALESCE(players_2.mirror_id, 0))
36. 0.000 1,221.664 ↓ 1.3 93,357 1

Gather (cost=310.31..44,645.42 rows=74,315 width=45) (actual time=29.118..1,221.664 rows=93,357 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
37. 600.133 1,252.810 ↓ 1.0 18,671 5

Nested Loop (cost=210.31..43,662.93 rows=18,579 width=45) (actual time=28.973..1,252.810 rows=18,671 loops=5)

38. 13.469 652.660 ↑ 1.1 33,930 5

Hash Anti Join (cost=209.88..22,174.94 rows=37,216 width=41) (actual time=24.492..652.660 rows=33,930 loops=5)

  • Hash Cond: (withdraws.player_id = player_debts_2.player_id)
  • Join Filter: (((withdraws.created_at)::date >= (player_debts_2.opened_at)::date) AND ((player_debts_2.closed_at IS NULL) OR ((withdraws.created_at)::date <= (player_debts_2.closed_at)::date)))
  • Rows Removed by Join Filter: 4240
39. 636.624 636.624 ↑ 1.2 34,173 5

Parallel Seq Scan on withdraws (cost=0.00..20,916.67 rows=39,922 width=41) (actual time=21.770..636.624 rows=34,173 loops=5)

  • Filter: ((created_at >= '2009-08-08 00:00:00'::timestamp without time zone) AND (created_at <= '2019-08-08 23:59:59.999999'::timestamp without time zone) AND (project_id = 4))
  • Rows Removed by Filter: 270531
40. 1.309 2.567 ↑ 1.0 8,897 5

Hash (cost=98.67..98.67 rows=8,897 width=20) (actual time=2.567..2.567 rows=8,897 loops=5)

  • Buckets: 16384 Batches: 1 Memory Usage: 576kB
41. 1.258 1.258 ↑ 1.0 8,897 5

Seq Scan on player_debts player_debts_2 (cost=0.00..98.67 rows=8,897 width=20) (actual time=0.015..1.258 rows=8,897 loops=5)

42. 0.017 0.017 ↑ 1.0 1 169,651

Index Scan using players_pkey on players players_2 (cost=0.43..0.57 rows=1 width=20) (actual time=0.017..0.017 rows=1 loops=169,651)

  • Index Cond: (id = withdraws.player_id)
  • Filter: ((test IS NOT TRUE) AND (zeroing = 'false'::text) AND (NOT COALESCE(((banned_from_pp_at)::date < (withdraws.created_at)::date), false)))
  • Rows Removed by Filter: 0
Planning time : 19.182 ms
Execution time : 47,643.427 ms