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?