4 ms·

This seems to be unfounded criticism. When statistics are gathered, PostgreSQL samples a certain percentage of the table, so that obviously scales. The number o

This seems to be unfounded criticism. When statistics are gathered, PostgreSQL samples a certain percentage of the table, so that obviously scales. The number of "most common values" and histogram buckets scales up to 10000, which should be good even for large tables. While I'll readily admit that not all aspects of cross-column dependencies are dealt with, and cross-table distributions are not considered, that has nothing to do with size. I guess you hit a problem somewhere, couldn't solve it and jumped to unwarranted conclusions.

jandrewrogers 6y ago
It is ironic that you accuse me of "unwarranted conclusions". I've been customizing and modifying PostgreSQL internals for almost two decades, I know how to read the source. You aren't as familiar with PostgreSQL as you think you are. This wasn't my problem, I was asked by a well-known company with many large PG installations and enterprise support contracts to look at the issue because no one else could figure it out. The limitations of the statistics collector are not only evident in the source but they are documented there. There are also deep architectural reasons why you can't trivially modify the statistics collector -- I looked into this option -- to work for larger tables without introducing other serious issues. If you have a uniform distribution of values, the statistics collector will work fine. In the above case, the values followed a power law distribution which created extreme biases in the statistical model due to necessary restrictions on sampling. Other distributions can have similar effects once you exceed the ability of the statistics collector to acquire a representative sample.
- brasetvik 6y ago
  > I know how to read the source. You aren't as familiar with PostgreSQL as you think you are. Oh, maybe you have read some of Laurenz Albe's many contributions to Postgres, then. https://git.postgresql.org/gitweb/?p=postgresql.git&a=search&h=HEAD&st=commit&s=laurenz https://git.postgresql.org/gitweb/?p=postgresql.git&a=search...
  - bobthebuilders 6y ago
    Nice deepfake. It's so surprising how far the field has gone. I re never seeing the fake Nixon moon speech and being blown away
  - syspec 6y ago
    murdered by words
- srcreigh 6y ago
  Naive question. Wouldn't sampling a power law dataset be straightforward? The idea is there's only a few outlier values, and the rest are uncommon. This distribution seems extremely common. Ie column with mostly NULL values and the rest somewhat unique non null strings. I'm curious what data you saw and why the sampling didn't work?
  - ldng 6y ago
    I'm curious, will he answered given his vested interest in "exabyte-scale analytical database" ? :-) And laurenz_albe has a vested interest in PG as he's a contributor. So I, too, would love more concrete details.
  - kikimora 6y ago
    I think the problem is PG does not know underlying data distribution and use one sampling strategy which works for normally distributed data.

jandrewrogers 6y ago

It is ironic that you accuse me of "unwarranted conclusions". I've been customizing and modifying PostgreSQL internals for almost two decades, I know how to read the source. You aren't as familiar with PostgreSQL as you think you are. This wasn't my problem, I was asked by a well-known company with many large PG installations and enterprise support contracts to look at the issue because no one else could figure it out. The limitations of the statistics collector are not only evident in the source but they are documented there. There are also deep architectural reasons why you can't trivially modify the statistics collector -- I looked into this option -- to work for larger tables without introducing other serious issues. If you have a uniform distribution of values, the statistics collector will work fine. In the above case, the values followed a power law distribution which created extreme biases in the statistical model due to necessary restrictions on sampling. Other distributions can have similar effects once you exceed the ability of the statistics collector to acquire a representative sample.

brasetvik 6y ago
> I know how to read the source. You aren't as familiar with PostgreSQL as you think you are. Oh, maybe you have read some of Laurenz Albe's many contributions to Postgres, then. https://git.postgresql.org/gitweb/?p=postgresql.git&a=search&h=HEAD&st=commit&s=laurenz https://git.postgresql.org/gitweb/?p=postgresql.git&a=search...
- bobthebuilders 6y ago
  Nice deepfake. It's so surprising how far the field has gone. I re never seeing the fake Nixon moon speech and being blown away
- syspec 6y ago
  murdered by words
srcreigh 6y ago
Naive question. Wouldn't sampling a power law dataset be straightforward? The idea is there's only a few outlier values, and the rest are uncommon. This distribution seems extremely common. Ie column with mostly NULL values and the rest somewhat unique non null strings. I'm curious what data you saw and why the sampling didn't work?
- ldng 6y ago
  I'm curious, will he answered given his vested interest in "exabyte-scale analytical database" ? :-) And laurenz_albe has a vested interest in PG as he's a contributor. So I, too, would love more concrete details.
- kikimora 6y ago
  I think the problem is PG does not know underlying data distribution and use one sampling strategy which works for normally distributed data.