3 ms·

Are you afraid the Postgres tuple-at-a-time iterator architecture is going to be limiting for your long term performance optimizations? Presumably the custom o

Are you afraid the Postgres tuple-at-a-time iterator architecture is going to be limiting for your long term performance optimizations? Presumably the custom operators you’ve implemented in your query plan can push projection and predicates down to a more efficient architecture without so many indirect function calls / branches / etc, but once you get up past that, aren’t you back in iterator land?

WireBaron 7y ago
We actually haven't been running against any limits here. One thing to keep in mind is that postgres remote-fetch operations aren't tuple-at-a-time, so this shouldn't be a bottleneck for our multi-node operations.
- ryanworl 7y ago
  Have you done any analysis of your per-core scan rates for simple aggregations like sum/count + group by with a reasonably large cardinality key? Or has anyone published a benchmark you trust on queries of that variety? An example would be TPC-H Q1, which is a little weak on the group by cardinality, but is good for testing raw aggregation performance.
  - WireBaron 7y ago
    We actually have done fairly extensive benchmarking of high cardinality data on our single-node product (we have a blog entry detailing at least our insert performance here: https://blog.timescale.com/blog/what-is-high-cardinality-how-do-time-series-databases-influxdb-timescaledb-compare/ https://blog.timescale.com/blog/what-is-high-cardinality-how...) We're actually currently focused on query optimization for our multi-node product, but we don't have any numbers we're currently ready to share.
einpoklum 7y ago
They probably don't care, otherwise they wouldn't have gone with PostgreSQL in the first place.
AdamProut 7y ago
I'm guessing the use cases they see don't involve query shapes that need to scan a lot of data. A columnstore along with vectorized or batched execution is a much better storage layout for fast scans with filters and aggregations (i.e., the top TPC-H and TPC-DS analytics benchmark results are from columnstore databases). A B-tree with tuple at a time execution is orders of magnitude slower [1][2]. [1] http://cidrdb.org/cidr2005/papers/P19.pdf http://cidrdb.org/cidr2005/papers/P19.pdf [2] http://db.csail.mit.edu/projects/cstore/vldb.pdf http://db.csail.mit.edu/projects/cstore/vldb.pdf

WireBaron 7y ago

We actually haven't been running against any limits here. One thing to keep in mind is that postgres remote-fetch operations aren't tuple-at-a-time, so this shouldn't be a bottleneck for our multi-node operations.

ryanworl 7y ago
Have you done any analysis of your per-core scan rates for simple aggregations like sum/count + group by with a reasonably large cardinality key? Or has anyone published a benchmark you trust on queries of that variety? An example would be TPC-H Q1, which is a little weak on the group by cardinality, but is good for testing raw aggregation performance.
- WireBaron 7y ago
  We actually have done fairly extensive benchmarking of high cardinality data on our single-node product (we have a blog entry detailing at least our insert performance here: https://blog.timescale.com/blog/what-is-high-cardinality-how-do-time-series-databases-influxdb-timescaledb-compare/ https://blog.timescale.com/blog/what-is-high-cardinality-how...) We're actually currently focused on query optimization for our multi-node product, but we don't have any numbers we're currently ready to share.

einpoklum 7y ago

They probably don't care, otherwise they wouldn't have gone with PostgreSQL in the first place.

AdamProut 7y ago

I'm guessing the use cases they see don't involve query shapes that need to scan a lot of data. A columnstore along with vectorized or batched execution is a much better storage layout for fast scans with filters and aggregations (i.e., the top TPC-H and TPC-DS analytics benchmark results are from columnstore databases). A B-tree with tuple at a time execution is orders of magnitude slower [1][2]. [1] http://cidrdb.org/cidr2005/papers/P19.pdf http://cidrdb.org/cidr2005/papers/P19.pdf [2] http://db.csail.mit.edu/projects/cstore/vldb.pdf http://db.csail.mit.edu/projects/cstore/vldb.pdf