Significant performance degradation of ASOF join between 22.12 and 23.01

[chfanboy]

There is a huge performance degradation of ASOF join between versions 22.12 and 23.01

Asof join in 23.01 is many times slower compared to 22.12. In 22.12 following query runs with steady performance of ~30M rows/sec. In 23.01 it runs times slower, less than 5M/sec, actually the longer query runs the slower it becomes. It starts with same numbers, but over the time gets slower and slower.

query:

SELECT
    t1.trade_price,
    t1.exchange_ts,
    t1.security,
    t2.bid_price,
    t2.ask_price,
    t2.bid_size,
    t2.ask_size
FROM
(
    SELECT *
    FROM data
    WHERE message_type = 'trade'
) AS t1
ASOF LEFT JOIN
(
    SELECT *
    FROM data
    WHERE message_type = 'quote'
) AS t2 ON (t1.security = t2.security) AND (t1.exchange_ts >= t2.exchange_ts)
SETTINGS max_threads = 32

This is unrelated to #46453. I did revert #35525 manually on top of master branch and build from source, still the same performance problem.

I’m not sure which commit bring this problem, but a build from this PR 1c2dc05 works just fine, stable good performance. So it’s something between this PR and 23.01 release. Version 23.02 also has this problem.

Data schema:

┌─name────────────┬─type─────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ dtype           │ LowCardinality(Nullable(String)) │              │                    │         │                  │                │
│ ip_address      │ Nullable(String)                 │              │                    │         │                  │                │
│ exchange_ts     │ Nullable(Int64)                  │              │                    │         │                  │                │
│ seqnum          │ Nullable(UInt32)                 │              │                    │         │                  │                │
│ market          │ Nullable(String)                 │              │                    │         │                  │                │
│ product         │ LowCardinality(Nullable(String)) │              │                    │         │                  │                │
│ message_type    │ LowCardinality(Nullable(String)) │              │                    │         │                  │                │
│ trade_price     │ Nullable(Float64)                │              │                    │         │                  │                │
│ trade_size      │ Nullable(UInt32)                 │              │                    │         │                  │                │
│ trade_condition │ Nullable(String)                 │              │                    │         │                  │                │
│ bid_price       │ Nullable(Float64)                │              │                    │         │                  │                │
│ bid_size        │ Nullable(UInt32)                 │              │                    │         │                  │                │
│ bid_condition   │ Nullable(String)                 │              │                    │         │                  │                │
│ ask_price       │ Nullable(Float64)                │              │                    │         │                  │                │
│ ask_size        │ Nullable(UInt32)                 │              │                    │         │                  │                │
│ ask_condition   │ Nullable(String)                 │              │                    │         │                  │                │
│ security           │ LowCardinality(String)           │              │                    │         │                  │                │
└─────────────────┴──────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Could I somehow assist in fixing this?

All settings are default.

[den-crane]

Again: Your query is quite complex. I suggest to minimize your query.
Also it would be nice to have reproducible example.
Provide select value,name from system.settings where changed

[den-crane]

I persisted parquet files to normal MergeTree and rerun same query. 23.01 is 4 times slower compared to 22.12.
22.12: Elapsed: 35.795 sec. Processed 1.55 billion rows, 51.56 GB (43.30 million rows/s., 1.44 GB/s.)
23.01: Elapsed: 122.804 sec. Processed 1.33 billion rows, 45.74 GB (10.86 million rows/s., 372.50 MB/s.)
Maybe that's a problem with ASOF JOIN?

As I understood the issue is not related to parquet and s3 ? Right?
Why did you share a query with parquet and s3?

[chfanboy]

@den-crane , thank you for helping to simplify the description. you're right, issue is unrelated to s3/parquet (it's just my use case). I downloaded more data and now difference in not 4x, but 10x
sorry for confusion, I updated title and description
all settings are default.

[den-crane]

Though,

drop table data;
create table data (
   e     Int64,
   s     LowCardinality(String),   
   c0    Nullable(Float64)   ,
   c1    Nullable(Float64)   ,
   c2    Nullable(UInt32)    ,
   c3    Nullable(Float64)   ,
   c4    Nullable(UInt32)    ,
   c5    Nullable(Float64)   ,
   c6    Nullable(UInt32)    ,
   c7    Nullable(Float64)   ,
   c8    Nullable(UInt32)                           
) Engine=MergeTree order by (s);

insert into data (s, e)
select toString(intDiv(number, 100)), 
       toDateTime('2020-01-01 00:00:00')+intDiv(number,10)
from numbers(1e8);


SELECT
    t1.c0,
    t1.e,
    t1.s,
    t2.c1, t2.c3, t2.c2, t2.c4, t2.c5, t2.c6, t2.c7, t2.c8     
FROM data AS t1 ASOF LEFT JOIN data AS t2 
ON (t1.s = t2.s) AND (t1.e >= t2.e)
SETTINGS max_threads = 32
format Null;


22.3.19.6
set max_memory_usage='40G';
0 rows in set. Elapsed: 6.414 sec. Processed 200.00 million rows, 10.24 GB (31.18 million rows/s., 1.60 GB/s.)

22.12.4.76
0 rows in set. Elapsed: 7.806 sec. Processed 200.00 million rows, 10.24 GB (25.62 million rows/s., 1.31 GB/s.)

23.1.4.58
0 rows in set. Elapsed: 26.286 sec. Processed 200.00 million rows, 10.22 GB (7.61 million rows/s., 388.75 MB/s.)

23.2.2.20
0 rows in set. Elapsed: 25.384 sec. Processed 200.00 million rows, 10.25 GB (7.88 million rows/s., 403.70 MB/s.)

[den-crane]

The same issue with ANY LEFT

SELECT
    t1.c0,
    t1.e,
    t1.s,
    t2.c1, t2.c3, t2.c2, t2.c4, t2.c5, t2.c6, t2.c7, t2.c8     
FROM data AS t1 ANY LEFT JOIN data AS t2 
ON (t1.s = t2.s) AND (t1.e = t2.e)
SETTINGS max_threads = 32
format Null;

22.3.19.6
set max_memory_usage='40G';
0 rows in set. Elapsed: 9.808 sec. Processed 200.00 million rows, 10.24 GB (20.39 million rows/s., 1.04 GB/s.)

22.12.4.76
0 rows in set. Elapsed: 9.075 sec. Processed 200.00 million rows, 10.24 GB (22.04 million rows/s., 1.13 GB/s.)


23.1.4.58
0 rows in set. Elapsed: 21.242 sec. Processed 200.00 million rows, 10.24 GB (9.42 million rows/s., 482.05 MB/s.)

set join_algorithm = 'hash';
0 rows in set. Elapsed: 21.442 sec. Processed 200.00 million rows, 10.24 GB (9.33 million rows/s., 477.56 MB/s.)

23.2.2.20
0 rows in set. Elapsed: 20.339 sec. Processed 200.00 million rows, 10.25 GB (9.83 million rows/s., 503.84 MB/s.)