filter ourside the join vs inside the join in clickhouse

res.sql

MacBook-Pro-de-javi.local :) select cityHash64(groupArray(cityHash64(*))) from A a asof inner join (select * from B  where ts<toDateTime('1970-01-01 02:00:00')) b on a.id=b.id and a.ts=b.ts where a.ts<toDateTime('1970-01-01 02:00:00');

SELECT cityHash64(groupArray(cityHash64(*)))
FROM A AS a
ASOF INNER JOIN
(
    SELECT *
    FROM B
    WHERE ts < toDateTime('1970-01-01 02:00:00')
) AS b ON (a.id = b.id) AND (a.ts = b.ts)
WHERE a.ts < toDateTime('1970-01-01 02:00:00')

┌─cityHash64(groupArray(cityHash64(ts, id, b.ts, b.id)))─┐
│                                   11024216907930891576 │
└────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.009 sec. Processed 16.38 thousand rows, 292.93 KB (1.88 million rows/s., 33.62 MB/s.)

MacBook-Pro-de-javi.local :) select cityHash64(groupArray(cityHash64(*))) from A a asof inner join B b on a.id=b.id and a.ts=b.ts where a.ts<toDateTime('1970-01-01 02:00:00');

SELECT cityHash64(groupArray(cityHash64(*)))
FROM A AS a
ASOF INNER JOIN B AS b ON (a.id = b.id) AND (a.ts = b.ts)
WHERE a.ts < toDateTime('1970-01-01 02:00:00')

┌─cityHash64(groupArray(cityHash64(ts, id, b.ts, b.id)))─┐
│                                   11024216907930891576 │
└────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.793 sec. Processed 10.01 million rows, 179.05 MB (12.62 million rows/s., 225.78 MB/s.)