3 ms·
I don't hate query planning, some people are better than me at writing algo and the database knows my data better than me. However, what I hate is the lack of
by h1fra 2y ago
I don't hate query planning, some people are better than me at writing algo and the database knows my data better than me.
However, what I hate is the lack of transparency (and I feel like this article tries to pin-point just this). When I execute a query locally I get a different plan vs staging vs prod. A plan than can also change depending on some parameters or load or size.
I don't care about understanding all the underlying optimizations, I just care that the query plan I saw is the same and is still the same in prod, and that I can be warned when it changes. PG does not return the hash of the query plan or metrics along the data, which is imo a mistake. With this you could track it in your favorite metrics store and be able to point when and why stuff are executing differently.
- wrs 2y agoYes! The thing with SQL is that the DB “recompiles” the query plan from source every time you do the query. That results in changes at runtime, which are often good and healthy changes, but are sometimes a very bad surprise. I like the metrics idea, but by the time you see the change in the metric, it’s too late. For critical queries it might be helpful to be able to “freeze” a query plan just as one “freezes” a binary executable by compiling. In other words, let the query planner do its job, but only at a time of your choosing, so the performance of a production system doesn’t change suddenly. So no hints in the source, just an opaque token representing a compiled query plan that can be deployed alongside a binary. With tooling you could be notified if the planner wants to do it differently and decide whether to deploy the new plan, after testing. (And again, you’d only do this for a critical subset of your choice.)