4 ms·

Isn't the optimizer fooled by some inadequately set parameter, for example "effective_cache_size"? The planner may be fooled due to a too small data sample, yo

Isn't the optimizer fooled by some inadequately set parameter, for example "effective_cache_size"? The planner may be fooled due to a too small data sample, you may try: ALTER TABLE table_name ALTER COLUMN column_name SET STATISTICS 10000; Can't you use the autovacuumer in order to kick an ANALYZE whenever there is a risk of data distribution shift? ALTER TABLE table_name autovacuum_analyze_scale_factor=X, autovacuum_analyze_threshold=Y;

brianwawok 6y ago
It's pretty hard to fix a complex black-box query planner with an estimate of when another black box analyze command will fix it. That said, if you have good monitoring, you can hopefully find out when a query gets hosed and at least have a chance to fix it.. it's not terribly often.
gkop 6y ago
Don’t blindly set stats to 10000, an intermediate value between the default of 100 and the max of 10000 may give you the best plans; experiment to find out.
- natmaka 6y ago
  I don't understand. At ANALYZE time isn't, all other parameters being equal and adequate (costs, GEQO at max, effective_cache_size ...), the probability of obtaining a representative set of columns better with a larger amount of randomly-selected values? Then at planning time isn't the the devised plan of better quality? Adding sampled values may be bad performance-wise, for example if the planner cannot take everything into account due to some margin/interval effect, and therefore produces the same plan using a bigger set of values. The random selection process may also, sometimes, select less-representative data in the biggest analyzed set. But how may it never lead to the best plans (which may be produced using a smaller analyzed set) or lead to (on average) worse plans?
  - gkop 6y ago
    First, your point about planning time is important, thanks for adding that. Regarding my point, it's possible that the planner may provide a better (on average, faster executed) plan for a given key, if that key is not found in stats, and that keys for which this is true may fit a pattern within the middle of the stats distribution. It all depends on the database schema and stats distributions.
    - natmaka 6y ago
      I understand and neglected this case, thank you!

brianwawok 6y ago

It's pretty hard to fix a complex black-box query planner with an estimate of when another black box analyze command will fix it. That said, if you have good monitoring, you can hopefully find out when a query gets hosed and at least have a chance to fix it.. it's not terribly often.

gkop 6y ago

Don’t blindly set stats to 10000, an intermediate value between the default of 100 and the max of 10000 may give you the best plans; experiment to find out.

natmaka 6y ago
I don't understand. At ANALYZE time isn't, all other parameters being equal and adequate (costs, GEQO at max, effective_cache_size ...), the probability of obtaining a representative set of columns better with a larger amount of randomly-selected values? Then at planning time isn't the the devised plan of better quality? Adding sampled values may be bad performance-wise, for example if the planner cannot take everything into account due to some margin/interval effect, and therefore produces the same plan using a bigger set of values. The random selection process may also, sometimes, select less-representative data in the biggest analyzed set. But how may it never lead to the best plans (which may be produced using a smaller analyzed set) or lead to (on average) worse plans?
- gkop 6y ago
  First, your point about planning time is important, thanks for adding that. Regarding my point, it's possible that the planner may provide a better (on average, faster executed) plan for a given key, if that key is not found in stats, and that keys for which this is true may fit a pattern within the middle of the stats distribution. It all depends on the database schema and stats distributions.
  - natmaka 6y ago
    I understand and neglected this case, thank you!