Can I rely on the estimate execution plan to recommend indexes? I have a database on Microsoft SQL Server 2016, with a table of about one million rows, let's call it books. This query takes more than one minute, which is not acceptable:

select * from books where publisher_id in (857413, 857317, 857316)

There is a proper FK from publisher_id to the publishers table.
I display the Estimated Execution Plan, and it tells me that 100% of the cost is in the "Clustered Index Scan (clustered)" on the primary key of the books table.
And what worries me, it does not recommend an index at all.
By looking at the query, it seems obvious that an index will help. And in fact, when I create the index the query returns results instantly.
Did something go corrupt in my database, maybe statistics? Or do you believe I should nor, in general, trust what I read in the estimated execution plan?
There's a limitation where it will not give you missing indexes when the query plan is trivial, perhaps that's what you are encountering.

Right click on the SELECT operator and select Properties. Check if the Optimization Level is TRIVIAL.



I use the estimated execution plan a great deal when performance troubleshooting. It's rare that I'll have the actual plan. Regarding missing indexes, I don't just add what the plan thinks is missing. I test the index, variations of it, etc.