explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6UDk

Settings
# exclusive inclusive rows x rows loops node
1. 13.364 4,186.129 ↑ 6,628,252.2 102 1

Hash Join (cost=3,210,439.05..5,194,844.61 rows=676,081,728 width=175) (actual time=4,177.987..4,186.129 rows=102 loops=1)

  • Hash Cond: (((((json_array_elements(((ed.related_segments)::json -> 'included'::text))))::text)::integer) = s.id)
2. 0.226 2.102 ↑ 27.4 584 1

Result (cost=113.96..819.22 rows=16,000 width=107) (actual time=1.003..2.102 rows=584 loops=1)

3. 0.667 1.876 ↑ 27.4 584 1

ProjectSet (cost=113.96..219.22 rows=16,000 width=111) (actual time=0.998..1.876 rows=584 loops=1)

4. 0.146 1.209 ↑ 1.0 160 1

Hash Join (cost=113.96..136.82 rows=160 width=210) (actual time=0.979..1.209 rows=160 loops=1)

  • Hash Cond: (em.email_delivery_id = ed.id)
5. 0.102 0.102 ↑ 1.0 701 1

Seq Scan on email_distributions em (cost=0.00..21.01 rows=701 width=76) (actual time=0.009..0.102 rows=701 loops=1)

6. 0.463 0.961 ↑ 1.0 1,927 1

Hash (cost=89.54..89.54 rows=1,954 width=146) (actual time=0.961..0.961 rows=1,927 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 352kB
7. 0.498 0.498 ↑ 1.0 1,927 1

Seq Scan on email_deliveries ed (cost=0.00..89.54 rows=1,954 width=146) (actual time=0.006..0.498 rows=1,927 loops=1)

8. 0.250 4,170.663 ↑ 32,255.8 262 1

Hash (cost=2,997,398.31..2,997,398.31 rows=8,451,022 width=80) (actual time=4,170.663..4,170.663 rows=262 loops=1)

  • Buckets: 262,144 Batches: 128 Memory Usage: 2,048kB
9. 0.062 4,170.413 ↑ 32,255.8 262 1

Hash Left Join (cost=1,992,446.07..2,997,398.31 rows=8,451,022 width=80) (actual time=4,160.307..4,170.413 rows=262 loops=1)

  • Hash Cond: ((mde.external_player_id = cte_orders.player_external_id) AND (mde.project_id = cte_orders.project_id))
10. 0.091 4,170.332 ↑ 173.4 262 1

Nested Loop (cost=1,992,215.24..2,912,315.75 rows=45,436 width=91) (actual time=4,160.275..4,170.332 rows=262 loops=1)

11. 0.050 4,170.241 ↑ 43.4 131 1

Nested Loop Left Join (cost=1,992,214.68..2,911,737.43 rows=5,679 width=83) (actual time=4,160.253..4,170.241 rows=131 loops=1)

  • Join Filter: ((cte_bets.player_external_id = mde.external_player_id) AND (cte_bets.project_id = mde.project_id))
12. 0.000 4,170.191 ↑ 4.8 131 1

Nested Loop Left Join (cost=1,991,487.71..2,910,910.86 rows=631 width=83) (actual time=4,160.231..4,170.191 rows=131 loops=1)

  • Join Filter: ((cte_gambling.project_id = mde.project_id) AND (cte_gambling.player_internal_id = (mde.external_player_id)::text))
13. 8.200 1,737.665 ↓ 3.7 131 1

Nested Loop (cost=1,000.27..916,607.01 rows=35 width=83) (actual time=1,727.740..1,737.665 rows=131 loops=1)

  • Join Filter: (s.id = mde.segment_id)
  • Rows Removed by Join Filter: 106,765
14. 0.361 0.361 ↓ 1.0 816 1

Index Scan using segments_pkey on segments s (cost=0.28..43.81 rows=811 width=55) (actual time=0.012..0.361 rows=816 loops=1)

15. 4.550 1,729.104 ↓ 3.7 131 816

Materialize (cost=1,000.00..916,137.51 rows=35 width=28) (actual time=0.001..2.119 rows=131 loops=816)

16. 3.706 1,724.554 ↓ 3.7 131 1

Gather (cost=1,000.00..916,137.34 rows=35 width=28) (actual time=0.457..1,724.554 rows=131 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
17. 1,720.848 1,720.848 ↓ 2.9 44 3 / 3

Parallel Seq Scan on marketing_email_distribution_events mde (cost=0.00..915,133.84 rows=15 width=28) (actual time=5.206..1,720.848 rows=44 loops=3)

  • Filter: ((project_id = 'fc'::text) AND (external_player_id = 2,430,694))
  • Rows Removed by Filter: 13,178,787
18. 0.055 2,432.539 ↓ 0.0 0 131

Materialize (cost=1,990,487.44..1,991,473.14 rows=3,606 width=10) (actual time=18.569..18.569 rows=0 loops=131)

19. 0.000 2,432.484 ↓ 0.0 0 1

Subquery Scan on cte_gambling (cost=1,990,487.44..1,991,455.11 rows=3,606 width=10) (actual time=2,432.484..2,432.484 rows=0 loops=1)

20. 0.000 2,432.484 ↓ 0.0 0 1

Finalize GroupAggregate (cost=1,990,487.44..1,991,419.05 rows=3,606 width=82) (actual time=2,432.484..2,432.484 rows=0 loops=1)

  • Group Key: csh_2.project_id, csh_2.player_internal_id, csh_2."time
21. 4.240 2,432.537 ↓ 0.0 0 1

Gather Merge (cost=1,990,487.44..1,991,328.90 rows=7,212 width=18) (actual time=2,432.481..2,432.537 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
22. 0.046 2,428.297 ↓ 0.0 0 3 / 3

Sort (cost=1,989,487.42..1,989,496.43 rows=3,606 width=18) (actual time=2,428.297..2,428.297 rows=0 loops=3)

  • Sort Key: csh_2.player_internal_id, csh_2."time
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
23. 0.024 2,428.251 ↓ 0.0 0 3 / 3

Partial HashAggregate (cost=1,989,238.31..1,989,274.37 rows=3,606 width=18) (actual time=2,428.251..2,428.251 rows=0 loops=3)

  • Group Key: csh_2.project_id, csh_2.player_internal_id, csh_2."time
24. 0.002 2,428.227 ↓ 0.0 0 3 / 3

Nested Loop (cost=0.41..1,986,473.45 rows=368,648 width=18) (actual time=2,428.227..2,428.227 rows=0 loops=3)

25. 0.007 2,428.225 ↓ 0.0 0 3 / 3

Parallel Append (cost=0.00..1,647,741.62 rows=15,023 width=22) (actual time=2,428.225..2,428.225 rows=0 loops=3)

26. 309.269 309.269 ↓ 0.0 0 1 / 3

Parallel Seq Scan on casino_stats_hourly_partition_2020_04 csh_2 (cost=0.00..244,980.85 rows=1,878 width=22) (actual time=927.807..927.807 rows=0 loops=1)

  • Filter: ((project_id = 'fc'::text) AND ((player_internal_id)::bigint = 2,430,694))
  • Rows Removed by Filter: 3,354,019
27. 294.227 294.227 ↓ 0.0 0 1 / 3

Parallel Seq Scan on casino_stats_hourly_partition_2020_03 csh_1 (cost=0.00..228,238.12 rows=2,027 width=22) (actual time=882.680..882.680 rows=0 loops=1)

  • Filter: ((project_id = 'fc'::text) AND ((player_internal_id)::bigint = 2,430,694))
  • Rows Removed by Filter: 3,409,733
28. 271.892 271.892 ↓ 0.0 0 1 / 3

Parallel Seq Scan on casino_stats_hourly_partition_2020_05 csh_3 (cost=0.00..188,568.40 rows=1,691 width=22) (actual time=815.677..815.677 rows=0 loops=1)

  • Filter: ((project_id = 'fc'::text) AND ((player_internal_id)::bigint = 2,430,694))
  • Rows Removed by Filter: 3,145,328
29. 304.536 304.536 ↓ 0.0 0 1 / 3

Parallel Seq Scan on casino_stats_hourly_partition_2020_06 csh_4 (cost=0.00..184,244.10 rows=1,696 width=22) (actual time=913.606..913.607 rows=0 loops=1)

  • Filter: ((project_id = 'fc'::text) AND ((player_internal_id)::bigint = 2,430,694))
  • Rows Removed by Filter: 3,032,152
30. 290.667 290.667 ↓ 0.0 0 3 / 3

Parallel Seq Scan on casino_stats_hourly_partition_2020_07 csh_5 (cost=0.00..182,264.04 rows=1,675 width=22) (actual time=290.667..290.667 rows=0 loops=3)

  • Filter: ((project_id = 'fc'::text) AND ((player_internal_id)::bigint = 2,430,694))
  • Rows Removed by Filter: 1,009,051
31. 299.572 299.572 ↓ 0.0 0 2 / 3

Parallel Seq Scan on casino_stats_hourly_partition_2020_02 csh (cost=0.00..182,228.40 rows=1,919 width=22) (actual time=449.358..449.358 rows=0 loops=2)

  • Filter: ((project_id = 'fc'::text) AND ((player_internal_id)::bigint = 2,430,694))
  • Rows Removed by Filter: 1,629,864
32. 255.765 255.765 ↓ 0.0 0 1 / 3

Parallel Seq Scan on casino_stats_hourly_partition_2020_08 csh_6 (cost=0.00..178,177.47 rows=1,695 width=22) (actual time=767.296..767.296 rows=0 loops=1)

  • Filter: ((project_id = 'fc'::text) AND ((player_internal_id)::bigint = 2,430,694))
  • Rows Removed by Filter: 2,963,896
33. 262.665 262.665 ↓ 0.0 0 1 / 3

Parallel Seq Scan on casino_stats_hourly_partition_2020_09 csh_7 (cost=0.00..167,674.24 rows=1,572 width=22) (actual time=787.996..787.996 rows=0 loops=1)

  • Filter: ((project_id = 'fc'::text) AND ((player_internal_id)::bigint = 2,430,694))
  • Rows Removed by Filter: 2,933,133
34. 139.625 139.625 ↓ 0.0 0 1 / 3

Parallel Seq Scan on casino_stats_hourly_partition_2020_10 csh_8 (cost=0.00..91,290.88 rows=870 width=22) (actual time=418.874..418.874 rows=0 loops=1)

  • Filter: ((project_id = 'fc'::text) AND ((player_internal_id)::bigint = 2,430,694))
  • Rows Removed by Filter: 1,574,301
35. 0.000 0.000 ↓ 0.0 0 / 3

Index Only Scan using currency_rates_period_currency_excl on currency_rates cr (cost=0.41..16.56 rows=599 width=25) (never executed)

  • Index Cond: ((period @> csh_2."time") AND (currency = csh_2.currency))
  • Heap Fetches: 0
36. 0.000 0.000 ↓ 0.0 0 131

Materialize (cost=726.96..727.21 rows=9 width=11) (actual time=0.000..0.000 rows=0 loops=131)

37. 0.001 0.017 ↓ 0.0 0 1

Subquery Scan on cte_bets (cost=726.96..727.17 rows=9 width=11) (actual time=0.017..0.017 rows=0 loops=1)

38. 0.002 0.016 ↓ 0.0 0 1

HashAggregate (cost=726.96..727.08 rows=9 width=123) (actual time=0.016..0.016 rows=0 loops=1)

  • Group Key: b.project_id, b.player_external_id, date_trunc('hour'::text, b.created_at)
39. 0.000 0.014 ↓ 0.0 0 1

Nested Loop (cost=0.84..715.11 rows=1,581 width=19) (actual time=0.014..0.014 rows=0 loops=1)

40. 0.014 0.014 ↓ 0.0 0 1

Index Scan using betting_betslips_player_external_id_index on betslips b (cost=0.42..42.99 rows=9 width=23) (actual time=0.014..0.014 rows=0 loops=1)

  • Index Cond: (player_external_id = 2,430,694)
  • Filter: ((status = ANY ('{win,lost}'::text[])) AND (project_id = 'fc'::text))
41. 0.000 0.000 ↓ 0.0 0

Index Only Scan using currency_rates_period_currency_excl on currency_rates cr_1 (cost=0.41..68.25 rows=599 width=25) (never executed)

  • Index Cond: ((period @> b.created_at) AND (currency = b.currency))
  • Heap Fetches: 0
42. 0.000 0.000 ↑ 4.0 2 131

Materialize (cost=0.56..10.44 rows=8 width=19) (actual time=0.000..0.000 rows=2 loops=131)

43. 0.021 0.021 ↑ 4.0 2 1

Index Only Scan using hydra_player_lifecycles_project_id_player_id_status_calculated_ on player_lifecycles pl (cost=0.56..10.40 rows=8 width=19) (actual time=0.018..0.021 rows=2 loops=1)

  • Index Cond: ((project_id = 'fc'::text) AND (player_external_id = 2,430,694))
  • Heap Fetches: 2
44. 0.001 0.019 ↓ 0.0 0 1

Hash (cost=228.04..228.04 rows=186 width=11) (actual time=0.019..0.019 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
45. 0.001 0.018 ↓ 0.0 0 1

Subquery Scan on cte_orders (cost=223.85..228.04 rows=186 width=11) (actual time=0.018..0.018 rows=0 loops=1)

46. 0.002 0.017 ↓ 0.0 0 1

HashAggregate (cost=223.85..226.18 rows=186 width=99) (actual time=0.017..0.017 rows=0 loops=1)

  • Group Key: exchanged_orders.player_external_id, exchanged_orders.project_id, (date_trunc('hour'::text, exchanged_orders.status_updated_at)), COALESCE(count(CASE WHEN ((exchanged_orders.type = 'deposit'::text) AND (exchanged_orders.status = 'success'::text) AND (exchanged_orders.payment_type = 'normal'::text)) THEN exchanged_orders."amount_USD" ELSE NULL::numeric END), '0'::bigint), COALESCE(sum(CASE WHEN ((exchanged_orders.type = 'deposit'::text) AND (exchanged_orders.status = 'success'::text) AND (exchanged_orders.payment_type = 'normal'::text)) THEN exchanged_orders."amount_USD" ELSE NULL::numeric END), '0'::numeric), COALESCE(sum(CASE WHEN ((exchanged_orders.type = 'withdraw'::text) AND (exchanged_orders.status = 'success'::text) AND (exchanged_orders.payment_type = 'normal'::text)) THEN exchanged_orders."amount_USD" ELSE NULL::numeric END), '0'::numeric), COALESCE(count(CASE WHEN ((exchanged_orders.type = 'withdraw'::text) AND (exchanged_orders.status = 'success'::text) AND (exchanged_orders.payment_type = 'normal'::text)) THEN exchanged_orders."amount_USD" ELSE NULL::numeric END), '0'::bigint)
47. 0.003 0.015 ↓ 0.0 0 1

HashAggregate (cost=217.34..220.60 rows=186 width=99) (actual time=0.015..0.015 rows=0 loops=1)

  • Group Key: exchanged_orders.player_external_id, exchanged_orders.project_id, date_trunc('hour'::text, exchanged_orders.status_updated_at)
48. 0.012 0.012 ↓ 0.0 0 1

Index Scan using unetsafe_exchanged_orders_project_id on exchanged_orders (cost=0.56..208.51 rows=186 width=52) (actual time=0.012..0.012 rows=0 loops=1)

  • Index Cond: ((project_id = 'fc'::text) AND (player_external_id = 2,430,694))
Planning time : 2.428 ms
Execution time : 4,186.528 ms