News for hackers. Stuff that matters.
Re:A gentle-ish introduction to worst-case optimal joins by juancn on Thursday June 02, 2022 @10:39AM (#31595453)
In pure analytics, most queries need to read most rows, but not all columns. Typical relational queries just touch a few rows of the dataset (think CRUD).

That's why if you force analytics into a relational DB you end up with a star schema, but the fact tables are not physically aligned, so you need to join them, this causes a query scan to jump around the data a lot.

Analytic engines, keep the data aligned across columns, but still on a per-column basis, so the full scans likely only need to look at nearby data, helping prefetchers (both CPU and from disk).

This avoids joins, but makes inserts a bit more annoying, multi valued data, is usually kept also close and compact so the scan doesn't jump around, but the explanation is a bit longer for a comment.

Re:A gentle-ish introduction to worst-case optimal joins by zasdffaa on Thursday June 02, 2022 @12:25PM (#31596905)
Col-based storage is for usual tables, DW don't need that because all the stuff is pre-joined except for the dimension tables.

The dimension tables are tiny IIRC, and there should (?) be only one fact table. You don't join between fact tables anyway (do you?)

I feel you may be mixing up a few things but it's not my strong point here.

Re:A gentle-ish introduction to worst-case optimal joins by juancn on Wednesday June 08, 2022 @10:47AM (#31668159)
Yeah, I may have messed the terminology here, depending on the dataset, the dimension tables can be close to the fact table in cardinality, for true flexibility, it degenerates into a fact table with just a record id and dimension tables with a FK to the fact table and a single column.

If the DB is a true columnar store, there's no need to do that, but most are record based.