explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BYRS

Settings
# exclusive inclusive rows x rows loops node
1. 70,684.320 213,845.037 ↓ 17,619.0 17,619 1

Nested Loop Anti Join (cost=878,645.75..2,446,022.53 rows=1 width=148) (actual time=61,161.340..213,845.037 rows=17,619 loops=1)

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

CTE bonus

3. 9.797 27,084.402 ↓ 301.7 60,348 1

Unique (cost=762,185.04..762,192.66 rows=200 width=4) (actual time=27,067.465..27,084.402 rows=60,348 loops=1)

4. 75.964 27,074.605 ↓ 48.1 73,305 1

Sort (cost=762,185.04..762,188.85 rows=1,524 width=4) (actual time=27,067.464..27,074.605 rows=73,305 loops=1)

  • Sort Key: ps_1.playernumber
  • Sort Method: external merge Disk: 1016kB
5. 0.000 26,998.641 ↓ 48.1 73,305 1

Gather (cost=1,000.43..762,104.47 rows=1,524 width=4) (actual time=6.575..26,998.641 rows=73,305 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
6. 2.978 27,008.204 ↓ 34.4 18,326 4 / 4

Append (cost=0.43..760,952.07 rows=533 width=4) (actual time=4.464..27,008.204 rows=18,326 loops=4)

7. 5,639.830 5,639.830 ↓ 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=4.463..5,639.830 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. 6,254.942 6,254.942 ↓ 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=5.193..6,254.942 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. 6,669.254 6,669.254 ↓ 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=3.567..6,669.254 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. 7,710.692 7,710.692 ↓ 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=10.331..7,710.692 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. 730.508 730.508 ↓ 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=11.851..730.508 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 39,081.033 ↓ 19,164.0 19,164 1

Nested Loop (cost=116,453.09..1,683,823.37 rows=1 width=124) (actual time=34,057.169..39,081.033 rows=19,164 loops=1)

13. 0.000 34,375.893 ↓ 29,602.0 29,602 1

Gather (cost=116,452.67..1,683,822.24 rows=1 width=122) (actual time=34,056.809..34,375.893 rows=29,602 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
14. 44.659 37,099.423 ↓ 3,700.0 3,700 8 / 8

Nested Loop (cost=115,452.67..1,682,822.14 rows=1 width=122) (actual time=34,043.070..37,099.423 rows=3,700 loops=8)

15. 34,096.775 34,120.809 ↓ 2.0 48,899 8 / 8

Parallel Seq Scan on churn_candidates rt (cost=115,452.23..1,641,957.93 rows=24,816 width=16) (actual time=33,964.790..34,120.809 rows=48,899 loops=8)

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

SubPlan (for Parallel Seq Scan)

17. 16.285 24.034 ↑ 1.2 34,491 8 / 8

HashAggregate (cost=114,930.14..115,347.81 rows=41,767 width=4) (actual time=17.792..24.034 rows=34,491 loops=8)

  • Group Key: d.player_number
18. 5.497 7.749 ↑ 1.2 34,491 8 / 8

Bitmap Heap Scan on campaigns_2018 d (cost=821.69..114,823.92 rows=42,489 width=4) (actual time=2.314..7.749 rows=34,491 loops=8)

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

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

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

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

  • Index Cond: (id_ = ANY ('{1595,1669}'::integer[]))
21. 0.075 0.075 ↑ 23.6 601 8 / 8

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

  • Index Cond: (id_ = 1782)
22. 2,933.955 2,933.955 ↓ 0.0 0 391,194 / 8

Index Scan using vm_bi_getregistrationsinfo_playernumber_idx on vm_getregistrationsinfo rr (cost=0.43..1.65 rows=1 width=106) (actual time=0.060..0.060 rows=0 loops=391,194)

  • 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[])))
  • Rows Removed by Filter: 1
23. 29.602 4,825.126 ↑ 1.0 1 29,602

Append (cost=0.43..1.12 rows=1 width=10) (actual time=0.162..0.163 rows=1 loops=29,602)

24. 4,795.524 4,795.524 ↑ 1.0 1 29,602

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

  • Index Cond: (player_number = rt.playernumber)
  • Filter: ((base_profit IS NOT NULL) AND (amount > '0'::numeric) AND (period = '6M'::text))
25. 104,079.684 104,079.684 ↓ 290.5 58,098 19,164

CTE Scan on bonus b (cost=0.00..4.00 rows=200 width=4) (actual time=1.413..5.431 rows=58,098 loops=19,164)

Planning time : 3.995 ms
Execution time : 213,993.805 ms