I have a Postgres database with tables in the billion scale. So any aggregate functions such as count() and avg(), as well as "order by random()" are very time consuming. Postgres has pg_catalog which contains lots of useful statistics (such as the histogram bins in view pg_stats) that describe a database. Is there a way to take advantage of the statistics in pg_catalog to estimate the average and median numbers over a numeric column in a Postgres table?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
If an estimate is good enough, then statistical sampling is your friend. I'd probably use a sample size calculator to determine how many rows I need, then write some code to randomly insert that many keys into a table. A join, a function, and you're done.
If you've never done anything like this before, you'll probably want to do some background reading. When I had to do that stuff, I used a handbook from nist.gov. (And you'll probably be surprised at how small a sample you need.)
You can get a reasonable estimate of the median using pg_stats if you set the number of bins to 2. You need to set it low because it is a maximum, and the idea is to force an even number of bins so the middle bound is the middle of the distribution. Set it to 100 and postgres is free to use 99, 97, ... which is not what we want.
For the average you can take the average of the mid-points of the histogram bins, in this case having more bins is an advantage:
Note that in either case the result is distorted as the number of elements in