At Heap we have non-trivial complexity in our analytical queries, and some of them can take a long time to complete. We did analyze features like the query planner's output, our query properties (type, parameters, complexity) and tried to automatically identify factors that contribute the most into the total query time. It turns out that you don't need to use machine learning for the basics, but at this point we were not aiming for predictions yet.
And how did you do that? Manually analyzing some queries?
But I think, as you said, it wouldn't apply for predictions, but instead for making long queries run faster, right?
As a spoiler: queries take long time because they do a lot of IO. Features like reachback depth and duration (e.g. what period is the analytical query about) can contribute a lot to the amount of IO, thus, the query time. I have a blog post in my queue about our analysis, would gladly bump its priority if there is interest in such details.
If it's not too much work, I would like to have some details on your process. It looks it's not exactly what I'm trying to do, but would certainly help me with my work.
I'm also curious: if you had a great way to predict the time/cost of the queries, how would you use it?
I'm working on something for my master degree (it's the idea, and I really hope I can make it possible) where I'll help the user choosing the resources for the database that will give him the best performance (or at least the performance he thinks it's good enough). So the idea would be to train each machine (with different resources) and then be able to predict for an specific query what the performance would be.
Thank you all for the answers so far. I hope we can clear my mind about this issue.