explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7IMH

Settings
# exclusive inclusive rows x rows loops node
1. 12.365 597,624.294 ↓ 4.4 882 1

Hash Left Join (cost=1,198,499,640.23..1,198,499,685.99 rows=200 width=1,484) (actual time=597,599.667..597,624.294 rows=882 loops=1)

  • Hash Cond: (pl.reportdate = min_max.reportdate)
  • Functions: 192
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 30.907 ms, Inlining 310.639 ms, Optimization 2685.130 ms, Emission 1742.672 ms, Total 4769.349 ms
2.          

CTE capital_p_l_mm

3. 0.005 163,992.987 ↓ 0.0 0 1

GroupAggregate (cost=2,020,702.09..2,020,702.13 rows=1 width=92) (actual time=163,992.987..163,992.987 rows=0 loops=1)

  • Group Key: a.user_id, d.account, ((d.reportdate)::date)
4. 0.010 163,992.982 ↓ 0.0 0 1

Sort (cost=2,020,702.09..2,020,702.09 rows=1 width=59) (actual time=163,992.982..163,992.982 rows=0 loops=1)

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

Nested Loop (cost=1,001.00..2,020,702.08 rows=1 width=59) (actual time=163,992.972..163,992.972 rows=0 loops=1)

6. 20.798 163,993.090 ↓ 0.0 0 1

Gather (cost=1,000.57..2,020,699.42 rows=1 width=55) (actual time=163,992.970..163,993.090 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 163,972.292 163,972.292 ↓ 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..2,019,699.32 rows=1 width=55) (actual time=163,972.292..163,972.292 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: (((report_type)::text = ''::text) AND (regexp_replace((account)::text, '[^0-9]'::text, ''::text, 'g'::text) = (account)::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.005 163,993.002 ↓ 0.0 0 1

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

11. 0.009 163,992.997 ↓ 0.0 0 1

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

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

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

13.          

CTE min_max

14. 0.003 163,993.006 ↓ 0.0 0 1

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

  • Group Key: capital_p_l_mm_2.reportdate
15. 163,993.003 163,993.003 ↓ 0.0 0 1

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

16.          

CTE capital_p_l

17. 161.543 433,444.035 ↑ 16.7 16,185 1

Finalize GroupAggregate (cost=943,600,463.37..1,196,311,300.85 rows=269,984 width=404) (actual time=377,077.447..433,444.035 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 433,282.492 ↑ 16.0 33,785 1

Gather Merge (cost=943,600,463.37..1,196,263,041.21 rows=539,968 width=404) (actual time=377,074.803..433,282.492 rows=33,785 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
19. 137,317.626 1,284,225.933 ↑ 24.0 11,262 3

Partial GroupAggregate (cost=943,599,463.34..1,196,199,715.48 rows=269,984 width=404) (actual time=373,483.212..428,075.311 rows=11,262 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. 262,954.332 1,146,908.307 ↑ 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=373,444.441..382,302.769 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: 2560376kB
  • Worker 0: Sort Method: external merge Disk: 2530496kB
  • Worker 1: Sort Method: external merge Disk: 2579080kB
21. 28,793.790 883,953.975 ↑ 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=271,859.877..294,651.325 rows=16,764,218 loops=3)

  • Merge Cond: ((p.account)::text = ((a_1.id)::text))
22. 239,358.153 808,687.077 ↑ 1.3 16,764,218 3

Sort (cost=5,793,286.96..5,845,751.08 rows=20,985,647 width=76) (actual time=259,952.137..269,562.359 rows=16,764,218 loops=3)

  • Sort Key: p.account
  • Sort Method: external merge Disk: 1536080kB
  • Worker 0: Sort Method: external merge Disk: 1518120kB
  • Worker 1: Sort Method: external merge Disk: 1547864kB
23. 18,668.004 569,328.924 ↑ 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,449.051..189,776.308 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. 550,625.754 550,625.754 ↑ 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,437.197..183,541.918 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. 18.897 35.166 ↑ 1.0 34,152 3

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

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

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

27. 3,237.831 46,473.108 ↓ 11.2 18,094,589 3

Materialize (cost=265,521.38..273,609.05 rows=1,617,534 width=23) (actual time=11,907.599..15,491.036 rows=18,094,589 loops=3)

28. 23,383.929 43,235.277 ↑ 1.2 1,359,884 3

Sort (cost=265,521.38..269,565.22 rows=1,617,534 width=23) (actual time=11,907.593..14,411.759 rows=1,359,884 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. 19,851.348 19,851.348 ↑ 1.2 1,359,884 3

Seq Scan on as_account a_1 (cost=0.00..79,280.34 rows=1,617,534 width=23) (actual time=0.352..6,617.116 rows=1,359,884 loops=3)

30.          

CTE capital_p_l_upl

31. 14.357 433,537.421 ↑ 16.7 16,185 1

WindowAgg (cost=58,943.53..71,767.77 rows=269,984 width=532) (actual time=433,520.248..433,537.421 rows=16,185 loops=1)

32. 46.006 433,523.064 ↑ 16.7 16,185 1

Sort (cost=58,943.53..59,618.49 rows=269,984 width=404) (actual time=433,520.192..433,523.064 rows=16,185 loops=1)

  • Sort Key: pl_1.venue, pl_1.metric_type, pl_1.reportdate
  • Sort Method: quicksort Memory: 2661kB
33. 433,477.058 433,477.058 ↑ 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=377,077.454..433,477.058 rows=16,185 loops=1)

34.          

CTE pl

35. 66.574 433,616.317 ↓ 4.4 882 1

HashAggregate (cost=95,844.32..95,869.32 rows=200 width=1,428) (actual time=433,606.521..433,616.317 rows=882 loops=1)

  • Group Key: capital_p_l_upl.reportdate
36. 433,549.743 433,549.743 ↑ 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=433,520.251..433,549.743 rows=16,185 loops=1)

37. 433,618.919 433,618.919 ↓ 4.4 882 1

CTE Scan on pl (cost=0.00..4.00 rows=200 width=1,428) (actual time=433,606.527..433,618.919 rows=882 loops=1)

38. 0.001 163,993.010 ↓ 0.0 0 1

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

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

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

Planning time : 5.744 ms
Execution time : 598,228.225 ms