3 ms·

Because an RDBMS need not be I/O-bound. It might be compute bound (e.g., if the dataset fits in memory), so optimizing the compute-side can help. RDBMes gener

Because an RDBMS need not be I/O-bound. It might be compute bound (e.g., if the dataset fits in memory), so optimizing the compute-side can help. RDBMes generally compile queries into a "plan" that is then interpreted to execute it (SQLite3 compiles queries into bytecode, while PostgreSQL compiles them into AST-like tree structures). JITting certain portions of a query plan can help it go faster.

anarazel 7y ago
In fact, I'd say it's extremely common for database workloads to be compute bound on modern hardware. For one, storage bandwidth has increased massively (my laptop's NVMe drives can do 2 x 3.2GB/s reads, you can get quite a few into even small servers). While memory latency and CPU throughput have not increased to the same degree. To benefit from the increases in CPU throughput, one needs to take advantage of superscalar execution. Which isn't significantly possible with interpreted execution. > SQLite3 compiles queries into bytecode, while PostgreSQL compiles them into AST-like tree structures FWIW, expressions are compiled into bytecode in PostgreSQL as well. While there'd plenty benefit of doing that for query trees as well, there are not quite as much raw execution speed reason for it as there is for expressions (as the individual "steps" are much coarser, so the tree walk overhead is proportionally smaller).
timgarmstrong 7y ago
Being compute-bound is pretty standard for analytic queries (i.e. computing aggregate things over larger data sets). A lot of workloads do have high reuse rates of data so you'll get a lot of data cached in memory, and a lot of the processing is pretty CPU-intensive. Columnar data formats can also achieve very high compression rates, so a relatively small amount of data read off disk turns into a large number of rows. Plus, real queries often have insanely complex expressions (giant case statements, for example), that can burn a lot of compute. It's very different from a OLTP workload where a query will read 10s or 100s of rows via a btree index.

anarazel 7y ago

In fact, I'd say it's extremely common for database workloads to be compute bound on modern hardware. For one, storage bandwidth has increased massively (my laptop's NVMe drives can do 2 x 3.2GB/s reads, you can get quite a few into even small servers). While memory latency and CPU throughput have not increased to the same degree. To benefit from the increases in CPU throughput, one needs to take advantage of superscalar execution. Which isn't significantly possible with interpreted execution. > SQLite3 compiles queries into bytecode, while PostgreSQL compiles them into AST-like tree structures FWIW, expressions are compiled into bytecode in PostgreSQL as well. While there'd plenty benefit of doing that for query trees as well, there are not quite as much raw execution speed reason for it as there is for expressions (as the individual "steps" are much coarser, so the tree walk overhead is proportionally smaller).

timgarmstrong 7y ago

Being compute-bound is pretty standard for analytic queries (i.e. computing aggregate things over larger data sets). A lot of workloads do have high reuse rates of data so you'll get a lot of data cached in memory, and a lot of the processing is pretty CPU-intensive. Columnar data formats can also achieve very high compression rates, so a relatively small amount of data read off disk turns into a large number of rows. Plus, real queries often have insanely complex expressions (giant case statements, for example), that can burn a lot of compute. It's very different from a OLTP workload where a query will read 10s or 100s of rows via a btree index.