2 ms·

What I’m saying is the plans have a tiny budget devoted to their optimisation. For something like a view, I would happily let the optimiser run for hours findin

by thom 4y ago

What I’m saying is the plans have a tiny budget devoted to their optimisation. For something like a view, I would happily let the optimiser run for hours finding a speedup if it were then reliably delivered. As it is, none of that happens and you often have to rewrite to a functionally identical but faster query. The furthest you can go on something like Postgres is tweaking some config and turning off stuff like GEQO in favour of brute force etc.

dspillett 4y ago
> if it were then reliably delivered This is the limiting factor there. The best plan is going to vary depending on data patterns and (if variables are involved) input parameters. You would have to spend a potentially infinite amount of time testing for different patterns and store which plan is best for each, or do the analysis for every possible parameter each time the referenced data changes. > plans have a tiny budget devoted to their optimisation The reason planners get minimal time for planning is that in the general you quickly hit the point of diminishing returns. Their job is to find something good enough and find it quickly. They are helped by pre-computed hints like index stats, so you could perhaps spend more time building other helpful pre-computed data but you would need to reassess this data regularly (as index stats are reconsidered under certain conditions) and again you hit a point of diminishing returns in terms of the amount of time you spend on this as data it modified compared to the potential benefits from better plans. > For something like a view Databases do not create query plans for views when the view is created. They create query plans for overall statements that use the views when those statements are executed (or chose a cached one if the statement is sufficiently similar to a previous one for which there is a cached plan).
- thom 4y ago
  This is a very pessimistic argument, undermined by the fact that every single day, humans rewrite queries - in the supposedly declarative language SQL - to be identical but faster. I want a query planner that can be told to go away and try to do better with more resources. I don't know why this is unthinkable.
  - ttfkam 4y ago
    Yes, just like in browsers where one API is faster in Chrome and slower in Firefox or vice versa. Then Safari drops in and completely upends the notion of "best strategy". The underlying implementation will always affect top level access performance. "Best" will always be implementation dependent. That said, the DBs I use most (Postgres, MySQL, SQLite, and their derivatives) have pretty consistently improved performance from version to version with surprisingly few regressions. Also there have been more "knobs and dials" added to get what you want. WITH foo MATERIALIZED AS … springs immediately to mind. The ability to set or remove an optimization fence does wonders while still maintaining SQL's declarative roots.

by thom 4y ago

dspillett 4y ago

> if it were then reliably delivered This is the limiting factor there. The best plan is going to vary depending on data patterns and (if variables are involved) input parameters. You would have to spend a potentially infinite amount of time testing for different patterns and store which plan is best for each, or do the analysis for every possible parameter each time the referenced data changes. > plans have a tiny budget devoted to their optimisation The reason planners get minimal time for planning is that in the general you quickly hit the point of diminishing returns. Their job is to find something good enough and find it quickly. They are helped by pre-computed hints like index stats, so you could perhaps spend more time building other helpful pre-computed data but you would need to reassess this data regularly (as index stats are reconsidered under certain conditions) and again you hit a point of diminishing returns in terms of the amount of time you spend on this as data it modified compared to the potential benefits from better plans. > For something like a view Databases do not create query plans for views when the view is created. They create query plans for overall statements that use the views when those statements are executed (or chose a cached one if the statement is sufficiently similar to a previous one for which there is a cached plan).

thom 4y ago
This is a very pessimistic argument, undermined by the fact that every single day, humans rewrite queries - in the supposedly declarative language SQL - to be identical but faster. I want a query planner that can be told to go away and try to do better with more resources. I don't know why this is unthinkable.
- ttfkam 4y ago
  Yes, just like in browsers where one API is faster in Chrome and slower in Firefox or vice versa. Then Safari drops in and completely upends the notion of "best strategy". The underlying implementation will always affect top level access performance. "Best" will always be implementation dependent. That said, the DBs I use most (Postgres, MySQL, SQLite, and their derivatives) have pretty consistently improved performance from version to version with surprisingly few regressions. Also there have been more "knobs and dials" added to get what you want. WITH foo MATERIALIZED AS … springs immediately to mind. The ability to set or remove an optimization fence does wonders while still maintaining SQL's declarative roots.