explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aDdw

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

Nested Loop (cost=1,121,544.41..36,170,301,097.69 rows=1 width=148) (actual rows= loops=)

  • Join Filter: (rt.playernumber = rr.playernumber)
2.          

CTE bonus

3. 0.000 0.000 ↓ 0.0

Unique (cost=762,185.04..762,192.66 rows=200 width=4) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Sort (cost=762,185.04..762,188.85 rows=1,524 width=4) (actual rows= loops=)

  • Sort Key: ps_1.playernumber
5. 0.000 0.000 ↓ 0.0

Gather (cost=1,000.43..762,104.47 rows=1,524 width=4) (actual rows= loops=)

  • Workers Planned: 3
6. 0.000 0.000 ↓ 0.0

Append (cost=0.43..760,952.07 rows=533 width=4) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Parallel Index Scan using bi_getpaymentssummary_82_typepayment_idx on bi_getpaymentssummary_201910 ps_1 (cost=0.43..166,277.77 rows=91 width=4) (actual rows= loops=)

  • Index Cond: (typepayment = 5)
  • Filter: (((nameoperationbm)::text ~~ '%CRM%'::text) AND ((description ~~ '%Бонус 110%%'::text) OR (description ~~ '%Бонус 125%%'::text) OR (description ~~ '%Бонус 100%%'::text) OR (description ~~ '%120%%'::text) OR (description ~~ '%50%%%'::text) OR (description ~~ '%Free Bonus%'::text)))
8. 0.000 0.000 ↓ 0.0

Parallel Index Scan using bi_getpaymentssummary_86_typepayment_idx on bi_getpaymentssummary_201911 ps_2 (cost=0.43..196,132.28 rows=105 width=4) (actual rows= loops=)

  • Index Cond: (typepayment = 5)
  • Filter: (((nameoperationbm)::text ~~ '%CRM%'::text) AND ((description ~~ '%Бонус 110%%'::text) OR (description ~~ '%Бонус 125%%'::text) OR (description ~~ '%Бонус 100%%'::text) OR (description ~~ '%120%%'::text) OR (description ~~ '%50%%%'::text) OR (description ~~ '%Free Bonus%'::text)))
9. 0.000 0.000 ↓ 0.0

Parallel Index Scan using bi_getpaymentssummary_87_typepayment_idx on bi_getpaymentssummary_201912 ps_3 (cost=0.43..200,267.45 rows=142 width=4) (actual rows= loops=)

  • Index Cond: (typepayment = 5)
  • Filter: (((nameoperationbm)::text ~~ '%CRM%'::text) AND ((description ~~ '%Бонус 110%%'::text) OR (description ~~ '%Бонус 125%%'::text) OR (description ~~ '%Бонус 100%%'::text) OR (description ~~ '%120%%'::text) OR (description ~~ '%50%%%'::text) OR (description ~~ '%Free Bonus%'::text)))
10. 0.000 0.000 ↓ 0.0

Parallel Index Scan using bi_getpaymentssummary_88_typepayment_idx on bi_getpaymentssummary_202001 ps_4 (cost=0.43..178,233.26 rows=103 width=4) (actual rows= loops=)

  • Index Cond: (typepayment = 5)
  • Filter: (((nameoperationbm)::text ~~ '%CRM%'::text) AND ((description ~~ '%Бонус 110%%'::text) OR (description ~~ '%Бонус 125%%'::text) OR (description ~~ '%Бонус 100%%'::text) OR (description ~~ '%120%%'::text) OR (description ~~ '%50%%%'::text) OR (description ~~ '%Free Bonus%'::text)))
11. 0.000 0.000 ↓ 0.0

Parallel Index Scan using bi_getpaymentssummary_89_typepayment_idx on bi_getpaymentssummary_202002 ps_5 (cost=0.42..20,041.30 rows=92 width=4) (actual rows= loops=)

  • Index Cond: (typepayment = 5)
  • Filter: (((nameoperationbm)::text ~~ '%CRM%'::text) AND ((description ~~ '%Бонус 110%%'::text) OR (description ~~ '%Бонус 125%%'::text) OR (description ~~ '%Бонус 100%%'::text) OR (description ~~ '%120%%'::text) OR (description ~~ '%50%%%'::text) OR (description ~~ '%Free Bonus%'::text)))
12. 0.000 0.000 ↓ 0.0

Hash Anti Join (cost=359,351.32..36,169,425,466.77 rows=115,786 width=26) (actual rows= loops=)

  • Hash Cond: (rt.playernumber = b.playernumber)
13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=359,344.82..36,169,423,998.06 rows=115,801 width=26) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Gather (cost=359,344.39..36,169,227,416.46 rows=173,710 width=16) (actual rows= loops=)

  • Workers Planned: 7
15. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on churn_candidates rt (cost=358,344.39..36,169,209,045.46 rows=24,816 width=16) (actual rows= loops=)

  • Filter: ((probability > 0.8) AND (ts = '2020-02-03'::date) AND (NOT (SubPlan 2)))
16.          

SubPlan (for Parallel Seq Scan)

17. 0.000 0.000 ↓ 0.0

Materialize (cost=358,344.39..365,045.08 rows=443,529 width=4) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Unique (cost=358,344.39..361,094.43 rows=443,529 width=4) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Sort (cost=358,344.39..359,719.41 rows=550,008 width=4) (actual rows= loops=)

  • Sort Key: d.player_number
20. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on campaigns_2018 d (cost=10,573.09..298,382.12 rows=550,008 width=4) (actual rows= loops=)

  • Recheck Cond: ((id_ = ANY ('{1595,1669}'::integer[])) OR (id_ >= 1782))
21. 0.000 0.000 ↓ 0.0

BitmapOr (cost=10,573.09..10,573.09 rows=550,454 width=0) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on sandbox_campaigns_2018_id_idx (cost=0.00..533.63 rows=28,334 width=0) (actual rows= loops=)

  • Index Cond: (id_ = ANY ('{1595,1669}'::integer[]))
23. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on sandbox_campaigns_2018_id_idx (cost=0.00..9,764.45 rows=522,119 width=0) (actual rows= loops=)

  • Index Cond: (id_ >= 1782)
24. 0.000 0.000 ↓ 0.0

Append (cost=0.43..1.12 rows=1 width=10) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Index Scan using player_stats_6m_pk on player_stats_6m ps (cost=0.43..1.12 rows=1 width=10) (actual rows= loops=)

  • Index Cond: (player_number = rt.playernumber)
  • Filter: ((base_profit IS NOT NULL) AND (amount > '0'::numeric) AND (period = '6M'::text))
26. 0.000 0.000 ↓ 0.0

Hash (cost=4.00..4.00 rows=200 width=4) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

CTE Scan on bonus b (cost=0.00..4.00 rows=200 width=4) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Index Scan using vm_bi_getregistrationsinfo_playernumber_idx on vm_getregistrationsinfo rr (cost=0.43..0.97 rows=1 width=106) (actual rows= loops=)

  • Index Cond: (playernumber = ps.player_number)
  • Filter: ((is_deleted IS FALSE) AND (isblocked IS FALSE) AND (datelastvisit >= '2020-01-13 00:00:00'::timestamp without time zone) AND (registrationdate < '2020-01-06 00:00:00'::timestamp without time zone) AND (owcard = 0) AND (COALESCE(maxvalue, '0'::numeric) = '0'::numeric) AND (COALESCE((playerstatus)::integer, 0) <= 10) AND (COALESCE((playerstatus)::integer, 0) = ANY ('{0,5,10}'::integer[])) AND (currencyid = ANY ('{1,2,3,13,9,22,17}'::integer[])))