explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S8eZ

Settings
# exclusive inclusive rows x rows loops node
1. 107.689 567,597.942 ↑ 383.7 281,401 1

Hash Left Join (cost=96,350,811.11..102,559,430.19 rows=107,975,984 width=272) (actual time=566,452.607..567,597.942 rows=281,401 loops=1)

  • Hash Cond: (ld.advertiser_id = conf.id)
2.          

CTE stats

3. 17.999 564,605.823 ↓ 53.4 10,689 1

GroupAggregate (cost=60.33..75.83 rows=200 width=41) (actual time=564,586.585..564,605.823 rows=10,689 loops=1)

  • Group Key: ch.d_month, ch.id_advertiser
4. 7.610 564,587.824 ↓ 15.8 15,751 1

Sort (cost=60.33..62.83 rows=1,000 width=44) (actual time=564,586.556..564,587.824 rows=15,751 loops=1)

  • Sort Key: ch.d_month, ch.id_advertiser
  • Sort Method: quicksort Memory: 1615kB
5. 564,580.214 564,580.214 ↓ 15.8 15,751 1

Function Scan on clickhouse_make_query_post ch (cost=0.50..10.50 rows=1,000 width=44) (actual time=564,579.505..564,580.214 rows=15,751 loops=1)

6.          

CTE adv

7. 17.282 72.691 ↑ 1.0 26,299 1

Hash Left Join (cost=12.48..1,889.63 rows=26,838 width=50) (actual time=1.092..72.691 rows=26,299 loops=1)

  • Hash Cond: (a.manager_id = admin_panel_users.id)
8. 54.580 54.580 ↑ 1.0 26,299 1

Seq Scan on advertisers a (cost=0.00..1,671.38 rows=26,838 width=26) (actual time=0.245..54.580 rows=26,299 loops=1)

9. 0.029 0.829 ↓ 1.0 146 1

Hash (cost=10.67..10.67 rows=145 width=14) (actual time=0.829..0.829 rows=146 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
10. 0.800 0.800 ↓ 1.0 146 1

Seq Scan on admin_panel_users (cost=0.00..10.67 rows=145 width=14) (actual time=0.241..0.800 rows=146 loops=1)

  • Filter: active
  • Rows Removed by Filter: 122
11.          

CTE adv_matrix

12. 88.627 213.226 ↑ 30.4 294,242 1

Nested Loop (cost=0.02..626,498.40 rows=8,946,000 width=162) (actual time=1.115..213.226 rows=294,242 loops=1)

  • Join Filter: (m_date.m_date >= a_1.registered_on)
  • Rows Removed by Join Filter: 47645
13. 0.020 0.020 ↑ 76.9 13 1

Function Scan on generate_series m_date (cost=0.02..10.02 rows=1,000 width=8) (actual time=0.016..0.020 rows=13 loops=1)

14. 124.579 124.579 ↑ 1.0 26,299 13

CTE Scan on adv a_1 (cost=0.00..536.76 rows=26,838 width=158) (actual time=0.084..9.583 rows=26,299 loops=13)

15.          

CTE full_stats

16. 127.800 566,097.249 ↑ 551.8 294,242 1

Unique (cost=37,159,889.21..41,219,136.71 rows=162,369,900 width=203) (actual time=565,924.957..566,097.249 rows=294,242 loops=1)

17. 282.270 565,969.449 ↑ 551.8 294,242 1

Sort (cost=37,159,889.21..37,565,813.96 rows=162,369,900 width=203) (actual time=565,924.955..565,969.449 rows=294,242 loops=1)

  • Sort Key: am.m_date, am.advertiser_id, am.login, am.registered_on, am.manager, m.min_d, s.is_multi, (COALESCE(s.gross, '0'::numeric)), (CASE WHEN ((s.gross IS NULL) OR (s.gross < '1'::numeric)) THEN 0 WHEN ((s.gross >= '1'::numeric) AND (s.gross < '101'::numeric)) THEN 1 WHEN ((s.gross >= '101'::numeric) AND (s.gross < '1001'::numeric)) THEN 2 WHEN ((s.gross >= '1001'::numeric) AND (s.gross < '3001'::numeric)) THEN 3 WHEN ((s.gross >= '3001'::numeric) AND (s.gross < '6001'::numeric)) THEN 4 WHEN ((s.gross >= '6001'::numeric) AND (s.gross < '10001'::numeric)) THEN 5 WHEN (s.gross >= '10001'::numeric) THEN 6 ELSE 7 END)
  • Sort Method: quicksort Memory: 49307kB
18. 63.439 565,687.179 ↑ 551.8 294,242 1

Hash Left Join (cost=23,802.85..5,590,278.02 rows=162,369,900 width=203) (actual time=565,265.963..565,687.179 rows=294,242 loops=1)

  • Hash Cond: (am.advertiser_id = m.user_id)
19. 66.754 564,973.431 ↑ 30.4 294,242 1

Hash Left Join (cost=7.00..671,404.30 rows=8,946,000 width=195) (actual time=564,615.641..564,973.431 rows=294,242 loops=1)

  • Hash Cond: ((am.m_date = s.d_month) AND (am.advertiser_id = s.id_advertiser))
20. 292.174 292.174 ↑ 30.4 294,242 1

CTE Scan on adv_matrix am (cost=0.00..178,920.00 rows=8,946,000 width=162) (actual time=1.115..292.174 rows=294,242 loops=1)

21. 3.143 564,614.503 ↓ 53.4 10,689 1

Hash (cost=4.00..4.00 rows=200 width=41) (actual time=564,614.503..564,614.503 rows=10,689 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 707kB
22. 564,611.360 564,611.360 ↓ 53.4 10,689 1

CTE Scan on stats s (cost=0.00..4.00 rows=200 width=41) (actual time=564,586.589..564,611.360 rows=10,689 loops=1)

23. 0.815 650.309 ↓ 1.4 4,933 1

Hash (cost=23,750.48..23,750.48 rows=3,630 width=8) (actual time=650.309..650.309 rows=4,933 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 257kB
24. 0.415 649.494 ↓ 1.4 4,933 1

Subquery Scan on m (cost=23,668.80..23,750.48 rows=3,630 width=8) (actual time=647.726..649.494 rows=4,933 loops=1)

25. 252.732 649.079 ↓ 1.4 4,933 1

HashAggregate (cost=23,668.80..23,714.18 rows=3,630 width=8) (actual time=647.724..649.079 rows=4,933 loops=1)

  • Group Key: advertisers_money.user_id
26. 396.347 396.347 ↑ 1.0 662,138 1

Seq Scan on advertisers_money (cost=0.00..17,046.56 rows=662,224 width=8) (actual time=0.411..396.347 rows=662,138 loops=1)

  • Filter: (spent >= '1'::numeric)
  • Rows Removed by Filter: 159773
27.          

CTE tmp

28. 163.796 567,053.939 ↑ 197.2 274,507 1

Subquery Scan on t (cost=34,817,009.19..49,836,224.94 rows=54,123,300 width=240) (actual time=566,297.783..567,053.939 rows=274,507 loops=1)

  • Filter: (t.m_date >= (date_trunc('month'::text, now()) - '1 year'::interval))
  • Rows Removed by Filter: 19735
29. 557.073 566,890.143 ↑ 551.8 294,242 1

WindowAgg (cost=34,817,009.19..46,588,826.94 rows=162,369,900 width=240) (actual time=566,297.760..566,890.143 rows=294,242 loops=1)

30. 172.059 566,333.070 ↑ 551.8 294,242 1

Sort (cost=34,817,009.19..35,222,933.94 rows=162,369,900 width=203) (actual time=566,297.731..566,333.070 rows=294,242 loops=1)

  • Sort Key: full_stats.advertiser_id, full_stats.m_date
  • Sort Method: quicksort Memory: 49307kB
31. 566,161.011 566,161.011 ↑ 551.8 294,242 1

CTE Scan on full_stats (cost=0.00..3,247,398.00 rows=162,369,900 width=203) (actual time=565,924.959..566,161.011 rows=294,242 loops=1)

32.          

CTE last_data

33. 15.846 567,254.764 ↑ 383.7 281,401 1

Append (cost=0.00..3,378,647.01 rows=107,975,984 width=240) (actual time=566,297.788..567,254.764 rows=281,401 loops=1)

34. 567,185.461 567,185.461 ↑ 197.2 274,507 1

CTE Scan on tmp (cost=0.00..1,082,466.00 rows=54,123,300 width=240) (actual time=566,297.786..567,185.461 rows=274,507 loops=1)

35. 53.457 53.457 ↑ 7,811.5 6,894 1

CTE Scan on tmp tmp_1 (cost=0.00..1,217,774.25 rows=53,852,684 width=240) (actual time=0.010..53.457 rows=6,894 loops=1)

  • Filter: (prev_cohorts <> cohorts)
  • Rows Removed by Filter: 267613
36.          

CTE conf

37. 3.496 150.226 ↓ 51.2 10,250 1

GroupAggregate (cost=1,287,969.59..1,288,332.09 rows=200 width=36) (actual time=144.257..150.226 rows=10,250 loops=1)

  • Group Key: t_1.id
38. 1.843 146.730 ↓ 3.0 11,885 1

Unique (cost=1,287,969.59..1,288,269.59 rows=4,000 width=36) (actual time=144.248..146.730 rows=11,885 loops=1)

39. 4.524 144.887 ↑ 3.4 11,912 1

Sort (cost=1,287,969.59..1,288,069.59 rows=40,000 width=36) (actual time=144.245..144.887 rows=11,912 loops=1)

  • Sort Key: t_1.id, t_1.conference
  • Sort Method: quicksort Memory: 999kB
40. 1.123 140.363 ↑ 3.4 11,912 1

Subquery Scan on t_1 (cost=1,279,812.05..1,284,912.05 rows=40,000 width=36) (actual time=137.426..140.363 rows=11,912 loops=1)

41. 10.008 139.240 ↑ 3.4 11,912 1

HashAggregate (cost=1,279,812.05..1,284,512.05 rows=40,000 width=40) (actual time=137.424..139.240 rows=11,912 loops=1)

  • Group Key: advertisers.id, (((unnest(string_to_array(replace(replace(CASE WHEN (jsonb_typeof((advertisers.assign_params -> 'where_did_you_learn_about_us'::text)) IS NULL) THEN NULL::text WHEN (jsonb_typeof((advertisers.assign_params -> 'where_did_you_learn_about_us'::text)) = 'number'::text) THEN concat('""', ((advertisers.assign_params -> 'where_did_you_learn_about_us'::text))::text, '""') WHEN (jsonb_typeof((advertisers.assign_params -> 'where_did_you_learn_about_us'::text)) = 'null'::text) THEN NULL::text WHEN (jsonb_typeof((advertisers.assign_params -> 'where_did_you_learn_about_us'::text)) = 'string'::text) THEN ((advertisers.assign_params -> 'where_did_you_learn_about_us'::text))::text WHEN (jsonb_typeof((advertisers.assign_params -> 'where_did_you_learn_about_us'::text)) = 'array'::text) THEN substr(((advertisers.assign_params -> 'where_did_you_learn_about_us'::text))::text, 2, (length(((advertisers.assign_params -> 'where_did_you_learn_about_us'::text))::text) - 2)) ELSE NULL::text END, '""'::text, ''::text), ' '::text, ''::text), ','::text))))::integer), CASE (((unnest(string_to_array(replace(replace(CASE WHEN (jsonb_typeof((advertisers.assign_params -> 'where_did_you_learn_about_us'::text)) IS NULL) THEN NULL::text WHEN (jsonb_typeof((advertisers.assign_params -> 'where_did_you_learn_about_us'::text)) = 'number'::text) THEN concat('""', ((advertisers.assign_params -> 'where_did_you_learn_about_us'::text))::text, '""') WHEN (jsonb_typeof((advertisers.assign_params -> 'where_did_you_learn_about_us'::text)) = 'null'::text) THEN NULL::text WHEN (jsonb_typeof((advertisers.assign_params -> 'where_did_you_learn_about_us'::text)) = 'string'::text) THEN ((advertisers.assign_params -> 'where_did_you_learn_about_us'::text))::text WHEN (jsonb_typeof((advertisers.assign_params -> 'where_did_you_learn_about_us'::text)) = 'array'::text) THEN substr(((advertisers.assign_params -> 'where_did_you_learn_about_us'::text))::text, 2, (length(((advertisers.assign_params -> 'where_did_you_learn_about_us'::text))::text) - 2)) ELSE NULL::text END, '""'::text, ''::text), ' '::text, ''::text), ','::text))))::integer) WHEN 1 THEN 'ASE_2016'::text WHEN 2 THEN 'ADTECH_INDIA'::text WHEN 3 THEN 'ES_SITGES'::text WHEN 4 THEN 'ASW'::text WHEN 5 THEN 'MWC'::text WHEN 6 THEN 'IFXE'::text WHEN 7 THEN 'IMS'::text WHEN 8 THEN 'AWE'::text WHEN 9 THEN 'ADSUMMIT'::text WHEN 10 THEN 'POSTBACK_2017'::text WHEN 11 THEN 'ASE_2017'::text WHEN 12 THEN 'CHINA_JOY_2017'::text WHEN 13 THEN 'GAMESCOM_COLOGNE_2017'::text WHEN 14 THEN 'DMEXICO_2017'::text WHEN 15 THEN 'EUROPEAN_SUMMIT_2017'::text WHEN 16 THEN 'BLOCKCHAIN_LIFE_2017'::text WHEN 17 THEN 'WHITE_NIGHTS_2017'::text WHEN 18 THEN 'ARAB_AFFILIATE_SUMMIT_2017'::text WHEN 19 THEN 'CRYPTO_AFFILIATE_CONFERENCE_2017'::text WHEN 20 THEN 'BERLIN_AFFILIATE_CONFERENCE_2017'::text WHEN 21 THEN 'SIGMA_2017'::text WHEN 22 THEN 'AW_ASIA_BANGKOK_2017'::text WHEN 23 THEN 'ASW_2018'::text WHEN 24 THEN 'MWC_2018'::text WHEN 25 THEN 'AWE_BARCELONA_2018'::text WHEN 26 THEN 'ASE_2018'::text WHEN 27 THEN 'TES_2018'::text WHEN 28 THEN 'DMEXCO_2018'::text WHEN 29 THEN 'ASA_2018'::text WHEN 30 THEN 'AW_ASIA_BANGKOK_2018'::text WHEN 31 THEN 'MWC_2019'::text WHEN 32 THEN 'ASE_2019'::text WHEN 33 THEN 'MAC_2019'::text WHEN 34 THEN 'IMS_2019'::text WHEN 35 THEN 'ACC_ST_PETERSBURG_2019'::text WHEN 36 THEN 'AWE_BARCELONA_2019'::text WHEN 37 THEN 'CJ_SHANGHAI_2019'::text WHEN 38 THEN 'ASE_NEW_YORK_2019'::text WHEN 99 THEN 'OTHER'::text WHEN 1001 THEN 'MAIL_GROUP_2_PROMO'::text WHEN 1002 THEN 'TERRA_LEADS_PROMO'::text WHEN 1003 THEN 'LEAD_BIT_PROMO'::text WHEN 1004 THEN 'AD_COMBO_PROMO'::text ELSE 'OTHER'::text END
42. 9.232 129.232 ↑ 289.0 18,482 1

Result (cost=0.00..1,239,756.05 rows=5,340,800 width=40) (actual time=0.090..129.232 rows=18,482 loops=1)

43. 1.173 120.000 ↑ 289.0 18,482 1

Append (cost=0.00..612,212.05 rows=5,340,800 width=8) (actual time=0.082..120.000 rows=18,482 loops=1)

44. 2.776 75.761 ↑ 224.2 11,909 1

Result (cost=0.00..286,479.59 rows=2,670,400 width=8) (actual time=0.080..75.761 rows=11,909 loops=1)

45. 27.939 72.985 ↑ 224.2 11,909 1

ProjectSet (cost=0.00..19,439.59 rows=2,670,400 width=36) (actual time=0.077..72.985 rows=11,909 loops=1)

46. 45.046 45.046 ↑ 2.6 10,250 1

Seq Scan on advertisers (cost=0.00..3,684.23 rows=26,704 width=179) (actual time=0.050..45.046 rows=10,250 loops=1)

  • Filter: (CASE WHEN (jsonb_typeof((assign_params -> 'where_did_you_learn_about_us'::text)) IS NULL) THEN NULL::text WHEN (jsonb_typeof((assign_params -> 'where_did_you_learn_about_us'::text)) = 'number'::text) THEN concat('""', ((assign_params -> 'where_did_you_learn_about_us'::text))::text, '""') WHEN (jsonb_typeof((assign_params -> 'where_did_you_learn_about_us'::text)) = 'null'::text) THEN NULL::text WHEN (jsonb_typeof((assign_params -> 'where_did_you_learn_about_us'::text)) = 'string'::text) THEN ((assign_params -> 'where_did_you_learn_about_us'::text))::text WHEN (jsonb_typeof((assign_params -> 'where_did_you_learn_about_us'::text)) = 'array'::text) THEN substr(((assign_params -> 'where_did_you_learn_about_us'::text))::text, 2, (length(((assign_params -> 'where_did_you_learn_about_us'::text))::text) - 2)) ELSE NULL::text END IS NOT NULL)
  • Rows Removed by Filter: 16049
47. 1.669 43.066 ↑ 406.3 6,573 1

Result (cost=0.00..245,620.46 rows=2,670,400 width=8) (actual time=0.038..43.066 rows=6,573 loops=1)

48. 16.207 41.397 ↑ 406.3 6,573 1

ProjectSet (cost=0.00..18,636.46 rows=2,670,400 width=36) (actual time=0.037..41.397 rows=6,573 loops=1)

49. 25.190 25.190 ↑ 4.7 5,669 1

Seq Scan on advertisers advertisers_1 (cost=0.00..3,281.66 rows=26,704 width=179) (actual time=0.025..25.190 rows=5,669 loops=1)

  • Filter: (CASE WHEN (jsonb_typeof((assign_params -> 'q_where_did_you_learn_about_us'::text)) IS NULL) THEN NULL::text WHEN (jsonb_typeof((assign_params -> 'q_where_did_you_learn_about_us'::text)) = 'string'::text) THEN ((assign_params -> 'where_did_you_learn_about_us'::text))::text WHEN (jsonb_typeof((assign_params -> 'q_where_did_you_learn_about_us'::text)) = 'array'::text) THEN CASE WHEN (((assign_params -> 'where_did_you_learn_about_us'::text))::text = 'null'::text) THEN NULL::text ELSE substr(((assign_params -> 'where_did_you_learn_about_us'::text))::text, 2, (length(((assign_params -> 'where_did_you_learn_about_us'::text))::text) - 2)) END ELSE NULL::text END IS NOT NULL)
  • Rows Removed by Filter: 20630
50. 567,335.458 567,335.458 ↑ 383.7 281,401 1

CTE Scan on last_data ld (cost=0.00..2,159,519.68 rows=107,975,984 width=240) (actual time=566,297.790..567,335.458 rows=281,401 loops=1)

51. 1.689 154.795 ↓ 51.2 10,250 1

Hash (cost=4.00..4.00 rows=200 width=36) (actual time=154.795..154.795 rows=10,250 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 608kB
52. 153.106 153.106 ↓ 51.2 10,250 1

CTE Scan on conf (cost=0.00..4.00 rows=200 width=36) (actual time=144.259..153.106 rows=10,250 loops=1)

Planning time : 18.024 ms
Execution time : 567,679.261 ms