explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0rY

Settings
# exclusive inclusive rows x rows loops node
1. 734.773 16,652.485 ↓ 0.0 0 1

Nested Loop Anti Join (cost=1,069,540.45..2,634,326.38 rows=1 width=148) (actual time=16,652.485..16,652.485 rows=0 loops=1)

  • Join Filter: (b.playernumber = rt.playernumber)
  • Rows Removed by Join Filter: 12446620
2.          

CTE bonus

3. 8.866 10,617.287 ↓ 271.8 54,352 1

Unique (cost=762,185.04..762,192.66 rows=200 width=4) (actual time=10,599.764..10,617.287 rows=54,352 loops=1)

4. 33.639 10,608.421 ↓ 44.2 67,304 1

Sort (cost=762,185.04..762,188.85 rows=1,524 width=4) (actual time=10,599.762..10,608.421 rows=67,304 loops=1)

  • Sort Key: ps_1.playernumber
  • Sort Method: quicksort Memory: 6509kB
5. 19.735 10,574.782 ↓ 48.1 73,305 1

Gather (cost=1,000.43..762,104.47 rows=1,524 width=4) (actual time=1.070..10,574.782 rows=73,305 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
6. 1.909 10,555.047 ↓ 34.4 18,326 4 / 4

Append (cost=0.43..760,952.07 rows=533 width=4) (actual time=0.237..10,555.047 rows=18,326 loops=4)

7. 178.573 178.573 ↓ 59.1 5,376 4 / 4

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 time=0.237..178.573 rows=5,376 loops=4)

  • 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)))
  • Rows Removed by Filter: 53155
8. 194.757 194.757 ↓ 38.9 4,083 4 / 4

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 time=0.161..194.757 rows=4,083 loops=4)

  • 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)))
  • Rows Removed by Filter: 62622
9. 3,858.687 3,858.687 ↓ 40.9 5,808 4 / 4

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 time=0.066..3,858.687 rows=5,808 loops=4)

  • 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)))
  • Rows Removed by Filter: 58478
10. 5,793.586 5,793.586 ↓ 28.4 2,923 4 / 4

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 time=4.809..5,793.586 rows=2,923 loops=4)

  • 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)))
  • Rows Removed by Filter: 57024
11. 527.535 527.535 ↓ 1.5 136 4 / 4

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 time=9.353..527.535 rows=136 loops=4)

  • 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)))
  • Rows Removed by Filter: 7993
12. 0.000 4,489.428 ↓ 453.0 453 1

Gather (cost=307,347.79..1,872,127.22 rows=1 width=124) (actual time=4,488.934..4,489.428 rows=453 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
13. 0.294 4,686.044 ↓ 57.0 57 8 / 8

Nested Loop (cost=306,347.79..1,871,127.12 rows=1 width=124) (actual time=4,436.420..4,686.044 rows=57 loops=8)

  • Join Filter: (rt.playernumber = rr.playernumber)
14. 5.051 4,620.119 ↓ 1.1 9,376 8 / 8

Nested Loop (cost=306,347.36..1,860,936.04 rows=8,272 width=26) (actual time=4,391.108..4,620.119 rows=9,376 loops=8)

15. 3,799.114 4,470.639 ↓ 1.5 36,107 8 / 8

Parallel Seq Scan on churn_candidates rt (cost=306,346.93..1,832,852.63 rows=24,816 width=16) (actual time=4,390.987..4,470.639 rows=36,107 loops=8)

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

SubPlan (for Parallel Seq Scan)

17. 505.149 671.525 ↓ 1.5 679,702 8 / 8

HashAggregate (cost=300,707.54..305,219.05 rows=451,151 width=4) (actual time=514.270..671.525 rows=679,702 loops=8)

  • Group Key: d.player_number
18. 108.498 166.376 ↓ 1.6 887,889 8 / 8

Bitmap Heap Scan on campaigns_2018 d (cost=10,861.30..299,302.48 rows=562,023 width=4) (actual time=59.063..166.376 rows=887,889 loops=8)

  • Recheck Cond: ((id_ = ANY ('{1595,1669}'::integer[])) OR (id_ >= 1782))
  • Heap Blocks: exact=7490
19. 0.003 57.878 ↓ 0.0 0 8 / 8

BitmapOr (cost=10,861.30..10,861.30 rows=562,479 width=0) (actual time=57.878..57.878 rows=0 loops=8)

20. 1.929 1.929 ↓ 1.2 33,890 8 / 8

Bitmap Index Scan on sandbox_campaigns_2018_id_idx (cost=0.00..542.63 rows=28,468 width=0) (actual time=1.929..1.929 rows=33,890 loops=8)

  • Index Cond: (id_ = ANY ('{1595,1669}'::integer[]))
21. 55.946 55.946 ↓ 1.6 862,864 8 / 8

Bitmap Index Scan on sandbox_campaigns_2018_id_idx (cost=0.00..10,037.65 rows=534,012 width=0) (actual time=55.946..55.946 rows=862,864 loops=8)

  • Index Cond: (id_ >= 1782)
22. 0.000 144.429 ↓ 0.0 0 288,857 / 8

Append (cost=0.43..1.12 rows=1 width=10) (actual time=0.004..0.004 rows=0 loops=288,857)

23. 144.429 144.429 ↓ 0.0 0 288,857 / 8

Index Scan using player_stats_6m_pk on player_stats_6m ps (cost=0.43..1.12 rows=1 width=10) (actual time=0.004..0.004 rows=0 loops=288,857)

  • Index Cond: (player_number = rt.playernumber)
  • Filter: ((base_profit IS NOT NULL) AND (amount > '0'::numeric) AND (period = '6M'::text))
24. 65.631 65.631 ↓ 0.0 0 75,007 / 8

Index Scan using vm_bi_getregistrationsinfo_playernumber_idx on vm_getregistrationsinfo rr (cost=0.43..1.22 rows=1 width=106) (actual time=0.007..0.007 rows=0 loops=75,007)

  • 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,7}'::integer[])))
  • Rows Removed by Filter: 1
25. 11,428.284 11,428.284 ↓ 137.4 27,477 453

CTE Scan on bonus b (cost=0.00..4.00 rows=200 width=4) (actual time=23.399..25.228 rows=27,477 loops=453)

Planning time : 6.811 ms
Execution time : 16,670.126 ms