explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xHw9

Settings
# exclusive inclusive rows x rows loops node
1. 233.677 874,338.444 ↑ 3.0 84,904 1

Finalize GroupAggregate (cost=2,444,766.52..2,497,032.88 rows=255,035 width=276) (actual time=873,974.971..874,338.444 rows=84,904 loops=1)

  • Group Key: (date(p.reportdate)), a.user_id, (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 874,104.767 ↑ 2.3 91,444 1

Gather Merge (cost=2,444,766.52..2,478,330.28 rows=212,530 width=244) (actual time=873,974.945..874,104.767 rows=91,444 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 277.713 2,622,028.560 ↑ 3.5 30,481 3

Partial GroupAggregate (cost=2,443,766.49..2,452,799.02 rows=106,265 width=244) (actual time=873,912.498..874,009.520 rows=30,481 loops=3)

  • Group Key: (date(p.reportdate)), a.user_id, (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. 136.479 2,621,750.847 ↑ 3.4 31,573 3

Sort (cost=2,443,766.49..2,444,032.16 rows=106,265 width=96) (actual time=873,912.449..873,916.949 rows=31,573 loops=3)

  • Sort Key: a.user_id, (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: 5231kB
  • Worker 0: Sort Method: quicksort Memory: 5285kB
  • Worker 1: Sort Method: quicksort Memory: 5348kB
5. 165.669 2,621,614.368 ↑ 3.4 31,573 3

Hash Left Join (cost=20.00..2,431,702.95 rows=106,265 width=96) (actual time=37,372.272..873,871.456 rows=31,573 loops=3)

  • Hash Cond: ((date(p.reportdate) = ts_fx_daily.date) AND ((p.account_currency)::text = ts_fx_daily.currency))
6. 137.828 2,621,424.264 ↑ 3.4 31,573 3

Nested Loop (cost=1.00..2,429,474.54 rows=106,265 width=64) (actual time=37,364.051..873,808.088 rows=31,573 loops=3)

7. 2,549,490.192 2,549,490.192 ↑ 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,345,848.32 rows=106,265 width=72) (actual time=37,350.385..849,830.064 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: (((account)::text ~ '[0-9]'::text) AND ((report_type)::text = '17H_NY'::text) AND ((venue)::text = 'CAPITALCOM'::text) AND ((reportdate)::date = '2019-10-04'::date))
  • Rows Removed by Filter: 16859094
8. 71,796.244 71,796.244 ↑ 1.0 1 94,718

Index Scan using as_account_pkey on as_account a (cost=0.43..0.79 rows=1 width=16) (actual time=0.758..0.758 rows=1 loops=94,718)

  • Index Cond: (id = (p.account)::bigint)
9. 0.123 24.435 ↓ 2.9 98 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
10. 24.312 24.312 ↓ 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=4.769..8.104 rows=98 loops=3)

  • Index Cond: (date = '2019-10-04'::date)
Planning time : 0.572 ms
Execution time : 874,343.464 ms