News for hackers. Stuff that matters.
Re:Things I hate about PostgreSQL (2020) by natmaka on Tuesday April 06, 2021 @09:04AM (#26711288)
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;

Re:Things I hate about PostgreSQL (2020) by brianwawok on Tuesday April 06, 2021 @09:38AM (#26711580)
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.

Re:Things I hate about PostgreSQL (2020) by gkop on Tuesday April 06, 2021 @02:27PM (#26715500)
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.
Re:Things I hate about PostgreSQL (2020) by natmaka on Wednesday April 07, 2021 @09:47PM (#26734128)
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?

Re:Things I hate about PostgreSQL (2020) by gkop on Thursday April 08, 2021 @02:20PM (#26742011)
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.

Re:Things I hate about PostgreSQL (2020) by natmaka on Thursday April 08, 2021 @11:52PM (#26746494)
I understand and neglected this case, thank you!