explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l3VM

Settings
# exclusive inclusive rows x rows loops node
1. 2.621 48,379.422 ↑ 1.0 1 1

Aggregate (cost=1,015,415,592.59..1,015,415,592.61 rows=1 width=32) (actual time=48,379.421..48,379.422 rows=1 loops=1)

2.          

CTE mid_market_price_table

3. 45.122 48,042.542 ↑ 1,353.3 9,291 1

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

  • Hash Cond: (radar_orderbook_usd_prices.observed_timestamp = radar_orderbook_usd_prices_1.observed_timestamp)
4. 9.561 14,004.955 ↑ 5.5 9,291 1

Finalize GroupAggregate (cost=360,999.03..371,593.91 rows=51,433 width=40) (actual time=13,945.297..14,004.955 rows=9,291 loops=1)

  • Group Key: radar_orderbook_usd_prices.observed_timestamp
5. 0.000 13,995.394 ↑ 6.2 12,626 1

Gather Merge (cost=360,999.03..370,689.39 rows=78,038 width=40) (actual time=13,945.266..13,995.394 rows=12,626 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 61.845 41,838.051 ↑ 9.3 4,209 3

Partial GroupAggregate (cost=359,999.01..360,681.84 rows=39,019 width=40) (actual time=13,908.485..13,946.017 rows=4,209 loops=3)

  • Group Key: radar_orderbook_usd_prices.observed_timestamp
7. 1,382.289 41,776.206 ↓ 1.1 43,377 3

Sort (cost=359,999.01..360,096.56 rows=39,019 width=18) (actual time=13,908.462..13,925.402 rows=43,377 loops=3)

  • Sort Key: radar_orderbook_usd_prices.observed_timestamp
  • Sort Method: quicksort Memory: 3810kB
8. 40,393.917 40,393.917 ↓ 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=1,343.867..13,464.639 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.495 33,992.465 ↑ 5.3 9,291 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 1010kB
10. 10.796 33,986.970 ↑ 5.3 9,291 1

Finalize GroupAggregate (cost=360,681.56..370,264.14 rows=48,893 width=40) (actual time=33,919.923..33,986.970 rows=9,291 loops=1)

  • Group Key: radar_orderbook_usd_prices_1.observed_timestamp
11. 0.000 33,976.174 ↑ 5.6 12,613 1

Gather Merge (cost=360,681.56..369,423.22 rows=70,398 width=40) (actual time=33,919.890..33,976.174 rows=12,613 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 82.332 101,776.167 ↑ 8.4 4,204 3

Partial GroupAggregate (cost=359,681.54..360,297.52 rows=35,199 width=40) (actual time=33,875.037..33,925.389 rows=4,204 loops=3)

  • Group Key: radar_orderbook_usd_prices_1.observed_timestamp
13. 974.775 101,693.835 ↓ 1.2 40,969 3

Sort (cost=359,681.54..359,769.54 rows=35,199 width=18) (actual time=33,875.015..33,897.945 rows=40,969 loops=3)

  • Sort Key: radar_orderbook_usd_prices_1.observed_timestamp
  • Sort Method: quicksort Memory: 4034kB
14. 100,719.060 100,719.060 ↓ 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,736.339..33,573.020 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. 16.424 99.342 ↓ 173.6 10,588 1

WindowAgg (cost=21,196.51..21,199.87 rows=61 width=231) (actual time=35.770..99.342 rows=10,588 loops=1)

17. 23.409 82.918 ↓ 173.6 10,588 1

WindowAgg (cost=21,196.51..21,198.80 rows=61 width=199) (actual time=35.751..82.918 rows=10,588 loops=1)

18. 20.853 59.509 ↓ 173.6 10,588 1

WindowAgg (cost=21,196.51..21,197.88 rows=61 width=167) (actual time=35.728..59.509 rows=10,588 loops=1)

19. 10.230 38.656 ↓ 173.6 10,588 1

Sort (cost=21,196.51..21,196.66 rows=61 width=103) (actual time=35.701..38.656 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. 23.989 28.426 ↓ 173.6 10,588 1

Bitmap Heap Scan on radar_orderbook_usd_prices radar_orderbook_usd_prices_2 (cost=240.88..21,194.70 rows=61 width=103) (actual time=7.777..28.426 rows=10,588 loops=1)

  • Recheck Cond: (maker_address = '0x8196b13265fb41167616b3b521524fffe319487d'::bpchar)
  • Filter: (((base_asset_symbol)::text = 'USDC'::text) AND ((quote_asset_symbol)::text = 'WETH'::text) AND (order_type = 'ask'::order_t) AND ((observed_timestamp / 1000) >= 1559347200))
  • Rows Removed by Filter: 13270
  • Heap Blocks: exact=9444
21. 4.437 4.437 ↓ 4.0 23,858 1

Bitmap Index Scan on radar_orderbook_usd_prices_maker_index (cost=0.00..240.86 rows=5,907 width=0) (actual time=4.437..4.437 rows=23,858 loops=1)

  • Index Cond: (maker_address = '0x8196b13265fb41167616b3b521524fffe319487d'::bpchar)
22.          

CTE running_total_with_mid_market

23. 11.245 48,214.824 ↑ 362.2 10,588 1

Hash Join (cost=1.98..336,973.60 rows=3,834,938 width=564) (actual time=48,124.927..48,214.824 rows=10,588 loops=1)

  • Hash Cond: (mid_market.observed_timestamp = running_total.observed_timestamp)
24. 48,049.787 48,049.787 ↑ 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=47,970.152..48,049.787 rows=9,291 loops=1)

25. 18.941 153.792 ↓ 173.6 10,588 1

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

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2194kB
26. 134.851 134.851 ↓ 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=35.775..134.851 rows=10,588 loops=1)

27.          

CTE base_asset_amount_used_table

28. 20.715 48,263.753 ↑ 362.2 10,588 1

WindowAgg (cost=3,405,981.53..3,530,617.01 rows=3,834,938 width=628) (actual time=48,240.427..48,263.753 rows=10,588 loops=1)

29. 13.985 48,243.038 ↑ 362.2 10,588 1

Sort (cost=3,405,981.53..3,415,568.87 rows=3,834,938 width=564) (actual time=48,240.410..48,243.038 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. 48,229.053 48,229.053 ↑ 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=48,124.932..48,229.053 rows=10,588 loops=1)

31.          

CTE slippage_table

32. 5.601 48,339.039 ↑ 48.3 7,796 1

Unique (cost=1,543,175.46..1,581,524.85 rows=376,843 width=444) (actual time=48,331.534..48,339.039 rows=7,796 loops=1)

33. 8.759 48,333.438 ↑ 163.6 7,815 1

Sort (cost=1,543,175.46..1,546,371.25 rows=1,278,313 width=444) (actual time=48,331.532..48,333.438 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. 37.929 48,324.679 ↑ 163.6 7,815 1

WindowAgg (cost=827,639.18..897,946.39 rows=1,278,313 width=444) (actual time=48,284.819..48,324.679 rows=7,815 loops=1)

35. 8.750 48,286.750 ↑ 163.6 7,815 1

Sort (cost=827,639.18..830,834.96 rows=1,278,313 width=532) (actual time=48,284.791..48,286.750 rows=7,815 loops=1)

  • Sort Key: base_asset_amount_used_table.observed_timestamp
  • Sort Method: quicksort Memory: 2268kB
36. 48,278.000 48,278.000 ↑ 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=48,240.434..48,278.000 rows=7,815 loops=1)

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

CTE compute_table

38. 8.272 48,370.881 ↑ 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=48,354.593..48,370.881 rows=9,291 loops=1)

  • Merge Cond: (st.date = mmpt.date)
39. 5.058 48,349.872 ↑ 48.3 7,796 1

Sort (cost=52,746.81..53,688.92 rows=376,843 width=40) (actual time=48,348.160..48,349.872 rows=7,796 loops=1)

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

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

41. 4.133 12.737 ↑ 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.422..12.737 rows=9,291 loops=1)

42. 5.206 8.604 ↑ 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.417..8.604 rows=9,291 loops=1)

  • Sort Key: mmpt.date
  • Sort Method: quicksort Memory: 820kB
43. 3.398 3.398 ↑ 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.398 rows=9,291 loops=1)

44. 48,376.801 48,376.801 ↑ 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=48,354.596..48,376.801 rows=9,291 loops=1)

Planning time : 2.940 ms
Execution time : 48,393.345 ms