explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BjV1

Settings
# exclusive inclusive rows x rows loops node
1. 2.672 41,471.909 ↑ 1.0 1 1

Aggregate (cost=1,015,761,875.26..1,015,761,875.28 rows=1 width=32) (actual time=41,471.909..41,471.909 rows=1 loops=1)

2.          

CTE mid_market_price_table

3. 12.973 39,200.416 ↑ 1,353.3 9,291 1

Hash Right Join (cost=732,363.27..900,779.94 rows=12,573,568 width=48) (actual time=39,127.199..39,200.416 rows=9,291 loops=1)

  • Hash Cond: (radar_orderbook_usd_prices.observed_timestamp = radar_orderbook_usd_prices_1.observed_timestamp)
4. 11.747 9,798.400 ↑ 5.5 9,291 1

Finalize GroupAggregate (cost=360,999.03..371,593.91 rows=51,433 width=40) (actual time=9,737.779..9,798.400 rows=9,291 loops=1)

  • Group Key: radar_orderbook_usd_prices.observed_timestamp
5. 0.000 9,786.653 ↑ 6.1 12,790 1

Gather Merge (cost=360,999.03..370,689.39 rows=78,038 width=40) (actual time=9,737.752..9,786.653 rows=12,790 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 64.512 29,273.718 ↑ 9.2 4,263 3

Partial GroupAggregate (cost=359,999.01..360,681.84 rows=39,019 width=40) (actual time=9,718.209..9,757.906 rows=4,263 loops=3)

  • Group Key: radar_orderbook_usd_prices.observed_timestamp
7. 613.563 29,209.206 ↓ 1.1 43,377 3

Sort (cost=359,999.01..360,096.56 rows=39,019 width=18) (actual time=9,718.188..9,736.402 rows=43,377 loops=3)

  • Sort Key: radar_orderbook_usd_prices.observed_timestamp
  • Sort Method: quicksort Memory: 3976kB
8. 28,595.643 28,595.643 ↓ 1.1 43,377 3

Parallel Seq Scan on radar_orderbook_usd_prices (cost=0.00..357,023.44 rows=39,019 width=18) (actual time=4,321.854..9,531.881 rows=43,377 loops=3)

  • Filter: (((quote_asset_symbol)::text = 'WETH'::text) AND ((base_asset_symbol)::text = 'USDC'::text) AND (order_type = 'ask'::order_t) AND ((observed_timestamp / 1000) >= 1559347200))
  • Rows Removed by Filter: 2979928
9. 5.617 29,389.043 ↑ 5.3 9,291 1

Hash (cost=370,753.07..370,753.07 rows=48,893 width=40) (actual time=29,389.043..29,389.043 rows=9,291 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 1010kB
10. 10.733 29,383.426 ↑ 5.3 9,291 1

Finalize GroupAggregate (cost=360,681.56..370,264.14 rows=48,893 width=40) (actual time=29,299.176..29,383.426 rows=9,291 loops=1)

  • Group Key: radar_orderbook_usd_prices_1.observed_timestamp
11. 0.000 29,372.693 ↑ 5.6 12,578 1

Gather Merge (cost=360,681.56..369,423.22 rows=70,398 width=40) (actual time=29,299.152..29,372.693 rows=12,578 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 105.891 88,071.486 ↑ 8.4 4,193 3

Partial GroupAggregate (cost=359,681.54..360,297.52 rows=35,199 width=40) (actual time=29,289.157..29,357.162 rows=4,193 loops=3)

  • Group Key: radar_orderbook_usd_prices_1.observed_timestamp
13. 364.647 87,965.595 ↓ 1.2 40,969 3

Sort (cost=359,681.54..359,769.54 rows=35,199 width=18) (actual time=29,289.130..29,321.865 rows=40,969 loops=3)

  • Sort Key: radar_orderbook_usd_prices_1.observed_timestamp
  • Sort Method: quicksort Memory: 4074kB
14. 87,600.948 87,600.948 ↓ 1.2 40,969 3

Parallel Seq Scan on radar_orderbook_usd_prices radar_orderbook_usd_prices_1 (cost=0.00..357,023.44 rows=35,199 width=18) (actual time=7,733.090..29,200.316 rows=40,969 loops=3)

  • Filter: (((quote_asset_symbol)::text = 'WETH'::text) AND ((base_asset_symbol)::text = 'USDC'::text) AND (order_type = 'bid'::order_t) AND ((observed_timestamp / 1000) >= 1559347200))
  • Rows Removed by Filter: 2982336
15.          

CTE sorted_running_total_orderbook

16. 14.993 2,074.661 ↓ 173.6 10,588 1

WindowAgg (cost=367,479.18..367,482.53 rows=61 width=231) (actual time=2,015.990..2,074.661 rows=10,588 loops=1)

17. 17.347 2,059.668 ↓ 173.6 10,588 1

WindowAgg (cost=367,479.18..367,481.47 rows=61 width=199) (actual time=2,015.972..2,059.668 rows=10,588 loops=1)

18. 20.893 2,042.321 ↓ 173.6 10,588 1

WindowAgg (cost=367,479.18..367,480.55 rows=61 width=167) (actual time=2,015.957..2,042.321 rows=10,588 loops=1)

19. 55.780 2,021.428 ↓ 173.6 10,588 1

Sort (cost=367,479.18..367,479.33 rows=61 width=103) (actual time=2,015.914..2,021.428 rows=10,588 loops=1)

  • Sort Key: radar_orderbook_usd_prices_2.observed_timestamp, radar_orderbook_usd_prices_2.price
  • Sort Method: quicksort Memory: 3132kB
20. 1.119 1,965.648 ↓ 173.6 10,588 1

Gather (cost=1,000.00..367,477.37 rows=61 width=103) (actual time=724.898..1,965.648 rows=10,588 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
21. 1,964.529 1,964.529 ↓ 141.2 3,529 3

Parallel Seq Scan on radar_orderbook_usd_prices radar_orderbook_usd_prices_2 (cost=0.00..366,471.27 rows=25 width=103) (actual time=720.011..1,964.529 rows=3,529 loops=3)

  • Filter: (((base_asset_symbol)::text = 'USDC'::text) AND ((quote_asset_symbol)::text = 'WETH'::text) AND (order_type = 'ask'::order_t) AND (maker_address = '0x8196b13265fb41167616b3b521524fffe319487d'::bpchar) AND ((observed_timestamp / 1000) >= 1559347200))
  • Rows Removed by Filter: 3019775
22.          

CTE running_total_with_mid_market

23. 10.656 41,314.094 ↑ 362.2 10,588 1

Hash Join (cost=1.98..336,973.60 rows=3,834,938 width=564) (actual time=41,223.437..41,314.094 rows=10,588 loops=1)

  • Hash Cond: (mid_market.observed_timestamp = running_total.observed_timestamp)
24. 39,207.530 39,207.530 ↑ 1,353.3 9,291 1

CTE Scan on mid_market_price_table mid_market (cost=0.00..251,471.36 rows=12,573,568 width=40) (actual time=39,127.204..39,207.530 rows=9,291 loops=1)

25. 6.132 2,095.908 ↓ 173.6 10,588 1

Hash (cost=1.22..1.22 rows=61 width=532) (actual time=2,095.908..2,095.908 rows=10,588 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2194kB
26. 2,089.776 2,089.776 ↓ 173.6 10,588 1

CTE Scan on sorted_running_total_orderbook running_total (cost=0.00..1.22 rows=61 width=532) (actual time=2,015.994..2,089.776 rows=10,588 loops=1)

27.          

CTE base_asset_amount_used_table

28. 20.861 41,360.862 ↑ 362.2 10,588 1

WindowAgg (cost=3,405,981.53..3,530,617.01 rows=3,834,938 width=628) (actual time=41,337.402..41,360.862 rows=10,588 loops=1)

29. 11.890 41,340.001 ↑ 362.2 10,588 1

Sort (cost=3,405,981.53..3,415,568.87 rows=3,834,938 width=564) (actual time=41,337.383..41,340.001 rows=10,588 loops=1)

  • Sort Key: running_total_with_mid_market.observed_timestamp, running_total_with_mid_market.base_volume_running_total
  • Sort Method: quicksort Memory: 3145kB
30. 41,328.111 41,328.111 ↑ 362.2 10,588 1

CTE Scan on running_total_with_mid_market (cost=0.00..76,698.76 rows=3,834,938 width=564) (actual time=41,223.442..41,328.111 rows=10,588 loops=1)

31.          

CTE slippage_table

32. 5.569 41,431.198 ↑ 48.3 7,796 1

Unique (cost=1,543,175.46..1,581,524.85 rows=376,843 width=444) (actual time=41,423.737..41,431.198 rows=7,796 loops=1)

33. 7.285 41,425.629 ↑ 163.6 7,815 1

Sort (cost=1,543,175.46..1,546,371.25 rows=1,278,313 width=444) (actual time=41,423.735..41,425.629 rows=7,815 loops=1)

  • Sort Key: (to_timestamp(((base_asset_amount_used_table.observed_timestamp / 1000))::double precision)), base_asset_amount_used_table.observed_timestamp, base_asset_amount_used_table.base_asset_price, base_asset_amount_used_table.quote_asset_price, base_asset_amount_used_table.base_asset_symbol, base_asset_amount_used_table.quote_asset_symbol, base_asset_amount_used_table.mid_market_price, base_asset_amount_used_table.base_asset_amount_required, base_asset_amount_used_table.base_volume_total, base_asset_amount_used_table.maker_address, (CASE WHEN (base_asset_amount_used_table.base_volume_total >= base_asset_amount_used_table.base_asset_amount_required) THEN ((((sum(((base_asset_amount_used_table.base_asset_amount_used * base_asset_amount_used_table.quote_volume) / base_asset_amount_used_table.base_volume)) OVER (?) / sum(base_asset_amount_used_table.base_asset_amount_used) OVER (?)) - base_asset_amount_used_table.mid_market_price) / base_asset_amount_used_table.mid_market_price) * '100'::numeric) ELSE ((((base_asset_amount_used_table.highest_price * '2'::numeric) - base_asset_amount_used_table.mid_market_price) / base_asset_amount_used_table.mid_market_price) * '100'::numeric) END)
  • Sort Method: quicksort Memory: 2268kB
34. 38.115 41,418.344 ↑ 163.6 7,815 1

WindowAgg (cost=827,639.18..897,946.39 rows=1,278,313 width=444) (actual time=41,378.308..41,418.344 rows=7,815 loops=1)

35. 6.916 41,380.229 ↑ 163.6 7,815 1

Sort (cost=827,639.18..830,834.96 rows=1,278,313 width=532) (actual time=41,378.278..41,380.229 rows=7,815 loops=1)

  • Sort Key: base_asset_amount_used_table.observed_timestamp
  • Sort Method: quicksort Memory: 2268kB
36. 41,373.313 41,373.313 ↑ 163.6 7,815 1

CTE Scan on base_asset_amount_used_table (cost=0.00..86,286.10 rows=1,278,313 width=532) (actual time=41,337.411..41,373.313 rows=7,815 loops=1)

  • Filter: (base_asset_amount_used > '0'::numeric)
  • Rows Removed by Filter: 2773
37.          

CTE compute_table

38. 8.382 41,463.226 ↑ 2,549,919.9 9,291 1

Merge Right Join (cost=2,130,698.44..416,761,861.59 rows=23,691,305,429 width=72) (actual time=41,446.646..41,463.226 rows=9,291 loops=1)

  • Merge Cond: (st.date = mmpt.date)
39. 4.719 41,441.705 ↑ 48.3 7,796 1

Sort (cost=52,746.81..53,688.92 rows=376,843 width=40) (actual time=41,439.907..41,441.705 rows=7,796 loops=1)

  • Sort Key: st.date
  • Sort Method: quicksort Memory: 802kB
40. 41,436.986 41,436.986 ↑ 48.3 7,796 1

CTE Scan on slippage_table st (cost=0.00..7,536.86 rows=376,843 width=40) (actual time=41,423.742..41,436.986 rows=7,796 loops=1)

41. 4.171 13.139 ↑ 1,353.3 9,291 1

Materialize (cost=2,077,951.63..2,140,819.47 rows=12,573,568 width=8) (actual time=6.729..13.139 rows=9,291 loops=1)

42. 5.279 8.968 ↑ 1,353.3 9,291 1

Sort (cost=2,077,951.63..2,109,385.55 rows=12,573,568 width=8) (actual time=6.723..8.968 rows=9,291 loops=1)

  • Sort Key: mmpt.date
  • Sort Method: quicksort Memory: 820kB
43. 3.689 3.689 ↑ 1,353.3 9,291 1

CTE Scan on mid_market_price_table mmpt (cost=0.00..251,471.36 rows=12,573,568 width=8) (actual time=0.002..3.689 rows=9,291 loops=1)

44. 41,469.237 41,469.237 ↑ 2,549,919.9 9,291 1

CTE Scan on compute_table (cost=0.00..473,826,108.58 rows=23,691,305,429 width=32) (actual time=41,446.649..41,469.237 rows=9,291 loops=1)

Planning time : 18.577 ms
Execution time : 41,486.172 ms