3 ms·

I strongly dislike the way the polroblem is presented and the “solution” is promoted. Author mentions merge join with count of top, and if th database supports

by galkk 1y ago

I strongly dislike the way the polroblem is presented and the “solution” is promoted. Author mentions merge join with count of top, and if th database supports index merges, it can be extremely efficient in described scenario. There are a lot of real optimizations that can be baked in such merges that author chooses to ignore. The generalized guidance without even mentioning database server as a baseline, without showing plans and/or checking if the database can be guided is just a bad teaching. It looks like author discovered a trick and tries to hammer everything into it by overgeneralizing.

ethanseal 1y ago
For sure, there's definitely a lot of cool techniques (and I'm not aware of all of them)! And the first example is very much contrived to show a small example. I'm not super familiar with the term index merge - this seems to be the term for a BitmapOr/BitmapAnd? Is there another optimization I'm missing? The article links to my code for my timings here: https://github.com/ethan-seal/ors_expensive https://github.com/ethan-seal/ors_expensive There is an optimization that went in the new release of PostgreSQL I'm excited about that may affect this - I'm not sure. See https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=ae4569161 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...
- Sesse__ 1y ago
  > I'm not super familiar with the term index merge - this seems to be the term for a BitmapOr/BitmapAnd? Different databases will use similar terms for different operations, but I would guess that the comment refers to something similar to MySQL's index merge (which is essentially reading the row IDs of all the relevant ranges, then deduplicating them, then doing the final scan; it's similar to but less flexible than Postgres' BitmapOr).
  - ethanseal 1y ago
    Cool. I'll have to read up on that.

by galkk 1y ago

ethanseal 1y ago

For sure, there's definitely a lot of cool techniques (and I'm not aware of all of them)! And the first example is very much contrived to show a small example. I'm not super familiar with the term index merge - this seems to be the term for a BitmapOr/BitmapAnd? Is there another optimization I'm missing? The article links to my code for my timings here: https://github.com/ethan-seal/ors_expensive https://github.com/ethan-seal/ors_expensive There is an optimization that went in the new release of PostgreSQL I'm excited about that may affect this - I'm not sure. See https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=ae4569161 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

Sesse__ 1y ago
> I'm not super familiar with the term index merge - this seems to be the term for a BitmapOr/BitmapAnd? Different databases will use similar terms for different operations, but I would guess that the comment refers to something similar to MySQL's index merge (which is essentially reading the row IDs of all the relevant ranges, then deduplicating them, then doing the final scan; it's similar to but less flexible than Postgres' BitmapOr).
- ethanseal 1y ago
  Cool. I'll have to read up on that.