explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pSyk

Settings
# exclusive inclusive rows x rows loops node
1. 1,428.340 54,868.788 ↓ 147.0 147 1

Nested Loop Anti Join (cost=1,068,315.22..2,635,630.09 rows=1 width=148) (actual time=51,386.791..54,868.788 rows=147 loops=1)

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

CTE bonus

3. 9.751 21,007.785 ↓ 301.7 60,348 1

Unique (cost=762,185.04..762,192.66 rows=200 width=4) (actual time=20,988.012..21,007.785 rows=60,348 loops=1)

4. 0.000 20,998.034 ↓ 48.1 73,305 1

Sort (cost=762,185.04..762,188.85 rows=1,524 width=4) (actual time=20,988.011..20,998.034 rows=73,305 loops=1)

  • Sort Key: ps_1.playernumber
  • Sort Method: quicksort Memory: 6509kB
5. 61.156 20,998.802 ↓ 48.1 73,305 1

Gather (cost=1,000.43..762,104.47 rows=1,524 width=4) (actual time=7.348..20,998.802 rows=73,305 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
6. 2.698 20,937.646 ↓ 34.4 18,326 4 / 4

Append (cost=0.43..760,952.07 rows=533 width=4) (actual time=4.080..20,937.646 rows=18,326 loops=4)

7. 4,784.434 4,784.434 ↓ 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.080..4,784.434 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. 4,581.705 4,581.705 ↓ 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=8.316..4,581.705 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. 5,122.894 5,122.894 ↓ 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.966..5,122.894 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,855.562 5,855.562 ↓ 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.597..5,855.562 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. 590.353 590.353 ↓ 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=13.262..590.353 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 30,870.560 ↓ 638.0 638 1

Nested Loop (cost=306,122.56..1,873,430.92 rows=1 width=124) (actual time=30,364.765..30,870.560 rows=638 loops=1)

13. 0.000 30,578.644 ↓ 10,695.0 10,695 1

Gather (cost=306,122.14..1,873,429.79 rows=1 width=122) (actual time=30,364.719..30,578.644 rows=10,695 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
14. 20.670 31,714.860 ↓ 1,337.0 1,337 8 / 8

Nested Loop (cost=305,122.14..1,872,429.69 rows=1 width=122) (actual time=30,343.946..31,714.860 rows=1,337 loops=8)

15. 30,229.396 30,715.346 ↓ 1.6 39,154 8 / 8

Parallel Seq Scan on churn_candidates rt (cost=305,121.70..1,831,627.40 rows=24,816 width=16) (actual time=30,298.835..30,715.346 rows=39,154 loops=8)

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

SubPlan (for Parallel Seq Scan)

17. 333.027 485.950 ↓ 1.0 467,110 8 / 8

HashAggregate (cost=299,556.33..304,008.63 rows=445,230 width=4) (actual time=387.476..485.950 rows=467,110 loops=8)

  • Group Key: d.player_number
18. 96.096 152.923 ↓ 1.2 669,062 8 / 8

Bitmap Heap Scan on campaigns_2018 d (cost=10,654.44..298,174.64 rows=552,674 width=4) (actual time=57.833..152.923 rows=669,062 loops=8)

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

BitmapOr (cost=10,654.44..10,654.44 rows=553,122 width=0) (actual time=56.827..56.827 rows=0 loops=8)

20. 2.395 2.395 ↓ 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.395..2.395 rows=33,890 loops=8)

  • Index Cond: (id_ = ANY ('{1595,1669}'::integer[]))
21. 54.431 54.431 ↓ 1.4 756,954 8 / 8

Bitmap Index Scan on sandbox_campaigns_2018_id_idx (cost=0.00..9,844.47 rows=524,788 width=0) (actual time=54.431..54.431 rows=756,954 loops=8)

  • Index Cond: (id_ >= 1782)
22. 978.844 978.844 ↓ 0.0 0 313,230 / 8

Index Scan using vm_bi_getregistrationsinfo_playernumber_idx on vm_getregistrationsinfo rr (cost=0.43..1.64 rows=1 width=106) (actual time=0.025..0.025 rows=0 loops=313,230)

  • 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. 0.000 438.495 ↓ 0.0 0 10,695

Append (cost=0.43..1.12 rows=1 width=10) (actual time=0.041..0.041 rows=0 loops=10,695)

24. 438.495 438.495 ↓ 0.0 0 10,695

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

  • Index Cond: (player_number = rt.playernumber)
  • Filter: ((base_profit IS NOT NULL) AND (amount > '0'::numeric) AND (period = '6M'::text))
25. 22,569.888 22,569.888 ↓ 180.1 36,020 638

CTE Scan on bonus b (cost=0.00..4.00 rows=200 width=4) (actual time=32.897..35.376 rows=36,020 loops=638)

Planning time : 21.625 ms
Execution time : 55,078.776 ms