explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RlQB

Settings
# exclusive inclusive rows x rows loops node
1. 0.195 4,714.979 ↑ 225,298,080.0 3 1

GroupAggregate (cost=340,535,730.27..477,404,313.87 rows=675,894,240 width=559) (actual time=4,714.886..4,714.979 rows=3 loops=1)

  • Group Key: ed.sent_at, ed.project_id, em.title, (CASE WHEN (ed.project_id = 'fc'::text) THEN '48:00:00'::interval ELSE '72:00:00'::interval END), ((ed.sent_at + CASE WHEN (ed.project_id = 'fc'::text) THEN '48:00:00'::interval ELSE '72:00:00'::interval END)), ((((json_array_elements(((ed.related_segments)::json -> 'included'::text))))::text)::integer), s.title, pl.status, mde.player_jurisdiction, mde.player_language, mde.player_country
2. 0.271 4,714.784 ↑ 6,626,414.1 102 1

Sort (cost=340,535,730.27..342,225,465.87 rows=675,894,240 width=433) (actual time=4,714.779..4,714.784 rows=102 loops=1)

  • Sort Key: ed.sent_at, ed.project_id, em.title, (CASE WHEN (ed.project_id = 'fc'::text) THEN '48:00:00'::interval ELSE '72:00:00'::interval END), ((ed.sent_at + CASE WHEN (ed.project_id = 'fc'::text) THEN '48:00:00'::interval ELSE '72:00:00'::interval END)), ((((json_array_elements(((ed.related_segments)::json -> 'included'::text))))::text)::integer), s.title, pl.status, mde.player_jurisdiction, mde.player_language, mde.player_country
  • Sort Method: quicksort Memory: 77kB
3. 0.097 4,714.513 ↑ 6,626,414.1 102 1

Merge Join (cost=2,402,288.08..5,967,430.89 rows=675,894,240 width=433) (actual time=4,705.606..4,714.513 rows=102 loops=1)

  • Merge Cond: (s.id = ((((json_array_elements(((ed.related_segments)::json -> 'included'::text))))::text)::integer))
4. 0.000 4,712.031 ↑ 32,246.9 262 1

Nested Loop (cost=2,400,191.60..3,443,987.42 rows=8,448,678 width=338) (actual time=4,700.131..4,712.031 rows=262 loops=1)

5. 0.063 4,711.908 ↑ 8,061.7 131 1

Nested Loop Left Join (cost=2,400,191.04..3,338,368.50 rows=1,056,085 width=333) (actual time=4,700.081..4,711.908 rows=131 loops=1)

  • Join Filter: ((cte_orders.player_external_id = mde.external_player_id) AND (cte_orders.project_id = mde.project_id))
6. 0.071 4,711.845 ↑ 43.3 131 1

Nested Loop Left Join (cost=2,399,967.19..3,319,658.10 rows=5,678 width=245) (actual time=4,700.051..4,711.845 rows=131 loops=1)

  • Join Filter: ((cte_bets.player_external_id = mde.external_player_id) AND (cte_bets.project_id = mde.project_id))
7. 0.094 4,711.774 ↑ 4.8 131 1

Nested Loop Left Join (cost=2,393,678.25..3,313,190.46 rows=631 width=165) (actual time=4,700.012..4,711.774 rows=131 loops=1)

  • Join Filter: ((cte_gambling.project_id = mde.project_id) AND (cte_gambling.player_internal_id = (mde.external_player_id)::text))
8. 10.542 1,729.203 ↓ 3.7 131 1

Nested Loop (cost=1,000.27..916,607.01 rows=35 width=93) (actual time=1,717.472..1,729.203 rows=131 loops=1)

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

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

10. 4.616 1,717.680 ↓ 3.7 131 816

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

11. 4.056 1,713.064 ↓ 3.7 131 1

Gather (cost=1,000.00..916,137.34 rows=35 width=38) (actual time=0.857..1,713.064 rows=131 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 1,709.008 1,709.008 ↓ 2.9 44 3 / 3

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

  • Filter: ((project_id = 'fc'::text) AND (external_player_id = 2,430,694))
  • Rows Removed by Filter: 13,178,754
13. 0.000 2,982.477 ↓ 0.0 0 131

Materialize (cost=2,392,677.97..2,393,753.52 rows=3,605 width=82) (actual time=22.767..22.767 rows=0 loops=131)

14. 0.002 2,982.528 ↓ 0.0 0 1

Subquery Scan on cte_gambling (cost=2,392,677.97..2,393,735.50 rows=3,605 width=82) (actual time=2,982.528..2,982.528 rows=0 loops=1)

15. 0.000 2,982.526 ↓ 0.0 0 1

Finalize GroupAggregate (cost=2,392,677.97..2,393,699.45 rows=3,605 width=82) (actual time=2,982.526..2,982.526 rows=0 loops=1)

  • Group Key: csh_2.project_id, csh_2.player_internal_id, csh_2."time
16. 4.596 2,982.590 ↓ 0.0 0 1

Gather Merge (cost=2,392,677.97..2,393,519.20 rows=7,210 width=82) (actual time=2,982.525..2,982.590 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
17. 0.040 2,977.994 ↓ 0.0 0 3 / 3

Sort (cost=2,391,677.95..2,391,686.96 rows=3,605 width=82) (actual time=2,977.994..2,977.994 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
18. 0.024 2,977.954 ↓ 0.0 0 3 / 3

Partial HashAggregate (cost=2,391,410.89..2,391,464.97 rows=3,605 width=82) (actual time=2,977.954..2,977.954 rows=0 loops=3)

  • Group Key: csh_2.project_id, csh_2.player_internal_id, csh_2."time
19. 0.003 2,977.930 ↓ 0.0 0 3 / 3

Nested Loop (cost=0.41..2,379,431.23 rows=368,605 width=1,286) (actual time=2,977.929..2,977.930 rows=0 loops=3)

20. 0.006 2,977.927 ↓ 0.0 0 3 / 3

Parallel Append (cost=0.00..1,647,653.99 rows=15,023 width=32) (actual time=2,977.927..2,977.927 rows=0 loops=3)

21. 620.685 620.685 ↓ 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=32) (actual time=1,862.054..1,862.054 rows=0 loops=1)

  • Filter: ((project_id = 'fc'::text) AND ((player_internal_id)::bigint = 2,430,694))
  • Rows Removed by Filter: 3,354,019
22. 604.483 604.483 ↓ 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=32) (actual time=1,813.450..1,813.450 rows=0 loops=1)

  • Filter: ((project_id = 'fc'::text) AND ((player_internal_id)::bigint = 2,430,694))
  • Rows Removed by Filter: 3,409,733
23. 308.516 308.516 ↓ 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=32) (actual time=925.548..925.548 rows=0 loops=1)

  • Filter: ((project_id = 'fc'::text) AND ((player_internal_id)::bigint = 2,430,694))
  • Rows Removed by Filter: 3,145,328
24. 270.488 270.488 ↓ 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=32) (actual time=811.465..811.465 rows=0 loops=1)

  • Filter: ((project_id = 'fc'::text) AND ((player_internal_id)::bigint = 2,430,694))
  • Rows Removed by Filter: 3,032,152
25. 266.672 266.672 ↓ 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=32) (actual time=266.672..266.672 rows=0 loops=3)

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

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

  • Filter: ((project_id = 'fc'::text) AND ((player_internal_id)::bigint = 2,430,694))
  • Rows Removed by Filter: 3,259,728
27. 252.011 252.011 ↓ 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=32) (actual time=756.032..756.032 rows=0 loops=1)

  • Filter: ((project_id = 'fc'::text) AND ((player_internal_id)::bigint = 2,430,694))
  • Rows Removed by Filter: 2,963,896
28. 252.619 252.619 ↓ 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=32) (actual time=757.856..757.856 rows=0 loops=1)

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

Parallel Seq Scan on casino_stats_hourly_partition_2020_10 csh_8 (cost=0.00..91,203.25 rows=870 width=32) (actual time=403.576..403.576 rows=0 loops=1)

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

Index Scan using currency_rates_period_currency_excl on currency_rates cr (cost=0.41..42.73 rows=598 width=1,283) (never executed)

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

Materialize (cost=6,288.94..6,368.28 rows=9 width=91) (actual time=0.000..0.000 rows=0 loops=131)

32. 0.001 0.028 ↓ 0.0 0 1

Subquery Scan on cte_bets (cost=6,288.94..6,368.24 rows=9 width=91) (actual time=0.028..0.028 rows=0 loops=1)

33. 0.001 0.027 ↓ 0.0 0 1

GroupAggregate (cost=6,288.94..6,368.15 rows=9 width=123) (actual time=0.027..0.027 rows=0 loops=1)

  • Group Key: b.project_id, b.player_external_id, (date_trunc('hour'::text, b.created_at))
34. 0.008 0.026 ↓ 0.0 0 1

Sort (cost=6,288.94..6,292.89 rows=1,581 width=1,306) (actual time=0.026..0.026 rows=0 loops=1)

  • Sort Key: (date_trunc('hour'::text, b.created_at))
  • Sort Method: quicksort Memory: 25kB
35. 0.000 0.018 ↓ 0.0 0 1

Nested Loop (cost=0.84..6,204.94 rows=1,581 width=1,306) (actual time=0.018..0.018 rows=0 loops=1)

36. 0.018 0.018 ↓ 0.0 0 1

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

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

Index Scan using currency_rates_period_currency_excl on currency_rates cr_1 (cost=0.41..678.24 rows=598 width=1,283) (never executed)

  • Index Cond: ((period @> b.created_at) AND (currency = b.currency))
38. 0.000 0.000 ↓ 0.0 0 131

Materialize (cost=223.85..228.97 rows=186 width=99) (actual time=0.000..0.000 rows=0 loops=131)

39. 0.000 0.024 ↓ 0.0 0 1

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

40. 0.003 0.024 ↓ 0.0 0 1

HashAggregate (cost=223.85..226.18 rows=186 width=99) (actual time=0.024..0.024 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)
41. 0.002 0.021 ↓ 0.0 0 1

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

  • Group Key: exchanged_orders.player_external_id, exchanged_orders.project_id, date_trunc('hour'::text, exchanged_orders.status_updated_at)
42. 0.019 0.019 ↓ 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.018..0.019 rows=0 loops=1)

  • Index Cond: ((project_id = 'fc'::text) AND (player_external_id = 2,430,694))
43. 0.092 0.131 ↑ 4.0 2 131

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

44. 0.039 0.039 ↑ 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.035..0.039 rows=2 loops=1)

  • Index Cond: ((project_id = 'fc'::text) AND (player_external_id = 2,430,694))
  • Heap Fetches: 2
45. 0.326 2.385 ↑ 25.0 639 1

Sort (cost=2,096.48..2,136.48 rows=16,000 width=107) (actual time=2.356..2.385 rows=639 loops=1)

  • Sort Key: ((((json_array_elements(((ed.related_segments)::json -> 'included'::text))))::text)::integer)
  • Sort Method: quicksort Memory: 146kB
46. 0.162 2.059 ↑ 27.4 584 1

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

47. 0.638 1.897 ↑ 27.4 584 1

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

48. 0.119 1.259 ↑ 1.0 160 1

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

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

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

50. 0.582 1.047 ↑ 1.0 1,927 1

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

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

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

Planning time : 2.949 ms
Execution time : 4,715.953 ms