explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 294t

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

Nested Loop Anti Join (cost=878,645.75..2,446,022.53 rows=1 width=148) (actual rows= loops=)

  • Join Filter: (b.playernumber = rt.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

Nested Loop (cost=116,453.09..1,683,823.37 rows=1 width=124) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Gather (cost=116,452.67..1,683,822.24 rows=1 width=122) (actual rows= loops=)

  • Workers Planned: 7
14. 0.000 0.000 ↓ 0.0

Nested Loop (cost=115,452.67..1,682,822.14 rows=1 width=122) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on churn_candidates rt (cost=115,452.23..1,641,957.93 rows=24,816 width=16) (actual rows= loops=)

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

SubPlan (for Parallel Seq Scan)

17. 0.000 0.000 ↓ 0.0

HashAggregate (cost=114,930.14..115,347.81 rows=41,767 width=4) (actual rows= loops=)

  • Group Key: d.player_number
18. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on campaigns_2018 d (cost=821.69..114,823.92 rows=42,489 width=4) (actual rows= loops=)

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

BitmapOr (cost=821.69..821.69 rows=42,501 width=0) (actual rows= loops=)

20. 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[]))
21. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on sandbox_campaigns_2018_id_idx (cost=0.00..266.81 rows=14,167 width=0) (actual rows= loops=)

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

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

  • Index Cond: (playernumber = rt.playernumber)
  • 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[])))
23. 0.000 0.000 ↓ 0.0

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

24. 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))
25. 0.000 0.000 ↓ 0.0

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