explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yfND

Settings
# exclusive inclusive rows x rows loops node
1. 36.209 12,348.754 ↑ 7,359,105.1 179,513 1

Subquery Scan on arbs_full (cost=1,506,876,076,647.28..1,674,220,474,698.07 rows=1,321,055,028,568 width=642) (actual time=12,076.953..12,348.754 rows=179,513 loops=1)

2. 0.000 12,312.545 ↑ 7,359,105.1 179,513 1

Gather Merge (cost=1,506,876,076,647.28..1,661,009,924,412.39 rows=1,321,055,028,568 width=654) (actual time=12,076.952..12,312.545 rows=179,513 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 509.292 35,400.378 ↑ 11,038,596.1 59,838 3

Sort (cost=1,506,876,075,647.25..1,508,527,394,432.96 rows=660,527,514,284 width=654) (actual time=11,761.197..11,800.126 rows=59,838 loops=3)

  • Sort Key: event__arb__creation.timestamp_epoch DESC
  • Sort Method: external merge Disk: 24760kB
4. 2,190.201 34,891.086 ↑ 11,038,596.1 59,838 3

Merge Join (cost=3,470,897.96..33,889,451,046.89 rows=660,527,514,284 width=654) (actual time=6,744.607..11,630.362 rows=59,838 loops=3)

  • Merge Cond: (arb.oe_exchange_ask_ulid = oe_exchange_ask_event.ulid)
5. 72.974 22,843.710 ↑ 115,843.0 59,838 3

Nested Loop (cost=600,179.77..14,693,741,486.66 rows=6,931,811,575 width=516) (actual time=3,839.472..7,614.570 rows=59,838 loops=3)

  • Join Filter: (arb.oe_osmo_ask_ulid = oe_osmo_ask_event.ulid)
6. 104.555 21,873.171 ↑ 31,626.1 59,838 3

Nested Loop (cost=600,179.22..4,198,236,797.34 rows=1,892,443,137 width=570) (actual time=3,839.466..7,291.057 rows=59,838 loops=3)

  • Join Filter: (arb.oe_osmo_bid_ulid = oe_osmo_bid_event.ulid)
7. 86.595 20,871.051 ↑ 8,634.2 59,838 3

Nested Loop Left Join (cost=600,178.67..1,332,875,327.62 rows=516,652,968 width=624) (actual time=3,839.454..6,957.017 rows=59,838 loops=3)

8. 207.030 20,245.917 ↑ 2,357.2 59,838 3

Nested Loop Left Join (cost=600,178.11..344,985,683.90 rows=141,050,626 width=643) (actual time=3,839.448..6,748.639 rows=59,838 loops=3)

9. 217.361 19,500.348 ↑ 643.5 59,838 3

Nested Loop (cost=600,177.56..75,283,461.92 rows=38,508,013 width=662) (actual time=3,839.438..6,500.116 rows=59,838 loops=3)

10. 207.475 18,385.422 ↑ 175.7 59,838 3

Nested Loop (cost=600,177.01..1,111,241.16 rows=10,513,013 width=642) (actual time=3,839.427..6,128.474 rows=59,838 loops=3)

  • Join Filter: (arb.oe_exchange_bid_ulid = oe_exchange_bid_event.ulid)
11. 414.630 12,613.044 ↑ 1.3 59,838 3

Merge Join (cost=600,176.46..603,589.71 rows=74,802 width=688) (actual time=3,839.376..4,204.348 rows=59,838 loops=3)

  • Merge Cond: (arb.oe_exchange_ask_ulid = oe_exchange_ask.ulid)
12. 850.560 6,102.060 ↑ 1.3 59,838 3

Sort (cost=429,551.92..429,738.92 rows=74,802 width=644) (actual time=2,002.770..2,034.020 rows=59,838 loops=3)

  • Sort Key: arb.oe_exchange_ask_ulid
  • Sort Method: external sort Disk: 29840kB
13. 221.580 5,251.500 ↑ 1.3 59,838 3

Merge Left Join (cost=299,050.59..402,016.90 rows=74,802 width=644) (actual time=1,273.317..1,750.500 rows=59,838 loops=3)

  • Merge Cond: (arb.oe_osmo_ask_ulid = trade_ask.oe_ulid)
14. 358.764 4,886.361 ↑ 1.3 59,838 3

Merge Join (cost=299,023.20..386,196.13 rows=74,802 width=460) (actual time=1,273.172..1,628.787 rows=59,838 loops=3)

  • Merge Cond: (arb.oe_osmo_ask_ulid = oe_osmo_ask.ulid)
15. 649.707 3,950.052 ↑ 1.3 59,838 3

Sort (cost=298,880.62..299,067.62 rows=74,802 width=433) (actual time=1,273.075..1,316.684 rows=59,838 loops=3)

  • Sort Key: arb.oe_osmo_ask_ulid
  • Sort Method: external merge Disk: 21784kB
16. 447.351 3,300.345 ↑ 1.3 59,838 3

Hash Join (cost=123,655.60..277,995.60 rows=74,802 width=433) (actual time=484.849..1,100.115 rows=59,838 loops=3)

  • Hash Cond: (arb.oe_exchange_bid_ulid = oe_exchange_bid.ulid)
17. 212.058 1,403.172 ↑ 1.3 59,838 3

Merge Left Join (cost=164.94..142,367.58 rows=74,802 width=381) (actual time=0.247..467.724 rows=59,838 loops=3)

  • Merge Cond: (arb.oe_osmo_bid_ulid = trade_bid.oe_ulid)
18. 280.044 1,046.118 ↑ 1.3 59,838 3

Merge Join (cost=137.55..126,546.81 rows=74,802 width=197) (actual time=0.087..348.706 rows=59,838 loops=3)

  • Merge Cond: (oe_osmo_bid.ulid = arb.oe_osmo_bid_ulid)
19. 401.598 401.598 ↑ 1.3 151,418 3

Parallel Index Only Scan using oe_pk on oe oe_osmo_bid (cost=0.42..82,367.27 rows=191,494 width=27) (actual time=0.028..133.866 rows=151,418 loops=3)

  • Heap Fetches: 56203
20. 364.476 364.476 ↑ 1.0 179,445 3

Index Scan using arb_oe_osmo_bid_ulid_idx on arb (cost=0.42..42,928.99 rows=179,524 width=170) (actual time=0.023..121.492 rows=179,445 loops=3)

21. 144.996 144.996 ↑ 1.0 201,974 3

Index Scan using trade_oe_ulid_idx on trade trade_bid (cost=0.42..14,690.47 rows=202,295 width=211) (actual time=0.017..48.332 rows=201,974 loops=3)

22. 462.057 1,449.822 ↑ 1.0 454,254 3

Hash (cost=113,256.85..113,256.85 rows=459,585 width=52) (actual time=483.274..483.274 rows=454,254 loops=3)

  • Buckets: 65536 Batches: 16 Memory Usage: 2895kB
23. 987.765 987.765 ↑ 1.0 454,254 3

Seq Scan on oe oe_exchange_bid (cost=0.00..113,256.85 rows=459,585 width=52) (actual time=0.014..329.255 rows=454,254 loops=3)

24. 577.545 577.545 ↑ 1.0 454,104 3

Index Only Scan using oe_pk on oe oe_osmo_ask (cost=0.42..85,048.18 rows=459,585 width=27) (actual time=0.024..192.515 rows=454,104 loops=3)

  • Heap Fetches: 166655
25. 143.559 143.559 ↑ 1.0 201,974 3

Index Scan using trade_oe_ulid_idx on trade trade_ask (cost=0.42..14,690.47 rows=202,295 width=211) (actual time=0.015..47.853 rows=201,974 loops=3)

26. 5,168.238 6,096.354 ↑ 1.0 454,081 3

Sort (cost=170,620.76..171,769.72 rows=459,585 width=44) (actual time=1,836.513..2,032.118 rows=454,081 loops=3)

  • Sort Key: oe_exchange_ask.ulid
  • Sort Method: external merge Disk: 24360kB
27. 928.116 928.116 ↑ 1.0 454,254 3

Seq Scan on oe oe_exchange_ask (cost=0.00..113,256.85 rows=459,585 width=44) (actual time=0.007..309.372 rows=454,254 loops=3)

28. 5,564.903 5,564.903 ↑ 4.0 1 179,513

Index Scan using event_ulid_idx on event oe_exchange_bid_event (cost=0.55..6.74 rows=4 width=35) (actual time=0.006..0.031 rows=1 loops=179,513)

  • Index Cond: ((ulid = oe_exchange_bid.ulid) AND (event_name = 'Creation'::text))
29. 897.565 897.565 ↑ 4.0 1 179,513

Index Scan using event_ulid_idx on event event__arb__creation (cost=0.55..7.02 rows=4 width=47) (actual time=0.005..0.005 rows=1 loops=179,513)

  • Index Cond: ((ulid = arb.ulid) AND (event_name = 'Creation'::text))
30. 538.539 538.539 ↑ 4.0 1 179,513

Index Scan using event_ulid_idx on event trade_bid_event (cost=0.55..6.96 rows=4 width=35) (actual time=0.003..0.003 rows=1 loops=179,513)

  • Index Cond: ((trade_bid.ulid = ulid) AND (event_name = 'Creation'::text))
31. 538.539 538.539 ↑ 4.0 1 179,513

Index Scan using event_ulid_idx on event trade_ask_event (cost=0.55..6.96 rows=4 width=35) (actual time=0.003..0.003 rows=1 loops=179,513)

  • Index Cond: ((trade_ask.ulid = ulid) AND (event_name = 'Creation'::text))
32. 897.565 897.565 ↑ 4.0 1 179,513

Index Only Scan using event_ulid_idx on event oe_osmo_bid_event (cost=0.55..5.50 rows=4 width=27) (actual time=0.005..0.005 rows=1 loops=179,513)

  • Index Cond: ((ulid = oe_osmo_bid.ulid) AND (event_name = 'Creation'::text))
  • Heap Fetches: 53022
33. 897.565 897.565 ↑ 4.0 1 179,513

Index Only Scan using event_ulid_idx on event oe_osmo_ask_event (cost=0.55..5.50 rows=4 width=27) (actual time=0.004..0.005 rows=1 loops=179,513)

  • Index Cond: ((ulid = oe_osmo_ask.ulid) AND (event_name = 'Creation'::text))
  • Heap Fetches: 53030
34. 383.802 9,857.175 ↓ 1.0 879,658 3

Materialize (cost=189,595.20..193,808.00 rows=842,560 width=35) (actual time=2,904.854..3,285.725 rows=879,658 loops=3)

35. 8,087.919 9,473.373 ↑ 1.0 835,035 3

Sort (cost=189,595.20..191,701.60 rows=842,560 width=35) (actual time=2,904.852..3,157.791 rows=835,035 loops=3)

  • Sort Key: oe_exchange_ask_event.ulid
  • Sort Method: external merge Disk: 36800kB
36. 1,385.454 1,385.454 ↑ 1.0 835,367 3

Seq Scan on event oe_exchange_ask_event (cost=0.00..83,628.18 rows=842,560 width=35) (actual time=0.006..461.818 rows=835,367 loops=3)

  • Filter: (event_name = 'Creation'::text)
  • Rows Removed by Filter: 1121238
Planning time : 11.758 ms
Execution time : 12,399.763 ms