explain.depesz.com

PostgreSQL's explain analyze made readable

Result: II8F

Settings
# exclusive inclusive rows x rows loops node
1. 356.861 9,655.580 ↓ 1.0 193,855 1

Nested Loop Left Join (cost=13,778.48..859,710,819.09 rows=191,536 width=79) (actual time=221.974..9,655.580 rows=193,855 loops=1)

  • Buffers: shared hit=4443639
2. 165.693 8,717.154 ↓ 1.0 193,855 1

Nested Loop Left Join (cost=13,777.49..818,145,082.88 rows=191,536 width=90) (actual time=221.947..8,717.154 rows=193,855 loops=1)

  • Buffers: shared hit=3859462
3. 64.070 7,969.896 ↓ 1.0 193,855 1

Nested Loop Left Join (cost=13,776.65..814,470,861.19 rows=191,536 width=90) (actual time=221.918..7,969.896 rows=193,855 loops=1)

  • Buffers: shared hit=3164189
4. 212.523 2,090.176 ↓ 1.0 193,855 1

Nested Loop Left Join (cost=9,651.38..24,327,793.03 rows=191,536 width=86) (actual time=221.661..2,090.176 rows=193,855 loops=1)

  • Buffers: shared hit=731654
5. 77.530 714.523 ↓ 1.0 193,855 1

Nested Loop (cost=9,524.50..22,120.45 rows=191,536 width=82) (actual time=221.563..714.523 rows=193,855 loops=1)

  • Buffers: shared hit=7701
6. 0.004 0.029 ↑ 1.0 1 1

Limit (cost=0.03..0.10 rows=1 width=24) (actual time=0.026..0.029 rows=1 loops=1)

7. 0.025 0.025 ↑ 1,000.0 1 1

Function Scan on generate_series gs (cost=0.03..70.03 rows=1,000 width=24) (actual time=0.025..0.025 rows=1 loops=1)

8. 288.839 636.964 ↓ 1.0 193,855 1

Hash Right Join (cost=9,524.47..20,204.98 rows=191,536 width=58) (actual time=221.534..636.964 rows=193,855 loops=1)

  • Hash Cond: (cl.f_client_id = cp.f_client_id)
  • Buffers: shared hit=7701
9. 127.479 127.479 ↑ 1.0 348,253 1

Seq Scan on dim_clients cl (cost=0.00..7,439.11 rows=353,611 width=8) (actual time=0.040..127.479 rows=348,253 loops=1)

  • Buffers: shared hit=3903
10. 94.378 220.646 ↓ 1.0 193,855 1

Hash (cost=7,130.27..7,130.27 rows=191,536 width=54) (actual time=220.646..220.646 rows=193,855 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 11135kB
  • Buffers: shared hit=3798
11. 126.268 126.268 ↓ 1.0 193,855 1

Index Scan using dim_client_profiles_id_pk on dim_client_profiles cp (cost=0.42..7,130.27 rows=191,536 width=54) (actual time=0.027..126.268 rows=193,855 loops=1)

  • Index Cond: (id < 746000)
  • Buffers: shared hit=3798
12. 0.000 1,163.130 ↓ 0.0 0 193,855

Limit (cost=126.88..126.88 rows=1 width=16) (actual time=0.006..0.006 rows=0 loops=193,855)

  • Buffers: shared hit=723953
13. 387.710 1,163.130 ↓ 0.0 0 193,855

Sort (cost=126.88..126.89 rows=4 width=16) (actual time=0.006..0.006 rows=0 loops=193,855)

  • Sort Key: fd1.d_deal_time DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=723953
14. 158.206 775.420 ↓ 0.0 0 193,855

Nested Loop (cost=0.71..126.86 rows=4 width=16) (actual time=0.004..0.004 rows=0 loops=193,855)

  • Buffers: shared hit=723953
15. 387.710 387.710 ↓ 0.0 0 193,855

Index Scan using dim_accounts_f_profile_id_index on dim_accounts aa3 (cost=0.42..4.09 rows=3 width=12) (actual time=0.002..0.002 rows=0 loops=193,855)

  • Index Cond: (f_profile_id = cp.f_profile_id)
  • Buffers: shared hit=608911
16. 86.064 229.504 ↓ 0.0 0 28,688

Append (cost=0.29..40.59 rows=32 width=20) (actual time=0.008..0.008 rows=0 loops=28,688)

  • Buffers: shared hit=115042
17. 0.000 0.000 ↓ 0.0 0

Index Only Scan using fact_deals_default_f_server_id_f_account_id_d_deal_time_idx on fact_deals_default fd1 (cost=0.29..2.31 rows=1 width=20) (never executed)

  • Index Cond: ((f_server_id = aa3.f_server_id) AND (f_account_id = aa3.f_account_id) AND (d_deal_time >= ((gs.gs)::date)) AND (d_deal_time < ((((gs.gs)::date + '1 mon'::interval))::date)))
  • Heap Fetches: 0
18. 0.000 0.000 ↓ 0.0 0

Index Only Scan using fact_deals_y2010_f_server_id_f_account_id_d_deal_time_idx on fact_deals_y2010 fd1_1 (cost=0.43..4.30 rows=5 width=20) (never executed)

  • Index Cond: ((f_server_id = aa3.f_server_id) AND (f_account_id = aa3.f_account_id) AND (d_deal_time >= ((gs.gs)::date)) AND (d_deal_time < ((((gs.gs)::date + '1 mon'::interval))::date)))
  • Heap Fetches: 0
19. 0.000 0.000 ↓ 0.0 0

Index Only Scan using fact_deals_y2011_f_server_id_f_account_id_d_deal_time_idx on fact_deals_y2011 fd1_2 (cost=0.56..5.36 rows=7 width=20) (never executed)

  • Index Cond: ((f_server_id = aa3.f_server_id) AND (f_account_id = aa3.f_account_id) AND (d_deal_time >= ((gs.gs)::date)) AND (d_deal_time < ((((gs.gs)::date + '1 mon'::interval))::date)))
  • Heap Fetches: 0
20. 0.000 0.000 ↓ 0.0 0

Index Only Scan using fact_deals_y2012_f_server_id_f_account_id_d_deal_time_idx on fact_deals_y2012 fd1_3 (cost=0.56..3.05 rows=2 width=20) (never executed)

  • Index Cond: ((f_server_id = aa3.f_server_id) AND (f_account_id = aa3.f_account_id) AND (d_deal_time >= ((gs.gs)::date)) AND (d_deal_time < ((((gs.gs)::date + '1 mon'::interval))::date)))
  • Heap Fetches: 0
21. 0.000 0.000 ↓ 0.0 0

Index Only Scan using fact_deals_y2013_f_server_id_f_account_id_d_deal_time_idx on fact_deals_y2013 fd1_4 (cost=0.56..4.08 rows=4 width=20) (never executed)

  • Index Cond: ((f_server_id = aa3.f_server_id) AND (f_account_id = aa3.f_account_id) AND (d_deal_time >= ((gs.gs)::date)) AND (d_deal_time < ((((gs.gs)::date + '1 mon'::interval))::date)))
  • Heap Fetches: 0
22. 0.000 0.000 ↓ 0.0 0

Index Only Scan using fact_deals_y2014_f_server_id_f_account_id_d_deal_time_idx on fact_deals_y2014 fd1_5 (cost=0.56..3.22 rows=2 width=20) (never executed)

  • Index Cond: ((f_server_id = aa3.f_server_id) AND (f_account_id = aa3.f_account_id) AND (d_deal_time >= ((gs.gs)::date)) AND (d_deal_time < ((((gs.gs)::date + '1 mon'::interval))::date)))
  • Heap Fetches: 0
23. 0.000 0.000 ↓ 0.0 0

Index Only Scan using fact_deals_y2015_f_server_id_f_account_id_d_deal_time_idx on fact_deals_y2015 fd1_6 (cost=0.56..3.39 rows=2 width=20) (never executed)

  • Index Cond: ((f_server_id = aa3.f_server_id) AND (f_account_id = aa3.f_account_id) AND (d_deal_time >= ((gs.gs)::date)) AND (d_deal_time < ((((gs.gs)::date + '1 mon'::interval))::date)))
  • Heap Fetches: 0
24. 0.000 0.000 ↓ 0.0 0

Index Only Scan using fact_deals_y2016_f_server_id_f_account_id_d_deal_time_idx on fact_deals_y2016 fd1_7 (cost=0.56..3.29 rows=2 width=20) (never executed)

  • Index Cond: ((f_server_id = aa3.f_server_id) AND (f_account_id = aa3.f_account_id) AND (d_deal_time >= ((gs.gs)::date)) AND (d_deal_time < ((((gs.gs)::date + '1 mon'::interval))::date)))
  • Heap Fetches: 0
25. 0.000 0.000 ↓ 0.0 0

Index Only Scan using fact_deals_y2017_f_server_id_f_account_id_d_deal_time_idx on fact_deals_y2017 fd1_8 (cost=0.56..3.28 rows=2 width=20) (never executed)

  • Index Cond: ((f_server_id = aa3.f_server_id) AND (f_account_id = aa3.f_account_id) AND (d_deal_time >= ((gs.gs)::date)) AND (d_deal_time < ((((gs.gs)::date + '1 mon'::interval))::date)))
  • Heap Fetches: 0
26. 0.000 0.000 ↓ 0.0 0

Index Only Scan using fact_deals_y2018_f_server_id_f_account_id_d_deal_time_idx on fact_deals_y2018 fd1_9 (cost=0.56..4.60 rows=3 width=20) (never executed)

  • Index Cond: ((f_server_id = aa3.f_server_id) AND (f_account_id = aa3.f_account_id) AND (d_deal_time >= ((gs.gs)::date)) AND (d_deal_time < ((((gs.gs)::date + '1 mon'::interval))::date)))
  • Heap Fetches: 0
27. 143.440 143.440 ↓ 0.0 0 28,688

Index Only Scan using fact_deals_y2019_f_server_id_f_account_id_d_deal_time_idx on fact_deals_y2019 fd1_10 (cost=0.56..3.55 rows=2 width=20) (actual time=0.005..0.005 rows=0 loops=28,688)

  • Index Cond: ((f_server_id = aa3.f_server_id) AND (f_account_id = aa3.f_account_id) AND (d_deal_time >= ((gs.gs)::date)) AND (d_deal_time < ((((gs.gs)::date + '1 mon'::interval))::date)))
  • Heap Fetches: 0
  • Buffers: shared hit=115042
28. 193.855 5,815.650 ↓ 0.0 0 193,855

Limit (cost=4,125.28..4,125.28 rows=1 width=16) (actual time=0.030..0.030 rows=0 loops=193,855)

  • Buffers: shared hit=2432535
29. 775.420 5,621.795 ↓ 0.0 0 193,855

Sort (cost=4,125.28..4,125.88 rows=243 width=16) (actual time=0.029..0.029 rows=0 loops=193,855)

  • Sort Key: fd2.d_deal_time DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2432535
30. 1,274.297 4,846.375 ↑ 34.7 7 193,855

Nested Loop (cost=0.71..4,124.06 rows=243 width=16) (actual time=0.009..0.025 rows=7 loops=193,855)

  • Buffers: shared hit=2432535
31. 387.710 387.710 ↓ 0.0 0 193,855

Index Scan using dim_accounts_f_profile_id_index on dim_accounts aa6 (cost=0.42..4.09 rows=3 width=12) (actual time=0.002..0.002 rows=0 loops=193,855)

  • Index Cond: (f_profile_id = cp.f_profile_id)
  • Buffers: shared hit=608911
32. 717.200 3,184.368 ↑ 47.3 45 28,688

Append (cost=0.29..1,351.21 rows=2,130 width=20) (actual time=0.043..0.111 rows=45 loops=28,688)

  • Buffers: shared hit=1823624
33. 57.376 57.376 ↓ 0.0 0 28,688

Index Only Scan using fact_deals_default_f_server_id_f_account_id_d_deal_time_idx on fact_deals_default fd2 (cost=0.29..19.64 rows=45 width=20) (actual time=0.002..0.002 rows=0 loops=28,688)

  • Index Cond: ((f_server_id = aa6.f_server_id) AND (f_account_id = aa6.f_account_id) AND (d_deal_time < (((gs.gs)::date))::timestamp with time zone))
  • Heap Fetches: 1076
  • Buffers: shared hit=57580
34. 86.064 86.064 ↓ 0.0 0 28,688

Index Only Scan using fact_deals_y2010_f_server_id_f_account_id_d_deal_time_idx on fact_deals_y2010 fd2_1 (cost=0.43..170.09 rows=360 width=20) (actual time=0.002..0.003 rows=0 loops=28,688)

  • Index Cond: ((f_server_id = aa6.f_server_id) AND (f_account_id = aa6.f_account_id) AND (d_deal_time < (((gs.gs)::date))::timestamp with time zone))
  • Heap Fetches: 12078
  • Buffers: shared hit=91683
35. 86.064 86.064 ↓ 0.0 0 28,688

Index Only Scan using fact_deals_y2011_f_server_id_f_account_id_d_deal_time_idx on fact_deals_y2011 fd2_2 (cost=0.56..207.43 rows=435 width=20) (actual time=0.003..0.003 rows=0 loops=28,688)

  • Index Cond: ((f_server_id = aa6.f_server_id) AND (f_account_id = aa6.f_account_id) AND (d_deal_time < (((gs.gs)::date))::timestamp with time zone))
  • Heap Fetches: 10004
  • Buffers: shared hit=119776
36. 86.064 86.064 ↓ 0.0 0 28,688

Index Only Scan using fact_deals_y2012_f_server_id_f_account_id_d_deal_time_idx on fact_deals_y2012 fd2_3 (cost=0.56..71.06 rows=146 width=20) (actual time=0.003..0.003 rows=0 loops=28,688)

  • Index Cond: ((f_server_id = aa6.f_server_id) AND (f_account_id = aa6.f_account_id) AND (d_deal_time < (((gs.gs)::date))::timestamp with time zone))
  • Heap Fetches: 12196
  • Buffers: shared hit=120507
37. 86.064 86.064 ↓ 0.0 0 28,688

Index Only Scan using fact_deals_y2013_f_server_id_f_account_id_d_deal_time_idx on fact_deals_y2013 fd2_4 (cost=0.56..123.52 rows=240 width=20) (actual time=0.003..0.003 rows=0 loops=28,688)

  • Index Cond: ((f_server_id = aa6.f_server_id) AND (f_account_id = aa6.f_account_id) AND (d_deal_time < (((gs.gs)::date))::timestamp with time zone))
  • Heap Fetches: 7038
  • Buffers: shared hit=118279
38. 114.752 114.752 ↑ 113.0 1 28,688

Index Only Scan using fact_deals_y2014_f_server_id_f_account_id_d_deal_time_idx on fact_deals_y2014 fd2_5 (cost=0.56..73.67 rows=113 width=20) (actual time=0.003..0.004 rows=1 loops=28,688)

  • Index Cond: ((f_server_id = aa6.f_server_id) AND (f_account_id = aa6.f_account_id) AND (d_deal_time < (((gs.gs)::date))::timestamp with time zone))
  • Heap Fetches: 18824
  • Buffers: shared hit=123707
39. 430.320 430.320 ↑ 15.6 10 28,688

Index Only Scan using fact_deals_y2015_f_server_id_f_account_id_d_deal_time_idx on fact_deals_y2015 fd2_6 (cost=0.56..126.57 rows=156 width=20) (actual time=0.003..0.015 rows=10 loops=28,688)

  • Index Cond: ((f_server_id = aa6.f_server_id) AND (f_account_id = aa6.f_account_id) AND (d_deal_time < (((gs.gs)::date))::timestamp with time zone))
  • Heap Fetches: 275284
  • Buffers: shared hit=251889
40. 573.760 573.760 ↑ 10.5 14 28,688

Index Only Scan using fact_deals_y2016_f_server_id_f_account_id_d_deal_time_idx on fact_deals_y2016 fd2_7 (cost=0.56..105.22 rows=147 width=20) (actual time=0.003..0.020 rows=14 loops=28,688)

  • Index Cond: ((f_server_id = aa6.f_server_id) AND (f_account_id = aa6.f_account_id) AND (d_deal_time < (((gs.gs)::date))::timestamp with time zone))
  • Heap Fetches: 409072
  • Buffers: shared hit=318861
41. 459.008 459.008 ↑ 13.9 11 28,688

Index Only Scan using fact_deals_y2017_f_server_id_f_account_id_d_deal_time_idx on fact_deals_y2017 fd2_8 (cost=0.56..107.69 rows=153 width=20) (actual time=0.004..0.016 rows=11 loops=28,688)

  • Index Cond: ((f_server_id = aa6.f_server_id) AND (f_account_id = aa6.f_account_id) AND (d_deal_time < (((gs.gs)::date))::timestamp with time zone))
  • Heap Fetches: 302971
  • Buffers: shared hit=266914
42. 344.256 344.256 ↑ 32.1 7 28,688

Index Only Scan using fact_deals_y2018_f_server_id_f_account_id_d_deal_time_idx on fact_deals_y2018 fd2_9 (cost=0.56..228.53 rows=225 width=20) (actual time=0.005..0.012 rows=7 loops=28,688)

  • Index Cond: ((f_server_id = aa6.f_server_id) AND (f_account_id = aa6.f_account_id) AND (d_deal_time < (((gs.gs)::date))::timestamp with time zone))
  • Heap Fetches: 189872
  • Buffers: shared hit=213820
43. 143.440 143.440 ↑ 55.0 2 28,688

Index Only Scan using fact_deals_y2019_f_server_id_f_account_id_d_deal_time_idx on fact_deals_y2019 fd2_10 (cost=0.56..107.14 rows=110 width=20) (actual time=0.003..0.005 rows=2 loops=28,688)

  • Index Cond: ((f_server_id = aa6.f_server_id) AND (f_account_id = aa6.f_account_id) AND (d_deal_time < (((gs.gs)::date))::timestamp with time zone))
  • Heap Fetches: 45527
  • Buffers: shared hit=140608
44. 0.000 581.565 ↓ 0.0 0 193,855

Limit (cost=0.84..19.16 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=193,855)

  • Buffers: shared hit=695273
45. 107.791 581.565 ↓ 0.0 0 193,855

Nested Loop (cost=0.84..19.16 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=193,855)

  • Buffers: shared hit=695273
46. 387.710 387.710 ↓ 0.0 0 193,855

Index Scan using dim_accounts_f_profile_id_index on dim_accounts aa2 (cost=0.42..4.09 rows=3 width=12) (actual time=0.002..0.002 rows=0 loops=193,855)

  • Index Cond: (f_profile_id = cp.f_profile_id)
  • Buffers: shared hit=608911
47. 86.064 86.064 ↓ 0.0 0 28,688

Index Scan using idx_open_positions_daily_slice_server_id_account_id on open_positions_daily_slice sl (cost=0.42..5.01 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=28,688)

  • Index Cond: ((server_id = aa2.f_server_id) AND (account_id = aa2.f_account_id))
  • Filter: (slice_date = (((date_trunc('MONTH'::text, ((((gs.gs)::date - '1 mon'::interval month))::date)::timestamp with time zone) + '1 mon -1 days'::interval))::date))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=86362
48. 193.855 581.565 ↓ 0.0 0 193,855

GroupAggregate (cost=0.99..216.87 rows=1 width=68) (actual time=0.003..0.003 rows=0 loops=193,855)

  • Group Key: fb1.f_profile_id
  • Buffers: shared hit=584177
49. 0.000 387.710 ↓ 0.0 0 193,855

Nested Loop Left Join (cost=0.99..216.83 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=193,855)

  • Buffers: shared hit=584177
50. 387.710 387.710 ↓ 0.0 0 193,855

Index Scan using fact_finance_bills_f_profile_id_index on fact_finance_bills fb1 (cost=0.43..10.87 rows=1 width=29) (actual time=0.002..0.002 rows=0 loops=193,855)

  • Index Cond: (f_profile_id = cp.f_profile_id)
  • Filter: (((date_part('year'::text, d_processed_at) * '100'::double precision) + date_part('month'::text, d_processed_at)) = ((date_part('year'::text, ((((date_trunc('MONTH'::text, ((gs.gs)::date)::timestamp with time zone) + '1 mon -1 days'::interval))::date))::timestamp without time zone) * '100'::double precision) + date_part('month'::text, ((((date_trunc('MONTH'::text, ((gs.gs)::date)::timestamp with time zone) + '1 mon -1 days'::interval))::date))::timestamp without time zone)))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=584085
51. 0.044 0.792 ↓ 0.0 0 22

Limit (cost=0.56..205.95 rows=1 width=19) (actual time=0.036..0.036 rows=0 loops=22)

  • Buffers: shared hit=92
52. 0.748 0.748 ↓ 0.0 0 22

Index Scan Backward using idx_fact_currenciesrates__index on fact_currenciesrates crr1 (cost=0.56..10,680.56 rows=52 width=19) (actual time=0.034..0.034 rows=0 loops=22)

  • Index Cond: (((c_currencyfrom)::text = (fb1.c_from_amount_currency_id)::text) AND ((c_currencyto)::text = 'EUR'::text) AND (d_tickdatetime <= fb1.d_processed_at))
  • Filter: (((date_part('year'::text, d_tickdatetime) * '100'::double precision) + date_part('month'::text, d_tickdatetime)) = ((date_part('year'::text, ((((date_trunc('MONTH'::text, ((gs.gs)::date)::timestamp with time zone) + '1 mon -1 days'::interval))::date))::timestamp without time zone) * '100'::double precision) + date_part('month'::text, ((((date_trunc('MONTH'::text, ((gs.gs)::date)::timestamp with time zone) + '1 mon -1 days'::interval))::date))::timestamp without time zone)))
  • Buffers: shared hit=92
Planning time : 4.395 ms
Execution time : 9,693.496 ms