#	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.	512.419	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.	169.764	11,800.126	↑ 11,038,596.1	59,838	3 / 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: 24,760kB
4.	730.067	11,630.362	↑ 11,038,596.1	59,838	3 / 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.	24.325	7,614.570	↑ 115,843.0	59,838	3 / 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.	34.852	7,291.057	↑ 31,626.1	59,838	3 / 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.	28.865	6,957.017	↑ 8,634.2	59,838	3 / 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.	69.010	6,748.639	↑ 2,357.2	59,838	3 / 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.	72.454	6,500.116	↑ 643.5	59,838	3 / 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.	69.158	6,128.474	↑ 175.7	59,838	3 / 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.	138.210	4,204.348	↑ 1.3	59,838	3 / 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.	283.520	2,034.020	↑ 1.3	59,838	3 / 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: 29,840kB
13.	73.860	1,750.500	↑ 1.3	59,838	3 / 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.	119.588	1,628.787	↑ 1.3	59,838	3 / 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.	216.569	1,316.684	↑ 1.3	59,838	3 / 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: 21,784kB
16.	149.117	1,100.115	↑ 1.3	59,838	3 / 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.	70.686	467.724	↑ 1.3	59,838	3 / 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.	93.348	348.706	↑ 1.3	59,838	3 / 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.	133.866	133.866	↑ 1.3	151,418	3 / 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: 56,203
20.	121.492	121.492	↑ 1.0	179,445	3 / 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.	48.332	48.332	↑ 1.0	201,974	3 / 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.	154.019	483.274	↑ 1.0	454,254	3 / 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: 65,536 Batches: 16 Memory Usage: 2,895kB
23.	329.255	329.255	↑ 1.0	454,254	3 / 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.	192.515	192.515	↑ 1.0	454,104	3 / 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: 166,655
25.	47.853	47.853	↑ 1.0	201,974	3 / 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.	1,722.746	2,032.118	↑ 1.0	454,081	3 / 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: 24,360kB
27.	309.372	309.372	↑ 1.0	454,254	3 / 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.	1,854.968	1,854.968	↑ 4.0	1	179,513 / 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=179,513) Index Cond: ((ulid = oe_exchange_bid.ulid) AND (event_name = 'Creation'::text))
29.	299.188	299.188	↑ 4.0	1	179,513 / 3	→ 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.	179.513	179.513	↑ 4.0	1	179,513 / 3	→ 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.	179.513	179.513	↑ 4.0	1	179,513 / 3	→ 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.	299.188	299.188	↑ 4.0	1	179,513 / 3	→ 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: 53,022
33.	299.188	299.188	↑ 4.0	1	179,513 / 3	→ 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: 53,030
34.	127.934	3,285.725	↓ 1.0	879,658	3 / 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.	2,695.973	3,157.791	↑ 1.0	835,035	3 / 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: 36,800kB
36.	461.818	461.818	↑ 1.0	835,367	3 / 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: 1,121,238

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	512.419 ms	4.1 %
Hash	1	154.019 ms	1.2 %
Hash Join	1	149.117 ms	1.2 %
Index Only Scan	3	790.892 ms	6.4 %
Index Scan	7	2,730.859 ms	22.1 %
Materialize	1	127.934 ms	1.0 %
Merge Join	4	1,081.213 ms	8.8 %
Merge Left Join	2	144.546 ms	1.2 %
Nested Loop	4	200.788 ms	1.6 %
Nested Loop Left Join	2	97.875 ms	0.8 %
Parallel Index Only Scan	1	133.866 ms	1.1 %
Seq Scan	3	1,100.445 ms	8.9 %
Sort	5	5,088.572 ms	41.2 %
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	121.492 ms	1.0 %
Index Scan	1	121.492 ms	100.0 %
event	7	3,573.377 ms	28.9 %
Index Only Scan	2	598.377 ms	16.7 %
Index Scan	4	2,513.182 ms	70.3 %
Seq Scan	1	461.818 ms	12.9 %
oe	4	965.008 ms	7.8 %
Index Only Scan	1	192.515 ms	19.9 %
Parallel Index Only Scan	1	133.866 ms	13.9 %
Seq Scan	2	638.627 ms	66.2 %
trade	2	96.185 ms	0.8 %
Index Scan	2	96.185 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