explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pUiH

Settings
# exclusive inclusive rows x rows loops node
1. 21,318.835 5,452,946.785 ↓ 478,507.3 8,134,624 1

Nested Loop (cost=1.28..20,844.48 rows=17 width=208) (actual time=405.818..5,452,946.785 rows=8,134,624 loops=1)

  • Output: NULL::text, NULL::bigint, date(to_timestamp(((s."timestamp" / 1000))::double precision)), NULL::double precision, NULL::double precision, 0, NULL::text, 0, 0, 0, 0, 0, 0, (l.user_id)::integer, NULL::integer, NULL::integer, 0, 0, 0, NULL::text, (l.user_id)::text
2. 6,253.999 802,933.222 ↓ 6,452.0 17,401,108 1

Nested Loop (cost=0.86..7,069.65 rows=2,697 width=70) (actual time=1.525..802,933.222 rows=17,401,108 loops=1)

  • Output: l.user_id, l.session_id
3. 8,103.513 8,103.513 ↓ 21,079.3 1,222,598 1

Index Scan using ix_dim_ums_users_role on ubas_stage.etl_dim_ums_users main_main (cost=0.42..74.12 rows=58 width=8) (actual time=0.027..8,103.513 rows=1,222,598 loops=1)

  • Output: main_main.dim_ums_users, main_main.user_id, main_main.username, main_main.user_status, main_main.test_sign, main_main.user_role, main_main.created_timestamp, main_main.email_confirmed_timestamp, main_main.proof_of_address_uploaded_timestamp, main_main.proof_of_address_rejected_reason, main_main.identity_file_uploaded_timestamp, main_main.identity_file_rejected_reason, main_main.id_card_back_file_uploaded_timestamp, main_main.id_card_back_file_rejected_reason, main_main.id_card_front_file_uploaded_timestamp, main_main.id_card_front_file_rejected_reason, main_main.email_confirmed, main_main.poa_confirmed, main_main.poi_confirmed, main_main.depositor_sign, main_main.first_deposit_timestamp, main_main.first_deposit_sum, main_main.deposit_currency, main_main.account_currency, main_main.education, main_main.employment_status, main_main.occupation, main_main.industry, main_main.company, main_main.has_been_employed_in_finances_more_1y, main_main.annual_income, main_main.income_sources, main_main.other_income_source, main_main.wealth, main_main.annual_invest, main_main.purpose, main_main.other_purpose, main_main.horizon_of_investment, main_main.payment_country, main_main.warning_risk_accepted, main_main.score, main_main.has_invest_app, main_main.has_capital_app, main_main.instalation_app, main_main.apps_flyer_id, main_main.registered_dt, main_main.verified_dt, main_main.created_dt, main_main.first_deposit_dt, main_main.email_confirmed_dt, main_main.proof_of_address_uploaded_dt, main_main.poa_confirmed_dt, main_main.poi_confirmed_dt, main_main.trading_frequency_forex_cfd_otc_past_3y, main_main.trading_frequency_shares_past_3y, main_main.af_country_code, main_main.af_platform, main_main.af_install_ts, main_main.last_refresh, main_main.deposit_sum, main_main.user_rpl, main_main.reg_form_start_app_version, main_main.user_trade_qty, main_main.withdrawal_sum, main_main.docs_uploaded_ts, main_main.registration_ts, main_main.first_trade_ts, main_main.first_wthdrwl_ts, main_main.user_balance_usd, main_main.age, main_main.date_of_birth, main_main.aff_id, main_main.offer_id, main_main.first_deposit_sum_usd, main_main.deposit_sum_2w, main_main.deposit_sum_1m, main_main.users_ltv, main_main.estimated_trade_value, main_main.estimated_trade_value_2w, main_main.estimated_trade_value_1m, main_main.estimated_trade_value_2m, main_main.swap_usd, main_main.revenue, main_main.volume, main_main.last_session_start, main_main.install_app_version, main_main.aquisition_channel, main_main.install_device_model, main_main.install_device_brand, main_main.avg_position_length, main_main.daily_client_equity, main_main.last_trade_ts, main_main.user_lifetime, main_main.gclid_id, main_main.marketing_manager, main_main.referrer_host, main_main.landing_type, main_main.landing_name, main_main.capital_campaign_id, main_main.media_source, main_main.campaign, main_main.adset, main_main.ad, main_main.user_group, main_main.user_group_2w, main_main.click_id, main_main.aff_sub, main_main.aff_sub2, main_main.reg_country, main_main.spread_trade_value_2w, main_main.spread_trade_value_1m, main_main.spread_trade_value_2m, main_main.spread_trade_value, main_main.crypto_volume_usd, main_main.crypto_volume_count, main_main.reg_city, main_main.demo_sign, main_main.registration_flow_type, main_main.regmail_addr_verification_confirmed, main_main.regmail_addr_verification_request_status, main_main.regmail_addr_verification_requested, main_main.phone_country_code, main_main.reg_country_name, main_main.email_hash, main_main.program_id, main_main.financial_knowledge, main_main.aq_leverage_vs_pl, main_main.aq_leverage_vs_trade, main_main.locale, main_main.timezone, main_main.has_tin, main_main.tenant, main_main.install_device_price, main_main.install_device_performance, main_main.keywords, main_main.ad_conceptname, main_main.ad_creativename, main_main.ad_creativetype, main_main.msclkid, main_main.client_officer, main_main.first_excom_ts, main_main.crypto_stability, main_main.crypto_risk, main_main.crypto_currency, main_main.crypto_token, main_main.crypto_blockchain, main_main.crypto_attempt, main_main.first_bonus_ts, main_main.has_currency_app, main_main.questionnaire_type, main_main.questionnaire_version, main_main.trading_frequency, main_main.competirors_apps_installed, main_main.app_etoro, main_main.app_igtrading, main_main.app_investing_com, main_main.app_plus500, main_main.app_trading212, main_main.first_license, main_main.app_name_clone, main_main.risk_score, main_main.risk_score_group, main_main.deposit_bin, main_main.deposit_bin_total, main_main.funnel
  • Index Cond: ((main_main.user_role = ANY ('{USER,PROFESSIONAL,ECP}'::text[])) AND (main_main.user_role = ANY ('{USER,PROFESSIONAL}'::text[])))
4. 788,575.710 788,575.710 ↑ 20.8 14 1,222,598

Index Scan using ix_login_1 on tracking_trading.login l (cost=0.44..117.70 rows=291 width=70) (actual time=0.022..0.645 rows=14 loops=1,222,598)

  • Output: l.session_event_num, l.event_name, l.session_id, l.user_id, l.device_id, l.as_guest, l."timestamp", l.mode
  • Index Cond: (l.user_id = main_main.user_id)
5. 34,802.216 4,628,694.728 ↓ 0.0 0 17,401,108

Append (cost=0.42..4.99 rows=12 width=70) (actual time=0.258..0.266 rows=0 loops=17,401,108)

6. 243,615.512 243,615.512 ↓ 0.0 0 17,401,108

Index Only Scan using session_2017_session_id_session_event_num_timestamp_key on tracking_trading.session_2017 s (cost=0.42..0.49 rows=1 width=45) (actual time=0.014..0.014 rows=0 loops=17,401,108)

  • Output: s."timestamp", s.session_id
  • Index Cond: (s.session_id = l.session_id)
  • Filter: (((s."timestamp")::double precision <= (date_part('epoch'::text, (CURRENT_DATE)::timestamp without time zone) * '1000'::double precision)) AND ((s."timestamp")::double precision >= (date_part('epoch'::text, (date((date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone) - '3 mons'::interval)))::timestamp without time zone) * '1000'::double precision)))
  • Rows Removed by Filter: 0
  • Heap Fetches: 559,695
7. 1,009,264.264 1,009,264.264 ↓ 0.0 0 17,401,108

Index Only Scan using session_2018_session_id_session_event_num_timestamp_key on tracking_trading.session_2018 s_1 (cost=0.56..0.63 rows=1 width=65) (actual time=0.058..0.058 rows=0 loops=17,401,108)

  • Output: s_1."timestamp", s_1.session_id
  • Index Cond: (s_1.session_id = l.session_id)
  • Filter: (((s_1."timestamp")::double precision <= (date_part('epoch'::text, (CURRENT_DATE)::timestamp without time zone) * '1000'::double precision)) AND ((s_1."timestamp")::double precision >= (date_part('epoch'::text, (date((date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone) - '3 mons'::interval)))::timestamp without time zone) * '1000'::double precision)))
  • Rows Removed by Filter: 0
  • Heap Fetches: 2,841,757
8. 800,450.968 800,450.968 ↓ 0.0 0 17,401,108

Index Only Scan using session_2019_session_id_session_event_num_timestamp_key on tracking_trading.session_2019 s_2 (cost=0.56..0.63 rows=1 width=72) (actual time=0.046..0.046 rows=0 loops=17,401,108)

  • Output: s_2."timestamp", s_2.session_id
  • Index Cond: (s_2.session_id = l.session_id)
  • Filter: (((s_2."timestamp")::double precision <= (date_part('epoch'::text, (CURRENT_DATE)::timestamp without time zone) * '1000'::double precision)) AND ((s_2."timestamp")::double precision >= (date_part('epoch'::text, (date((date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone) - '3 mons'::interval)))::timestamp without time zone) * '1000'::double precision)))
  • Rows Removed by Filter: 0
  • Heap Fetches: 3,670,152
9. 643,840.996 643,840.996 ↓ 0.0 0 17,401,108

Index Only Scan using session_2020_1_session_id_session_event_num_timestamp_key on tracking_trading.session_2020_1 s_3 (cost=0.56..0.62 rows=1 width=73) (actual time=0.037..0.037 rows=0 loops=17,401,108)

  • Output: s_3."timestamp", s_3.session_id
  • Index Cond: (s_3.session_id = l.session_id)
  • Filter: (((s_3."timestamp")::double precision <= (date_part('epoch'::text, (CURRENT_DATE)::timestamp without time zone) * '1000'::double precision)) AND ((s_3."timestamp")::double precision >= (date_part('epoch'::text, (date((date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone) - '3 mons'::interval)))::timestamp without time zone) * '1000'::double precision)))
  • Rows Removed by Filter: 0
  • Heap Fetches: 2,050,895
10. 1,218,077.560 1,218,077.560 ↓ 0.0 0 17,401,108

Index Only Scan using session_2020_2_session_id_session_event_num_timestamp_key on tracking_trading.session_2020_2 s_4 (cost=0.56..0.64 rows=1 width=74) (actual time=0.070..0.070 rows=0 loops=17,401,108)

  • Output: s_4."timestamp", s_4.session_id
  • Index Cond: (s_4.session_id = l.session_id)
  • Filter: (((s_4."timestamp")::double precision <= (date_part('epoch'::text, (CURRENT_DATE)::timestamp without time zone) * '1000'::double precision)) AND ((s_4."timestamp")::double precision >= (date_part('epoch'::text, (date((date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone) - '3 mons'::interval)))::timestamp without time zone) * '1000'::double precision)))
  • Heap Fetches: 6,054,905
11. 556,835.456 556,835.456 ↓ 0.0 0 17,401,108

Index Only Scan using session_2020_3_session_id_session_event_num_timestamp_key on tracking_trading.session_2020_3 s_5 (cost=0.56..0.62 rows=1 width=73) (actual time=0.032..0.032 rows=0 loops=17,401,108)

  • Output: s_5."timestamp", s_5.session_id
  • Index Cond: (s_5.session_id = l.session_id)
  • Filter: (((s_5."timestamp")::double precision <= (date_part('epoch'::text, (CURRENT_DATE)::timestamp without time zone) * '1000'::double precision)) AND ((s_5."timestamp")::double precision >= (date_part('epoch'::text, (date((date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone) - '3 mons'::interval)))::timestamp without time zone) * '1000'::double precision)))
  • Rows Removed by Filter: 0
  • Heap Fetches: 2,091,962
12. 17,401.108 17,401.108 ↓ 0.0 0 17,401,108

Index Only Scan using session_2020_4_session_id_session_event_num_timestamp_key on tracking_trading.session_2020_4 s_6 (cost=0.13..0.19 rows=1 width=40) (actual time=0.001..0.001 rows=0 loops=17,401,108)

  • Output: s_6."timestamp", s_6.session_id
  • Index Cond: (s_6.session_id = l.session_id)
  • Filter: (((s_6."timestamp")::double precision <= (date_part('epoch'::text, (CURRENT_DATE)::timestamp without time zone) * '1000'::double precision)) AND ((s_6."timestamp")::double precision >= (date_part('epoch'::text, (date((date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone) - '3 mons'::interval)))::timestamp without time zone) * '1000'::double precision)))
  • Heap Fetches: 0
13. 17,401.108 17,401.108 ↓ 0.0 0 17,401,108

Index Only Scan using session_2021_1_session_id_session_event_num_timestamp_key on tracking_trading.session_2021_1 s_7 (cost=0.12..0.18 rows=1 width=40) (actual time=0.001..0.001 rows=0 loops=17,401,108)

  • Output: s_7."timestamp", s_7.session_id
  • Index Cond: (s_7.session_id = l.session_id)
  • Filter: (((s_7."timestamp")::double precision <= (date_part('epoch'::text, (CURRENT_DATE)::timestamp without time zone) * '1000'::double precision)) AND ((s_7."timestamp")::double precision >= (date_part('epoch'::text, (date((date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone) - '3 mons'::interval)))::timestamp without time zone) * '1000'::double precision)))
  • Heap Fetches: 0
14. 17,401.108 17,401.108 ↓ 0.0 0 17,401,108

Index Only Scan using session_2021_2_session_id_session_event_num_timestamp_key on tracking_trading.session_2021_2 s_8 (cost=0.12..0.18 rows=1 width=40) (actual time=0.001..0.001 rows=0 loops=17,401,108)

  • Output: s_8."timestamp", s_8.session_id
  • Index Cond: (s_8.session_id = l.session_id)
  • Filter: (((s_8."timestamp")::double precision <= (date_part('epoch'::text, (CURRENT_DATE)::timestamp without time zone) * '1000'::double precision)) AND ((s_8."timestamp")::double precision >= (date_part('epoch'::text, (date((date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone) - '3 mons'::interval)))::timestamp without time zone) * '1000'::double precision)))
  • Heap Fetches: 0
15. 17,401.108 17,401.108 ↓ 0.0 0 17,401,108

Index Only Scan using session_2021_3_session_id_session_event_num_timestamp_key on tracking_trading.session_2021_3 s_9 (cost=0.14..0.20 rows=1 width=40) (actual time=0.001..0.001 rows=0 loops=17,401,108)

  • Output: s_9."timestamp", s_9.session_id
  • Index Cond: (s_9.session_id = l.session_id)
  • Filter: (((s_9."timestamp")::double precision <= (date_part('epoch'::text, (CURRENT_DATE)::timestamp without time zone) * '1000'::double precision)) AND ((s_9."timestamp")::double precision >= (date_part('epoch'::text, (date((date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone) - '3 mons'::interval)))::timestamp without time zone) * '1000'::double precision)))
  • Heap Fetches: 0
16. 17,401.108 17,401.108 ↓ 0.0 0 17,401,108

Index Only Scan using session_2021_4_session_id_session_event_num_timestamp_key on tracking_trading.session_2021_4 s_10 (cost=0.14..0.20 rows=1 width=40) (actual time=0.001..0.001 rows=0 loops=17,401,108)

  • Output: s_10."timestamp", s_10.session_id
  • Index Cond: (s_10.session_id = l.session_id)
  • Filter: (((s_10."timestamp")::double precision <= (date_part('epoch'::text, (CURRENT_DATE)::timestamp without time zone) * '1000'::double precision)) AND ((s_10."timestamp")::double precision >= (date_part('epoch'::text, (date((date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone) - '3 mons'::interval)))::timestamp without time zone) * '1000'::double precision)))
  • Heap Fetches: 0
17. 34,802.216 34,802.216 ↓ 0.0 0 17,401,108

Index Only Scan using session_def_session_id_session_event_num_timestamp_key on tracking_trading.session_def s_11 (cost=0.27..0.33 rows=1 width=57) (actual time=0.002..0.002 rows=0 loops=17,401,108)

  • Output: s_11."timestamp", s_11.session_id
  • Index Cond: (s_11.session_id = l.session_id)
  • Filter: (((s_11."timestamp")::double precision <= (date_part('epoch'::text, (CURRENT_DATE)::timestamp without time zone) * '1000'::double precision)) AND ((s_11."timestamp")::double precision >= (date_part('epoch'::text, (date((date_trunc('quarter'::text, (CURRENT_DATE)::timestamp with time zone) - '3 mons'::interval)))::timestamp without time zone) * '1000'::double precision)))
  • Rows Removed by Filter: 0
  • Heap Fetches: 43
Planning time : 1.902 ms
Execution time : 5,453,516.779 ms