3 ms·
ClickHouse is the opposite: it has no optimizer, so your SQL must be structured the way you want it to run: deeply nested subqueries with one JOIN per SELECT. B
by orthoxerox 6y ago
ClickHouse is the opposite: it has no optimizer, so your SQL must be structured the way you want it to run: deeply nested subqueries with one JOIN per SELECT. But at least you can be sure your query runs the way you intended.
- rwmj 6y agoAn interesting approach and I'm not sure if I'd prefer it (I happen to like my queries being optimized automatically for my very tiny databases). But wouldn't it be possible to modify PostgreSQL to work this way too? It's unclear why you'd want to switch to a whole new DBMS for this.
- jeff-davis 6y agoPostgreSQL offers a config where you can control join order to match the query text: https://www.postgresql.org/docs/13/runtime-config-query.html#GUC-JOIN-COLLAPSE-LIMIT https://www.postgresql.org/docs/13/runtime-config-query.html...
- barrkel 6y agoWell, you're better off not doing joins at all in ClickHouse, beyond small dimension tables. Don't do joins between two or more big tables at all, is generally the rule in analytics databases; instead, pre-join your data at insert time. CH supports optimizations for low-cardinality columns, so you can efficiently store things like enums directly as strings, rather than needing a separate table for them.