explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 60sq

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

Hash Join (cost=19,011.89..19,955.76 rows=4 width=399) (actual rows= loops=)

  • Hash Cond: (cd.super_site_id = vars._ssd)
2.          

CTE vars

3. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=12) (actual rows= loops=)

4.          

CTE sportsbook

5. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=6,183.34..6,193.21 rows=141 width=184) (actual rows= loops=)

  • Group Key: bfu.customer_id, bfu.currency
6. 0.000 0.000 ↓ 0.0

Sort (cost=6,183.34..6,183.69 rows=141 width=29) (actual rows= loops=)

  • Sort Key: bfu.customer_id, bfu.currency
7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=137.43..6,178.30 rows=141 width=29) (actual rows= loops=)

  • Join Filter: (vars_1._ssd = customer_dimension.super_site_id)
8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=137.01..1,471.53 rows=8,460 width=33) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

CTE Scan on vars vars_1 (cost=0.00..0.02 rows=1 width=12) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on bet_fact_unsplit bfu (cost=137.01..1,386.91 rows=8,460 width=33) (actual rows= loops=)

  • Recheck Cond: ((date_settled_id >= vars_1._from) AND (date_settled_id <= vars_1._to))
11. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on bet_fact_date_settled_id_idx (cost=0.00..134.89 rows=8,460 width=0) (actual rows= loops=)

  • Index Cond: ((date_settled_id >= vars_1._from) AND (date_settled_id <= vars_1._to))
12. 0.000 0.000 ↓ 0.0

Index Scan using customer_dimension_pkey on customer_dimension (cost=0.42..0.54 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (customer_id = bfu.customer_id)
13.          

CTE casino

14. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=1,274.93..1,275.37 rows=11 width=132) (actual rows= loops=)

  • Group Key: fct_play_summary.customer_id
15. 0.000 0.000 ↓ 0.0

Sort (cost=1,274.93..1,274.96 rows=11 width=18) (actual rows= loops=)

  • Sort Key: fct_play_summary.customer_id
16. 0.000 0.000 ↓ 0.0

Nested Loop (cost=107.74..1,274.74 rows=11 width=18) (actual rows= loops=)

  • Join Filter: (vars_2._ssd = customer_dimension_1.super_site_id)
17. 0.000 0.000 ↓ 0.0

Nested Loop (cost=107.32..196.14 rows=632 width=22) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

CTE Scan on vars vars_2 (cost=0.00..0.02 rows=1 width=12) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on fct_play_summary (cost=107.32..189.80 rows=632 width=22) (actual rows= loops=)

  • Recheck Cond: ((date_id >= vars_2._from) AND (date_id <= vars_2._to))
20. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on fct_play_summary_pkey (cost=0.00..107.16 rows=632 width=0) (actual rows= loops=)

  • Index Cond: ((date_id >= vars_2._from) AND (date_id <= vars_2._to))
21. 0.000 0.000 ↓ 0.0

Index Scan using customer_dimension_pkey on customer_dimension customer_dimension_1 (cost=0.42..1.69 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (customer_id = fct_play_summary.customer_id)
22.          

CTE accounts

23. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=6,112.41..6,113.26 rows=20 width=132) (actual rows= loops=)

  • Group Key: cd_1.customer_id
24. 0.000 0.000 ↓ 0.0

Sort (cost=6,112.41..6,112.46 rows=20 width=17) (actual rows= loops=)

  • Sort Key: cd_1.customer_id
25. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.32..6,111.98 rows=20 width=17) (actual rows= loops=)

  • Join Filter: ((caf.date_id >= vars_3._from) AND (caf.date_id <= vars_3._to))
26. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.03..5,413.19 rows=1,901 width=19) (actual rows= loops=)

  • Hash Cond: (cd_1.super_site_id = vars_3._ssd)
27. 0.000 0.000 ↓ 0.0

Seq Scan on customer_dimension cd_1 (cost=0.00..4,966.47 rows=114,047 width=15) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Hash (cost=0.02..0.02 rows=1 width=12) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

CTE Scan on vars vars_3 (cost=0.00..0.02 rows=1 width=12) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Index Scan using customer_account_fact_pkey on customer_account_fact caf (cost=0.29..0.34 rows=2 width=24) (actual rows= loops=)

  • Index Cond: ((customer_account_key)::text = (cd_1.customer_account_key)::text)
31.          

CTE newregistration

32. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.03..5,422.69 rows=211 width=4) (actual rows= loops=)

  • Hash Cond: (customer_dimension_2.super_site_id = vars_4._ssd)
  • Join Filter: ((customer_dimension_2.created_on >= vars_4._from) AND (customer_dimension_2.created_on <= vars_4._to))
33. 0.000 0.000 ↓ 0.0

Seq Scan on customer_dimension customer_dimension_2 (cost=0.00..4,966.47 rows=114,047 width=12) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Hash (cost=0.02..0.02 rows=1 width=12) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

CTE Scan on vars vars_4 (cost=0.00..0.02 rows=1 width=12) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Nested Loop (cost=7.31..950.28 rows=210 width=501) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Hash Full Join (cost=6.90..16.83 rows=210 width=408) (actual rows= loops=)

  • Hash Cond: (s4.customer_id = COALESCE(COALESCE(s1.customer_id, s2.customer_id), s3.customer_id))
  • Filter: (COALESCE(COALESCE(COALESCE(s1.customer_id, s2.customer_id), s3.customer_id), s4.customer_id) IS NOT NULL)
38. 0.000 0.000 ↓ 0.0

CTE Scan on newregistration s4 (cost=0.00..4.22 rows=211 width=4) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Hash (cost=5.13..5.13 rows=141 width=404) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Hash Full Join (cost=1.01..5.13 rows=141 width=404) (actual rows= loops=)

  • Hash Cond: (COALESCE(s1.customer_id, s2.customer_id) = s3.customer_id)
41. 0.000 0.000 ↓ 0.0

Hash Full Join (cost=0.36..3.82 rows=141 width=304) (actual rows= loops=)

  • Hash Cond: (s1.customer_id = s2.customer_id)
42. 0.000 0.000 ↓ 0.0

CTE Scan on sportsbook s1 (cost=0.00..2.82 rows=141 width=172) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Hash (cost=0.22..0.22 rows=11 width=132) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

CTE Scan on casino s2 (cost=0.00..0.22 rows=11 width=132) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Hash (cost=0.40..0.40 rows=20 width=100) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

CTE Scan on accounts s3 (cost=0.00..0.40 rows=20 width=100) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Index Scan using customer_dimension_pkey on customer_dimension cd (cost=0.42..4.44 rows=1 width=109) (actual rows= loops=)

  • Index Cond: (customer_id = COALESCE(COALESCE(COALESCE(s1.customer_id, s2.customer_id), s3.customer_id), s4.customer_id))
48. 0.000 0.000 ↓ 0.0

Hash (cost=0.02..0.02 rows=1 width=12) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

CTE Scan on vars (cost=0.00..0.02 rows=1 width=12) (actual rows= loops=)