explain.depesz.com

PostgreSQL's explain analyze made readable

Result: n8bu

Settings
# exclusive inclusive rows x rows loops node
1. 199.786 917,506.637 ↑ 302.5 84,904 1

Finalize GroupAggregate (cost=5,004,240.72..10,439,074.70 rows=25,683,983 width=276) (actual time=917,178.782..917,506.637 rows=84,904 loops=1)

  • Group Key: (date(p.reportdate)), a.user_id, (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}'::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)
2. 0.000 917,306.851 ↑ 234.9 91,117 1

Gather Merge (cost=5,004,240.72..8,437,864.32 rows=21,403,320 width=276) (actual time=917,178.734..917,306.851 rows=91,117 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 227.463 2,751,627.651 ↑ 352.4 30,372 3

Partial GroupAggregate (cost=5,003,240.70..5,966,390.10 rows=10,701,660 width=276) (actual time=917,130.267..917,209.217 rows=30,372 loops=3)

  • Group Key: (date(p.reportdate)), a.user_id, (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}'::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)
4. 82.596 2,751,400.188 ↑ 338.9 31,573 3

Sort (cost=5,003,240.70..5,029,994.85 rows=10,701,660 width=128) (actual time=917,130.222..917,133.396 rows=31,573 loops=3)

  • Sort Key: a.user_id, (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}'::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: 5380kB
  • Worker 0: Sort Method: quicksort Memory: 5312kB
  • Worker 1: Sort Method: quicksort Memory: 5194kB
5. 1,317.900 2,751,317.592 ↑ 338.9 31,573 3

Merge Join (cost=2,533,854.49..2,939,630.06 rows=10,701,660 width=128) (actual time=914,976.438..917,105.864 rows=31,573 loops=3)

  • Merge Cond: ((p.account)::text = ((a.id)::text))
6. 293.532 2,726,859.579 ↑ 3.4 31,573 3

Sort (cost=2,304,955.29..2,305,220.97 rows=106,275 width=76) (actual time=908,948.537..908,953.193 rows=31,573 loops=3)

  • Sort Key: p.account
  • Sort Method: quicksort Memory: 5281kB
  • Worker 0: Sort Method: quicksort Memory: 5230kB
  • Worker 1: Sort Method: quicksort Memory: 5114kB
7. 45.201 2,726,566.047 ↑ 3.4 31,573 3

Hash Left Join (cost=19.57..2,293,315.18 rows=106,275 width=76) (actual time=2,273.187..908,855.349 rows=31,573 loops=3)

  • Hash Cond: ((date(p.reportdate) = ts_fx_daily.date) AND ((p.account_currency)::text = ts_fx_daily.currency))
8. 2,726,519.712 2,726,519.712 ↑ 3.4 31,573 3

Parallel Index Scan using ix_tbl_src_finres_b2c_venue_daily_2 on src_finres_b2c_venue_daily p (cost=0.56..2,292,680.69 rows=106,275 width=72) (actual time=2,272.737..908,839.904 rows=31,573 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) AND ((venue)::text = 'CAPITALCOM'::text) AND ((reportdate)::date = '2019-10-04'::date))
  • Rows Removed by Filter: 16859094
9. 0.078 1.134 ↓ 2.9 98 3

Hash (cost=18.49..18.49 rows=34 width=16) (actual time=0.378..0.378 rows=98 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
10. 1.056 1.056 ↓ 2.9 98 3

Index Scan using ts_fx_daily_pkey on ts_fx_daily (cost=0.29..18.49 rows=34 width=16) (actual time=0.227..0.352 rows=98 loops=3)

  • Index Cond: (date = '2019-10-04'::date)
11. 401.244 23,140.113 ↓ 1.0 1,381,797 3

Materialize (cost=228,899.21..235,700.34 rows=1,360,226 width=16) (actual time=6,027.848..7,713.371 rows=1,381,797 loops=3)

12. 19,572.930 22,738.869 ↓ 1.0 1,364,240 3

Sort (cost=228,899.21..232,299.77 rows=1,360,226 width=16) (actual time=6,027.842..7,579.623 rows=1,364,240 loops=3)

  • Sort Key: ((a.id)::text)
  • Sort Method: external merge Disk: 57232kB
  • Worker 0: Sort Method: external merge Disk: 57232kB
  • Worker 1: Sort Method: external merge Disk: 57232kB
13. 3,165.939 3,165.939 ↓ 1.0 1,364,329 3

Seq Scan on as_account a (cost=0.00..76,707.26 rows=1,360,226 width=16) (actual time=0.171..1,055.313 rows=1,364,329 loops=3)

Planning time : 9.284 ms
Execution time : 917,528.694 ms