3 ms·

I'm interested in how you think they could best be applied. I know I've pushed the point that it uses the genetic algorithm higher to ensure I got better plans

I'm interested in how you think they could best be applied. I know I've pushed the point that it uses the genetic algorithm higher to ensure I got better plans for larger join lists before. But what ways could we use ML to generate optimal plans for different query parameters? An optimal plan for one set of parameters could be totally non optimal for another set. How could we handle that? I'd actually love to see this type of discussion happening on the mailing list.

thom 9y ago
I don't have the answers - I would honestly like to see training done on real workloads. In at least _some_ situations, it seems like live-calculating statistics would be quicker and I know ML work has been done on estimating statistics more accurately. I'll be honest, for a lot of stuff I can live without any real intelligence. For my workloads, I'd be happy to wait for the query planner to gain full knowledge of the physical layout of the database before trying anything. My other main annoyance, as mentioned, is WHERE clause pushdowns. I think there is a simple, if not efficient, decision procedure for this stuff and I'm happy to wait for the planner to do the work.
- anarazel 9y ago
  Are the pushdown problems you're experiencing with postgres, or something else? If with PG, I'd very much welcome you to report them. While we know about some weaknesses of the predicate pushdown logic (some more complicated group bys, lateral + subqueries), it'd be good to know about more of them. FWIW, to me pushdown doesn't sound at all like a field where ML would be helpful...
  - thom 9y ago
    Yeah, I'm not really proposing some specialised ML algorithm within Postgres to handle these edge cases, I'm more hoping that someone comes along with an entirely new paradigm for describing data and queries, that allows a platform to evolve on top of that for maximum performance given the described workload. In a particular case that annoyed me this week, casting an IN clause with a subquery to an array (on a query over a view with a GROUP BY) resulted in wildly better performance, despite setting the statistics target to the maximum value, indexing everything etc. I'm happy to demonstrate a trivial reproduction of this issue, and I'm sure I'm a crap DBA, but every day it's the same fight - SQL is _not_ a declarative language. It is incredibly sensitive to implementation details, in ways that are way less obvious than any other programming language. Postgres could do a better job in all sorts of situations, but I personally think that finding all these edge cases algorithmically or analytically is futile. A platform built on a paradigm more closely aligned to the logical descriptions of data and queries could, over time, learn to perform better.

thom 9y ago

I don't have the answers - I would honestly like to see training done on real workloads. In at least _some_ situations, it seems like live-calculating statistics would be quicker and I know ML work has been done on estimating statistics more accurately. I'll be honest, for a lot of stuff I can live without any real intelligence. For my workloads, I'd be happy to wait for the query planner to gain full knowledge of the physical layout of the database before trying anything. My other main annoyance, as mentioned, is WHERE clause pushdowns. I think there is a simple, if not efficient, decision procedure for this stuff and I'm happy to wait for the planner to do the work.

anarazel 9y ago
Are the pushdown problems you're experiencing with postgres, or something else? If with PG, I'd very much welcome you to report them. While we know about some weaknesses of the predicate pushdown logic (some more complicated group bys, lateral + subqueries), it'd be good to know about more of them. FWIW, to me pushdown doesn't sound at all like a field where ML would be helpful...
- thom 9y ago
  Yeah, I'm not really proposing some specialised ML algorithm within Postgres to handle these edge cases, I'm more hoping that someone comes along with an entirely new paradigm for describing data and queries, that allows a platform to evolve on top of that for maximum performance given the described workload. In a particular case that annoyed me this week, casting an IN clause with a subquery to an array (on a query over a view with a GROUP BY) resulted in wildly better performance, despite setting the statistics target to the maximum value, indexing everything etc. I'm happy to demonstrate a trivial reproduction of this issue, and I'm sure I'm a crap DBA, but every day it's the same fight - SQL is _not_ a declarative language. It is incredibly sensitive to implementation details, in ways that are way less obvious than any other programming language. Postgres could do a better job in all sorts of situations, but I personally think that finding all these edge cases algorithmically or analytically is futile. A platform built on a paradigm more closely aligned to the logical descriptions of data and queries could, over time, learn to perform better.