3 ms·

Something you'll discover when you do enough query optimization is that postgres' query planner isn't clause-order invariant. i.e. a AND b won't necessarily giv

by ris 6y ago

Something you'll discover when you do enough query optimization is that postgres' query planner isn't clause-order invariant. i.e. a AND b won't necessarily give you the same query plan as b AND a. My first reaction was that this was awful, but the more I thought about it, the more I was grateful that postgres (accidentally) gave me this knob to play with. Optimization of complex queries is a very tricky thing, and most engines won't do a good job of it 100% of the time. What I realized would have been a worse situation is if postgres sometimes picked a bad plan and there was little I could do to avoid it without reforming the query (a pain when you're generating the query with a query compiler already). Reordering the clauses effectively allowed me to ask the planner to roll the dice again. Following this, I may or may not have gone on to build a cache for the system that timed queries and kept notes of "good" clause orders for common queries, resorting to random ones otherwise....

shoo 6y ago
A variation of this is that the query planner may produce better plan if you add additional redundant constraints to the query that are logically implied by the constraints that are already there. Personally, it feels like a bit of a fragile mess trying to trick a sometimes-clever-sometimes-dumb optimiser into doing what you want by subtle indirect hacks - because the interface doesn't give you a way to directly override bad automated optimiser decisions.
- ris 6y ago
  > a fragile mess Oh, I'm not saying it wasn't a fragile mess... Seriously I actually considered the fragility of it to be a positive, precisely because it wasn't a hard "optimizer decision" that I was forcing. If you force something like that, you've got to take full responsibility for it - the planner will lose all intelligence over a certain decision and no longer do the clever thing query planners do and take the current distribution of the database's data into account to allow it to make better decisions. When you upgrade the database and the query planner gets smarter (or dumber), you've got to re-evaluate whether that's still the right decision. I could imagine an app with a bunch of out of date hard-forced decisions to be crippling performance wise. A look-aside of automatically deduced hints with a validity of less than a week seemed like a much lighter touch.
  - shoo 6y ago
    Totally get what you're saying. Ideally there's a way for user to supply additional information or constraints to the optimisation process which are used to influence the results without turning the optimisation process off completely. Although any scheme of doing that could, as you say, produce poor results if the distribution of data changes over time or the db planner code is changed. I used to work on non-database decision support tool that incorporated a custom optimiser which was used to spit out crude engineering designs for a particular kind of construction problem. The optimisation problem was difficult & the implementation to solve it was not state of the art: there were a few preprocessing stages that were used to lock in some early decisions using heuristics -- which helped massively reduce the search space, then a global optimisation approach was run on the remaining sub problem. The result was that the overall algorithm would locally optimise after perhaps locking in a bad early decision. Once the software was delivered to the client and in use by a small team of users I later discovered that the users had figured out that by running the software repeatedly with very small adjustments to input parameters (adjustments that should not obviously matter) they could bump the optimiser into outputting wildly different designs. It was a little bit like repeatedly pulling the handle on a one armed bandit until it eventually gave you a decent output. It was clever of our users to figure this out but the overall UI/UX was appalling, they had to click and re run a somewhat slow batch process until it produced a reasonable result. It would have been much better to give the users a user interface where they could directly override or constrain parts of of the engineering design problem in an ergonomic way, and then let the optimisation algorithm loose to make the remainder of the decisions.
tirumaraiselvan 6y ago
> postgres' query planner isn't clause-order invariant. i.e. a AND b won't necessarily give you the same query plan as b AND a. Do you have any references which shows this behaviour? Or a way to reproduce it?
- ris 6y ago
  Not to hand, but it's not amazingly hard to reproduce. You just have to build quite a complex query (many joins in our case) and play around with the clauses, watching the EXPLAIN output.

by ris 6y ago

shoo 6y ago

A variation of this is that the query planner may produce better plan if you add additional redundant constraints to the query that are logically implied by the constraints that are already there. Personally, it feels like a bit of a fragile mess trying to trick a sometimes-clever-sometimes-dumb optimiser into doing what you want by subtle indirect hacks - because the interface doesn't give you a way to directly override bad automated optimiser decisions.

ris 6y ago
> a fragile mess Oh, I'm not saying it wasn't a fragile mess... Seriously I actually considered the fragility of it to be a positive, precisely because it wasn't a hard "optimizer decision" that I was forcing. If you force something like that, you've got to take full responsibility for it - the planner will lose all intelligence over a certain decision and no longer do the clever thing query planners do and take the current distribution of the database's data into account to allow it to make better decisions. When you upgrade the database and the query planner gets smarter (or dumber), you've got to re-evaluate whether that's still the right decision. I could imagine an app with a bunch of out of date hard-forced decisions to be crippling performance wise. A look-aside of automatically deduced hints with a validity of less than a week seemed like a much lighter touch.
- shoo 6y ago
  Totally get what you're saying. Ideally there's a way for user to supply additional information or constraints to the optimisation process which are used to influence the results without turning the optimisation process off completely. Although any scheme of doing that could, as you say, produce poor results if the distribution of data changes over time or the db planner code is changed. I used to work on non-database decision support tool that incorporated a custom optimiser which was used to spit out crude engineering designs for a particular kind of construction problem. The optimisation problem was difficult & the implementation to solve it was not state of the art: there were a few preprocessing stages that were used to lock in some early decisions using heuristics -- which helped massively reduce the search space, then a global optimisation approach was run on the remaining sub problem. The result was that the overall algorithm would locally optimise after perhaps locking in a bad early decision. Once the software was delivered to the client and in use by a small team of users I later discovered that the users had figured out that by running the software repeatedly with very small adjustments to input parameters (adjustments that should not obviously matter) they could bump the optimiser into outputting wildly different designs. It was a little bit like repeatedly pulling the handle on a one armed bandit until it eventually gave you a decent output. It was clever of our users to figure this out but the overall UI/UX was appalling, they had to click and re run a somewhat slow batch process until it produced a reasonable result. It would have been much better to give the users a user interface where they could directly override or constrain parts of of the engineering design problem in an ergonomic way, and then let the optimisation algorithm loose to make the remainder of the decisions.

tirumaraiselvan 6y ago

> postgres' query planner isn't clause-order invariant. i.e. a AND b won't necessarily give you the same query plan as b AND a. Do you have any references which shows this behaviour? Or a way to reproduce it?

ris 6y ago
Not to hand, but it's not amazingly hard to reproduce. You just have to build quite a complex query (many joins in our case) and play around with the clauses, watching the EXPLAIN output.