I have tried to increase the statistics target to 5000, and it helps, but it reduces the error to 100X. Still crazy high.
As far as I know, increasing default_statistics_target will not help. [1]
I have considered these fixes:
- hardcode the statistics to a particular ratio of the total number of rows
You can hardcode the percentage of distinct values: ALTER TABLE bigtable ALTER COLUMN instrument_ref SET ( n_distinct=-0.06 ); /* -1 * (33385922 / 500000000) */
Thanks for sharing. Very interesting read. If anyone has reference to the papers alluded to, that would be appreciated. I had forgotten about the option to set negative values.