explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ODoV

Settings
# exclusive inclusive rows x rows loops node
1. 13.313 948,842.023 ↓ 4.4 882 1

Hash Left Join (cost=352,161,424.54..352,161,470.30 rows=200 width=1,484) (actual time=948,816.294..948,842.023 rows=882 loops=1)

  • Hash Cond: (pl.reportdate = min_max.reportdate)
2.          

CTE capital_p_l_mm

3. 0.002 328,146.824 ↓ 0.0 0 1

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

  • Group Key: a.user_id, d.account, ((d.reportdate)::date)
4. 0.012 328,146.822 ↓ 0.0 0 1

Sort (cost=2,020,702.09..2,020,702.09 rows=1 width=59) (actual time=328,146.821..328,146.822 rows=0 loops=1)

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

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

6. 37.406 328,146.891 ↓ 0.0 0 1

Gather (cost=1,000.57..2,020,699.42 rows=1 width=55) (actual time=328,146.808..328,146.891 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 328,109.485 328,109.485 ↓ 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=328,109.484..328,109.485 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.003 328,146.835 ↓ 0.0 0 1

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

11. 0.006 328,146.832 ↓ 0.0 0 1

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

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

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

13.          

CTE min_max

14. 0.001 328,146.837 ↓ 0.0 0 1

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

  • Group Key: capital_p_l_mm_2.reportdate
15. 328,146.836 328,146.836 ↓ 0.0 0 1

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

16.          

CTE capital_p_l

17. 91.069 620,538.463 ↑ 16.7 16,185 1

Finalize GroupAggregate (cost=349,891,012.76..350,002,273.06 rows=269,984 width=404) (actual time=620,427.999..620,538.463 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 620,447.394 ↑ 14.6 36,863 1

Gather Merge (cost=349,891,012.76..349,954,013.42 rows=539,968 width=404) (actual time=620,427.958..620,447.394 rows=36,863 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
19. 47.073 1,857,738.507 ↑ 22.0 12,288 3

Sort (cost=349,890,012.74..349,890,687.70 rows=269,984 width=404) (actual time=619,244.193..619,246.169 rows=12,288 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: quicksort Memory: 7140kB
  • Worker 0: Sort Method: quicksort Memory: 6391kB
  • Worker 1: Sort Method: quicksort Memory: 6630kB
20. 151,930.146 1,857,691.434 ↑ 22.0 12,288 3

Partial HashAggregate (cost=349,851,145.14..349,865,656.78 rows=269,984 width=404) (actual time=619,202.551..619,230.478 rows=12,288 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
21. 32,589.702 1,705,761.288 ↑ 150.7 16,764,218 3

Merge Left Join (cost=5,493,217.44..103,580,048.16 rows=2,525,857,405 width=151) (actual time=551,059.183..568,587.096 rows=16,764,218 loops=3)

  • Merge Cond: ((p.account)::text = ((a_1.id)::text))
22. 169,768.737 1,641,337.743 ↑ 1.3 16,764,218 3

Sort (cost=5,247,125.96..5,299,590.08 rows=20,985,647 width=76) (actual time=541,622.804..547,112.581 rows=16,764,218 loops=3)

  • Sort Key: p.account
  • Sort Method: external merge Disk: 1530520kB
  • Worker 0: Sort Method: external merge Disk: 1533184kB
  • Worker 1: Sort Method: external merge Disk: 1538024kB
23. 23,364.957 1,471,569.006 ↑ 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=15.915..490,523.002 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,448,157.048 1,448,157.048 ↑ 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=0.020..482,719.016 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. 29.463 47.001 ↑ 1.0 34,152 3

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

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

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

27. 23,594.316 31,833.843 ↓ 11.2 18,094,865 3

Sort (cost=246,091.48..250,135.32 rows=1,617,534 width=23) (actual time=9,436.346..10,611.281 rows=18,094,865 loops=3)

  • Sort Key: ((a_1.id)::text)
  • Sort Method: quicksort Memory: 175616kB
  • Worker 0: Sort Method: quicksort Memory: 175616kB
  • Worker 1: Sort Method: quicksort Memory: 175616kB
28. 8,239.527 8,239.527 ↑ 1.2 1,360,156 3

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

29.          

CTE capital_p_l_upl

30. 15.425 620,598.588 ↑ 16.7 16,185 1

WindowAgg (cost=29,755.63..42,579.87 rows=269,984 width=532) (actual time=620,580.587..620,598.588 rows=16,185 loops=1)

31. 31.787 620,583.163 ↑ 16.7 16,185 1

Sort (cost=29,755.63..30,430.59 rows=269,984 width=404) (actual time=620,580.566..620,583.163 rows=16,185 loops=1)

  • Sort Key: pl_1.venue, pl_1.metric_type, pl_1.reportdate
  • Sort Method: quicksort Memory: 2661kB
32. 620,551.376 620,551.376 ↑ 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=620,428.004..620,551.376 rows=16,185 loops=1)

33.          

CTE pl

34. 70.628 620,679.228 ↓ 4.4 882 1

HashAggregate (cost=95,844.32..95,869.32 rows=200 width=1,428) (actual time=620,669.371..620,679.228 rows=882 loops=1)

  • Group Key: capital_p_l_upl.reportdate
35. 620,608.600 620,608.600 ↑ 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=620,580.590..620,608.600 rows=16,185 loops=1)

36. 620,681.870 620,681.870 ↓ 4.4 882 1

CTE Scan on pl (cost=0.00..4.00 rows=200 width=1,428) (actual time=620,669.376..620,681.870 rows=882 loops=1)

37. 0.001 328,146.840 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
38. 328,146.839 328,146.839 ↓ 0.0 0 1

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

Planning time : 1.890 ms
Execution time : 948,924.452 ms