Tim: No. AI can help with execution plans, machine learning and algorithms can help with index creation, but neither are likely to be able to replace actual code tuning and changing logic within stored procedures and dynamic SQL. Plus, in five years, how many organizations will still be on SQL Server 2012, 2014, and 2016, all prior to any 2017 with the first attempt at automatic plan correction. Things are advancing well but making perf tuning and indexing redundant in five years—not likely at all.
Jonathan: Nope, not at all—I say that because people and ORMs write odd coding patterns that optimize in interesting ways, and I don't see AI being able to make the same logic tree decisions that creative code rewriting can do for getting the optimizer better information about reduced data sets. Early aggregations are possible when you understand the data set, business requirements, and what the end result 'should' be, which is not something the optimizer can actually predict or know until it has run the query. Let's just say that I am not concerned about having a lack of work in performance tuning any time soon.