explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6yUg

Settings
# exclusive inclusive rows x rows loops node
1. 7.602 920,910.683 ↓ 4.4 882 1

Hash Left Join (cost=1,198,454,349.46..1,198,454,395.22 rows=200 width=1,484) (actual time=920,895.450..920,910.683 rows=882 loops=1)

  • Hash Cond: (pl.reportdate = min_max.reportdate)
  • Functions: 192
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 42.832 ms, Inlining 327.912 ms, Optimization 2900.207 ms, Emission 1775.196 ms, Total 5046.148 ms
2.          

CTE capital_p_l_mm

3. 0.004 330,355.939 ↓ 0.0 0 1

GroupAggregate (cost=1,975,411.31..1,975,411.36 rows=1 width=92) (actual time=330,355.939..330,355.939 rows=0 loops=1)

  • Group Key: a.user_id, d.account, ((d.reportdate)::date)
4. 0.008 330,355.935 ↓ 0.0 0 1

Sort (cost=1,975,411.31..1,975,411.32 rows=1 width=59) (actual time=330,355.935..330,355.935 rows=0 loops=1)

  • Sort Key: a.user_id, d.account, ((d.reportdate)::date)
  • Sort Method: quicksort Memory: 25kB
5. 0.000 330,355.927 ↓ 0.0 0 1

Nested Loop (cost=1,001.00..1,975,411.30 rows=1 width=59) (actual time=330,355.926..330,355.927 rows=0 loops=1)

6. 22.283 330,356.013 ↓ 0.0 0 1

Gather (cost=1,000.57..1,975,408.65 rows=1 width=55) (actual time=330,355.924..330,356.013 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 330,333.730 330,333.730 ↓ 0.0 0 3

Parallel Index Scan using ix_tbl_src_finres_b2c_venue_daily_2 on src_finres_b2c_venue_daily d (cost=0.56..1,974,408.55 rows=1 width=55) (actual time=330,333.730..330,333.730 rows=0 loops=3)

  • Index Cond: ((metric_type)::text = ANY ('{TRADE_CORRECTION,OTHER,REFUND,TRADE_FEE,TRADE_SLIPPAGE_CORRECTION,INACTIVITY_FEE,NEGATIVE_BALANCE_PROTECTION,ADJUSTMENT,COMMISSION_ECOMMPAY,COMMISSION_PAYPAL,RPL_NEG,RPL_POS,SWAP,LP_PER_TRADE_COMMISSION,LP_SWAP,UPL,UPL_NEG,UPL_POS,VOLUME,REIMBURSEMENT,NEGATIVE_BALANCE_PROTECTION,DEPOSIT,WITHDRAWAL,PAYMENT_SYSTEM_FEE,COMMISSION_BANK,COMMISSION_SOFORT,USER_TR_COMMISION}'::text[]))
  • Filter: (((account)::text ~ '[0-9]'::text) AND ((report_type)::text = ''::text))
  • Rows Removed by Filter: 14479469
8. 0.000 0.000 ↓ 0.0 0

Index Scan using as_account_pkey on as_account a (cost=0.43..2.65 rows=1 width=16) (never executed)

  • Index Cond: (id = (d.account)::bigint)
9.          

CTE capital_p_l_mm_2

10. 0.004 330,356.088 ↓ 0.0 0 1

WindowAgg (cost=0.03..0.06 rows=1 width=134) (actual time=330,356.088..330,356.088 rows=0 loops=1)

11. 0.143 330,356.084 ↓ 0.0 0 1

Sort (cost=0.03..0.04 rows=1 width=134) (actual time=330,356.084..330,356.084 rows=0 loops=1)

  • Sort Key: capital_p_l_mm.user_id, capital_p_l_mm.reportdate
  • Sort Method: quicksort Memory: 25kB
12. 330,355.941 330,355.941 ↓ 0.0 0 1

CTE Scan on capital_p_l_mm (cost=0.00..0.02 rows=1 width=134) (actual time=330,355.941..330,355.941 rows=0 loops=1)

13.          

CTE min_max

14. 0.003 330,356.093 ↓ 0.0 0 1

HashAggregate (cost=0.05..0.06 rows=1 width=260) (actual time=330,356.093..330,356.093 rows=0 loops=1)

  • Group Key: capital_p_l_mm_2.reportdate
15. 330,356.090 330,356.090 ↓ 0.0 0 1

CTE Scan on capital_p_l_mm_2 (cost=0.00..0.02 rows=1 width=126) (actual time=330,356.090..330,356.090 rows=0 loops=1)

16.          

CTE capital_p_l

17. 150.766 590,400.511 ↑ 16.7 16,185 1

Finalize GroupAggregate (cost=943,600,463.37..1,196,311,300.85 rows=269,984 width=404) (actual time=539,947.777..590,400.511 rows=16,185 loops=1)

  • Group Key: (date(p.reportdate)), (CASE WHEN ((p.venue)::text = 'CAPITALCOM'::text) THEN 'CAPITALCOM'::text ELSE 'LP'::text END), (CASE WHEN ((p.metric_type)::text = ANY ('{RPL_NEG,RPL_POS}'::text[])) THEN 'RPL'::character varying WHEN ((p.metric_type)::text = ANY ('{UPL_NEG,UPL_POS,UPL}'::text[])) THEN 'UPL'::character varying WHEN ((p.metric_type)::text = ANY ('{SWAP,LP_SWAP}'::text[])) THEN 'SWAP'::character varying WHEN ((p.metric_type)::text ~~ 'COMMISSION%'::text) THEN 'PAYMENT_SYSTEMS_FEE'::character varying WHEN ((p.metric_type)::text = 'PAYMENT_SYSTEM_FEE'::text) THEN 'PAYMENT_SYSTEM_FEE'::character varying ELSE p.metric_type END)
18. 0.000 590,249.745 ↑ 15.2 35,465 1

Gather Merge (cost=943,600,463.37..1,196,263,041.21 rows=539,968 width=404) (actual time=539,931.317..590,249.745 rows=35,465 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
19. 126,186.084 1,766,914.200 ↑ 22.8 11,822 3

Partial GroupAggregate (cost=943,599,463.34..1,196,199,715.48 rows=269,984 width=404) (actual time=538,930.941..588,971.400 rows=11,822 loops=3)

  • Group Key: (date(p.reportdate)), (CASE WHEN ((p.venue)::text = 'CAPITALCOM'::text) THEN 'CAPITALCOM'::text ELSE 'LP'::text END), (CASE WHEN ((p.metric_type)::text = ANY ('{RPL_NEG,RPL_POS}'::text[])) THEN 'RPL'::character varying WHEN ((p.metric_type)::text = ANY ('{UPL_NEG,UPL_POS,UPL}'::text[])) THEN 'UPL'::character varying WHEN ((p.metric_type)::text = ANY ('{SWAP,LP_SWAP}'::text[])) THEN 'SWAP'::character varying WHEN ((p.metric_type)::text ~~ 'COMMISSION%'::text) THEN 'PAYMENT_SYSTEMS_FEE'::character varying WHEN ((p.metric_type)::text = 'PAYMENT_SYSTEM_FEE'::text) THEN 'PAYMENT_SYSTEM_FEE'::character varying ELSE p.metric_type END)
20. 285,751.275 1,640,728.116 ↑ 150.7 16,764,218 3

Sort (cost=943,599,463.34..949,914,106.86 rows=2,525,857,405 width=151) (actual time=538,910.461..546,909.372 rows=16,764,218 loops=3)

  • Sort Key: (date(p.reportdate)) DESC, (CASE WHEN ((p.venue)::text = 'CAPITALCOM'::text) THEN 'CAPITALCOM'::text ELSE 'LP'::text END), (CASE WHEN ((p.metric_type)::text = ANY ('{RPL_NEG,RPL_POS}'::text[])) THEN 'RPL'::character varying WHEN ((p.metric_type)::text = ANY ('{UPL_NEG,UPL_POS,UPL}'::text[])) THEN 'UPL'::character varying WHEN ((p.metric_type)::text = ANY ('{SWAP,LP_SWAP}'::text[])) THEN 'SWAP'::character varying WHEN ((p.metric_type)::text ~~ 'COMMISSION%'::text) THEN 'PAYMENT_SYSTEMS_FEE'::character varying WHEN ((p.metric_type)::text = 'PAYMENT_SYSTEM_FEE'::text) THEN 'PAYMENT_SYSTEM_FEE'::character varying ELSE p.metric_type END)
  • Sort Method: external merge Disk: 2560976kB
  • Worker 0: Sort Method: external merge Disk: 2562848kB
  • Worker 1: Sort Method: external merge Disk: 2545872kB
21. 30,987.000 1,354,976.841 ↑ 150.7 16,764,218 3

Merge Left Join (cost=6,058,808.34..104,149,682.89 rows=2,525,857,405 width=151) (actual time=415,961.146..451,658.947 rows=16,764,218 loops=3)

  • Merge Cond: ((p.account)::text = ((a_1.id)::text))
22. 252,179.493 1,287,323.475 ↑ 1.3 16,764,218 3

Sort (cost=5,793,286.96..5,845,751.08 rows=20,985,647 width=76) (actual time=408,785.358..429,107.825 rows=16,764,218 loops=3)

  • Sort Key: p.account
  • Sort Method: external merge Disk: 1536264kB
  • Worker 0: Sort Method: external merge Disk: 1538024kB
  • Worker 1: Sort Method: external merge Disk: 1527752kB
23. 18,268.410 1,035,143.982 ↑ 1.3 16,764,218 3

Hash Left Join (cost=1,108.64..2,148,806.03 rows=20,985,647 width=76) (actual time=1,531.777..345,047.994 rows=16,764,218 loops=3)

  • Hash Cond: (((p.account_currency)::text = ts_fx_daily.currency) AND (date(p.reportdate) = ts_fx_daily.date))
24. 1,016,840.529 1,016,840.529 ↑ 1.3 16,764,218 3

Parallel Index Scan using ix_tbl_src_finres_b2c_venue_daily_2 on src_finres_b2c_venue_daily p (cost=0.56..2,034,892.42 rows=20,985,647 width=72) (actual time=1,519.966..338,946.843 rows=16,764,218 loops=3)

  • Index Cond: ((metric_type)::text = ANY ('{OTHER,REFUND,TRADE_CORRECTION,TRADE_FEE,TRADE_SLIPPAGE_CORRECTION,INACTIVITY_FEE,NEGATIVE_BALANCE_PROTECTION,ADJUSTMENT,COMMISSION_ECOMMPAY,COMMISSION_PAYPAL,RPL_NEG,RPL_POS,SWAP,LP_PER_TRADE_COMMISSION,LP_SWAP,UPL,UPL_NEG,UPL_POS,VOLUME,REIMBURSEMENT,NEGATIVE_BALANCE_PROTECTION,DEPOSIT,WITHDRAWAL,PAYMENT_SYSTEM_FEE,COMMISSION_BANK,COMMISSION_SOFORT,USER_TR_COMMISION,EQUITY}'::text[]))
  • Filter: ((report_type)::text = '17H_NY'::text)
25. 21.318 35.043 ↑ 1.0 34,152 3

Hash (cost=595.03..595.03 rows=34,203 width=16) (actual time=11.680..11.681 rows=34,152 loops=3)

  • Buckets: 65536 Batches: 1 Memory Usage: 2184kB
26. 13.725 13.725 ↑ 1.0 34,152 3

Seq Scan on ts_fx_daily (cost=0.00..595.03 rows=34,203 width=16) (actual time=0.140..4.575 rows=34,152 loops=3)

27. 3,470.058 36,666.366 ↓ 11.2 18,094,707 3

Materialize (cost=265,521.38..273,609.05 rows=1,617,534 width=23) (actual time=7,175.655..12,222.122 rows=18,094,707 loops=3)

28. 26,530.197 33,196.308 ↑ 1.2 1,360,002 3

Sort (cost=265,521.38..269,565.22 rows=1,617,534 width=23) (actual time=7,175.646..11,065.436 rows=1,360,002 loops=3)

  • Sort Key: ((a_1.id)::text)
  • Sort Method: external merge Disk: 62248kB
  • Worker 0: Sort Method: external merge Disk: 62248kB
  • Worker 1: Sort Method: external merge Disk: 62248kB
29. 6,666.111 6,666.111 ↑ 1.2 1,360,002 3

Seq Scan on as_account a_1 (cost=0.00..79,280.34 rows=1,617,534 width=23) (actual time=1.756..2,222.037 rows=1,360,002 loops=3)

30.          

CTE capital_p_l_upl

31. 11.761 590,481.429 ↑ 16.7 16,185 1

WindowAgg (cost=58,943.53..71,767.77 rows=269,984 width=532) (actual time=590,466.935..590,481.429 rows=16,185 loops=1)

32. 38.329 590,469.668 ↑ 16.7 16,185 1

Sort (cost=58,943.53..59,618.49 rows=269,984 width=404) (actual time=590,466.877..590,469.668 rows=16,185 loops=1)

  • Sort Key: pl_1.venue, pl_1.metric_type, pl_1.reportdate
  • Sort Method: quicksort Memory: 2661kB
33. 590,431.339 590,431.339 ↑ 16.7 16,185 1

CTE Scan on capital_p_l pl_1 (cost=0.00..5,399.68 rows=269,984 width=404) (actual time=539,947.785..590,431.339 rows=16,185 loops=1)

34.          

CTE pl

35. 53.823 590,545.223 ↓ 4.4 882 1

HashAggregate (cost=95,844.32..95,869.32 rows=200 width=1,428) (actual time=590,539.225..590,545.223 rows=882 loops=1)

  • Group Key: capital_p_l_upl.reportdate
36. 590,491.400 590,491.400 ↑ 16.7 16,185 1

CTE Scan on capital_p_l_upl (cost=0.00..5,399.68 rows=269,984 width=436) (actual time=590,466.938..590,491.400 rows=16,185 loops=1)

37. 590,546.985 590,546.985 ↓ 4.4 882 1

CTE Scan on pl (cost=0.00..4.00 rows=200 width=1,428) (actual time=590,539.231..590,546.985 rows=882 loops=1)

38. 0.001 330,356.096 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=132) (actual time=330,356.096..330,356.096 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
39. 330,356.095 330,356.095 ↓ 0.0 0 1

CTE Scan on min_max (cost=0.00..0.02 rows=1 width=132) (actual time=330,356.095..330,356.095 rows=0 loops=1)

Planning time : 9.735 ms
Execution time : 921,048.538 ms