2 ms·

Could you share more details about the limited JOIN capabilities? AFAIK, Clickhouse has multiple join algorithms and supports on-disk joins to avoid out of memo

• FridgeSeal 6y ago
  Maybe I’m not doing anything particularly challenging with it, but I’ve not found anything lacking with the join functionality.
  • neilknowsbest 6y ago
    One issue I've come across is that the query optimizer in Clickhouse does not propagate `where` clauses through a join. My terminology might be wrong, so consider this example: select * from a inner join b using (id) where b.foo = 'bar' Clickhouse will not evaluate `foo = 'bar'` before performing the join, so you might wind up with a join that produces a large intermediate result before the filtering happens. Postgres (probably other databases) will optimize this for you. To force Clickhouse to filter first, you would need to write something like select * from a inner join ( select * from b where foo = 'bar' ) b using (id) Maybe not a strict limitation, but the workaround is a bit janky.
    • FridgeSeal 6y ago
      Interesting, I swear I’ve done that before and has it respect the clause. You can also use PREWHERE I believe, which has the benefit of increasing performance.