Manually updating planner statistics

Question

We have a severe misprediction of cardinality of some columns on some tables in our system after running ANALYZE (the tables in question have hundreds of millions to billions records). Increasing statistics target to the max = 10000 helps only a little. It seems in our case we can't make it work with acceptable accuracy using regular Postgres mechanisms, because of our data distribution and probabilistic sampling of Postgres.
We could in theory collect n_distinct and mcv values ourselves, the overhead on that may be acceptable. The question is, would it be ok to update pg_statistic, pg_statistic_ext and pg_statistic_ext_data manually? Are there any known side effects of that?
P.S. I'm aware that we can set n_distinct on the columns through ALTER TABLE, but collection of mcv values will still be on Postgres.

Answer 1

There is no way to override the statistics PostgreSQL collects, with the exception of n_distinct, which can be set with ALTER TABLE ... ALTER COLUMN ... SET (n_distinct = ...). You can update pg_statistic if you are a superuser, but the data types used might require that you use a C function. Also, updating catalog tables is not supported and not a good solution here, because these statistics are lost during an upgrade or whenever autovacuum decides to ANALYZE the table.

Answer 2

Actually, I've found a way to do that (by looking at some abandoned extension of Postgres Pro - https://github.com/postgrespro/postgrespro/tree/DEV/contrib/dump_stat). The way to write into anyarray columns is to utilize some obscure function called array_in. It's very underdocumented, I'd say, only mentioned in Postgres documentation once, but it can do the trick.