2 ms·
For one problem I had recently, I found MySQL a better fit. It supports unsigned ints which Postgres doesn't and it has bitcount built in. I am aware these are
by collyw 12y ago
For one problem I had recently, I found MySQL a better fit.
It supports unsigned ints which Postgres doesn't and it has bitcount built in. I am aware these are probably rarely used, but I wanted them for what I was trying to do (compare large combinations of DNA sequence). I tried implementing it in Python / Numpy and Java and PG but MySQL was still the fastest. (I am fairly sure if I spent more time, I could improve any of the solutions, but for a similar level of effort MySQL (plus Python) won.
- masklinn 12y agoPostgres does have a bit/varbit which may have been closer to your use case, though you're correct that there's no native bitcount (assuming by "bitcount" you mean "count the number of bits set to 1 in the field). Also even with CHECK constraints being available, the question of unsigned ints seems frequent enough that there's at least one extension implementing unsigned types: https://github.com/petere/pguint https://github.com/petere/pguint (no idea about the quality or production-readiness).
- collyw 12y agoTo be honest I could have probably used signed integers but conceptually representing DNA sequence (4 possibilities at each position) was far easier to think about that two's compliment. The idea was to make everything as small and lean as possible, to get the maximum amount into memory, and it seemed to work. Bitcount / popcount / hamming distance as a CPU operation will have saved a lot of CPU cycles over the PG method I tried. You sound like you know Postgres fairly well. How feasible would it be to write a CPU based popcount extension?
- deleted 12y ago[deleted]