#	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

Subquery Scan on arbs_full  (cost=1506876076647.28..1674220474698.07 rows=1321055028568 width=642) (actual time=12076.953..12348.754 rows=179513 loops=1)
  ->  Gather Merge  (cost=1506876076647.28..1661009924412.39 rows=1321055028568 width=654) (actual time=12076.952..12312.545 rows=179513 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Sort  (cost=1506876075647.25..1508527394432.96 rows=660527514284 width=654) (actual time=11761.197..11800.126 rows=59838 loops=3)
              Sort Key: event__arb__creation.timestamp_epoch DESC
              Sort Method: external merge  Disk: 24760kB
              ->  Merge Join  (cost=3470897.96..33889451046.89 rows=660527514284 width=654) (actual time=6744.607..11630.362 rows=59838 loops=3)
                    Merge Cond: (arb.oe_exchange_ask_ulid = oe_exchange_ask_event.ulid)
                    ->  Nested Loop  (cost=600179.77..14693741486.66 rows=6931811575 width=516) (actual time=3839.472..7614.570 rows=59838 loops=3)
                          Join Filter: (arb.oe_osmo_ask_ulid = oe_osmo_ask_event.ulid)
                          ->  Nested Loop  (cost=600179.22..4198236797.34 rows=1892443137 width=570) (actual time=3839.466..7291.057 rows=59838 loops=3)
                                Join Filter: (arb.oe_osmo_bid_ulid = oe_osmo_bid_event.ulid)
                                ->  Nested Loop Left Join  (cost=600178.67..1332875327.62 rows=516652968 width=624) (actual time=3839.454..6957.017 rows=59838 loops=3)
                                      ->  Nested Loop Left Join  (cost=600178.11..344985683.90 rows=141050626 width=643) (actual time=3839.448..6748.639 rows=59838 loops=3)
                                            ->  Nested Loop  (cost=600177.56..75283461.92 rows=38508013 width=662) (actual time=3839.438..6500.116 rows=59838 loops=3)
                                                  ->  Nested Loop  (cost=600177.01..1111241.16 rows=10513013 width=642) (actual time=3839.427..6128.474 rows=59838 loops=3)
                                                        Join Filter: (arb.oe_exchange_bid_ulid = oe_exchange_bid_event.ulid)
                                                        ->  Merge Join  (cost=600176.46..603589.71 rows=74802 width=688) (actual time=3839.376..4204.348 rows=59838 loops=3)
                                                              Merge Cond: (arb.oe_exchange_ask_ulid = oe_exchange_ask.ulid)
                                                              ->  Sort  (cost=429551.92..429738.92 rows=74802 width=644) (actual time=2002.770..2034.020 rows=59838 loops=3)
                                                                    Sort Key: arb.oe_exchange_ask_ulid
                                                                    Sort Method: external sort  Disk: 29840kB
                                                                    ->  Merge Left Join  (cost=299050.59..402016.90 rows=74802 width=644) (actual time=1273.317..1750.500 rows=59838 loops=3)
                                                                          Merge Cond: (arb.oe_osmo_ask_ulid = trade_ask.oe_ulid)
                                                                          ->  Merge Join  (cost=299023.20..386196.13 rows=74802 width=460) (actual time=1273.172..1628.787 rows=59838 loops=3)
                                                                                Merge Cond: (arb.oe_osmo_ask_ulid = oe_osmo_ask.ulid)
                                                                                ->  Sort  (cost=298880.62..299067.62 rows=74802 width=433) (actual time=1273.075..1316.684 rows=59838 loops=3)
                                                                                      Sort Key: arb.oe_osmo_ask_ulid
                                                                                      Sort Method: external merge  Disk: 21784kB
                                                                                      ->  Hash Join  (cost=123655.60..277995.60 rows=74802 width=433) (actual time=484.849..1100.115 rows=59838 loops=3)
                                                                                            Hash Cond: (arb.oe_exchange_bid_ulid = oe_exchange_bid.ulid)
                                                                                            ->  Merge Left Join  (cost=164.94..142367.58 rows=74802 width=381) (actual time=0.247..467.724 rows=59838 loops=3)
                                                                                                  Merge Cond: (arb.oe_osmo_bid_ulid = trade_bid.oe_ulid)
                                                                                                  ->  Merge Join  (cost=137.55..126546.81 rows=74802 width=197) (actual time=0.087..348.706 rows=59838 loops=3)
                                                                                                        Merge Cond: (oe_osmo_bid.ulid = arb.oe_osmo_bid_ulid)
                                                                                                        ->  Parallel Index Only Scan using oe_pk on oe oe_osmo_bid  (cost=0.42..82367.27 rows=191494 width=27) (actual time=0.028..133.866 rows=151418 loops=3)
                                                                                                              Heap Fetches: 56203
                                                                                                        ->  Index Scan using arb_oe_osmo_bid_ulid_idx on arb  (cost=0.42..42928.99 rows=179524 width=170) (actual time=0.023..121.492 rows=179445 loops=3)
                                                                                                  ->  Index Scan using trade_oe_ulid_idx on trade trade_bid  (cost=0.42..14690.47 rows=202295 width=211) (actual time=0.017..48.332 rows=201974 loops=3)
                                                                                            ->  Hash  (cost=113256.85..113256.85 rows=459585 width=52) (actual time=483.274..483.274 rows=454254 loops=3)
                                                                                                  Buckets: 65536  Batches: 16  Memory Usage: 2895kB
                                                                                                  ->  Seq Scan on oe oe_exchange_bid  (cost=0.00..113256.85 rows=459585 width=52) (actual time=0.014..329.255 rows=454254 loops=3)
                                                                                ->  Index Only Scan using oe_pk on oe oe_osmo_ask  (cost=0.42..85048.18 rows=459585 width=27) (actual time=0.024..192.515 rows=454104 loops=3)
                                                                                      Heap Fetches: 166655
                                                                          ->  Index Scan using trade_oe_ulid_idx on trade trade_ask  (cost=0.42..14690.47 rows=202295 width=211) (actual time=0.015..47.853 rows=201974 loops=3)
                                                              ->  Sort  (cost=170620.76..171769.72 rows=459585 width=44) (actual time=1836.513..2032.118 rows=454081 loops=3)
                                                                    Sort Key: oe_exchange_ask.ulid
                                                                    Sort Method: external merge  Disk: 24360kB
                                                                    ->  Seq Scan on oe oe_exchange_ask  (cost=0.00..113256.85 rows=459585 width=44) (actual time=0.007..309.372 rows=454254 loops=3)
                                                        ->  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=179513)
                                                              Index Cond: ((ulid = oe_exchange_bid.ulid) AND (event_name = 'Creation'::text))
                                                  ->  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=179513)
                                                        Index Cond: ((ulid = arb.ulid) AND (event_name = 'Creation'::text))
                                            ->  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=179513)
                                                  Index Cond: ((trade_bid.ulid = ulid) AND (event_name = 'Creation'::text))
                                      ->  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=179513)
                                            Index Cond: ((trade_ask.ulid = ulid) AND (event_name = 'Creation'::text))
                                ->  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=179513)
                                      Index Cond: ((ulid = oe_osmo_bid.ulid) AND (event_name = 'Creation'::text))
                                      Heap Fetches: 53022
                          ->  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=179513)
                                Index Cond: ((ulid = oe_osmo_ask.ulid) AND (event_name = 'Creation'::text))
                                Heap Fetches: 53030
                    ->  Materialize  (cost=189595.20..193808.00 rows=842560 width=35) (actual time=2904.854..3285.725 rows=879658 loops=3)
                          ->  Sort  (cost=189595.20..191701.60 rows=842560 width=35) (actual time=2904.852..3157.791 rows=835035 loops=3)
                                Sort Key: oe_exchange_ask_event.ulid
                                Sort Method: external merge  Disk: 36800kB
                                ->  Seq Scan on event oe_exchange_ask_event  (cost=0.00..83628.18 rows=842560 width=35) (actual time=0.006..461.818 rows=835367 loops=3)
                                      Filter: (event_name = 'Creation'::text)
                                      Rows Removed by Filter: 1121238
Planning time: 11.758 ms
Execution time: 12399.763 ms

Per node type stats

node type	count	sum of times	% of query
Gather Merge	1	0.000 ms	0.0 %
Hash	1	462.057 ms	3.7 %
Hash Join	1	447.351 ms	3.6 %
Index Only Scan	3	2,372.675 ms	19.2 %
Index Scan	7	8,192.577 ms	66.3 %
Materialize	1	383.802 ms	3.1 %
Merge Join	4	3,243.639 ms	26.3 %
Merge Left Join	2	433.638 ms	3.5 %
Nested Loop	4	602.365 ms	4.9 %
Nested Loop Left Join	2	293.625 ms	2.4 %
Parallel Index Only Scan	1	401.598 ms	3.3 %
Seq Scan	3	3,301.335 ms	26.7 %
Sort	5	15,265.716 ms	123.6 %
Subquery Scan	1	36.209 ms	0.3 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
arb	1	364.476 ms	3.0 %
Index Scan	1	364.476 ms	100.0 %
event	7	10,720.130 ms	86.8 %
Index Only Scan	2	1,795.130 ms	16.7 %
Index Scan	4	7,539.546 ms	70.3 %
Seq Scan	1	1,385.454 ms	12.9 %
oe	4	2,895.024 ms	23.4 %
Index Only Scan	1	577.545 ms	19.9 %
Parallel Index Only Scan	1	401.598 ms	13.9 %
Seq Scan	2	1,915.881 ms	66.2 %
trade	2	288.555 ms	2.3 %
Index Scan	2	288.555 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yfND

Color mode:

Visible columns:

Per node type stats

Per table stats