Why is it faster to create index and run query than run query without it?

I have table with few million rows. It contains logs from external service, so I've decided not to index it (lot of inserts, sparse reads).

When I run query that reads from the table without index, it takes (unsurprisingly) very long time.

However, when I create index and run the query and then drop the index, it is considerably faster (even with creating and dropping index).

Why is it faster to create index ad hoc, instead of letting SQL Server do it's thing? It seems unintuitive (Why wouldn't SQL Server create the index itself?).
Are there any downsides to this approach?

The query in question looks something like this, but I do not think it is necessarily relevant, as I've seen similar behavior elsewhere as well.

UPDATE Device
    SET Col1 = l.Col1
        ,Col2 = l.Col2
        ,Col3 = l.Col3
    FROM dbo.Device
        OUTER APPLY (
            SELECT MAX(Id) AS [Id]
            FROM dbo.Logs 
            WHERE Logs.Device_FK = Device.Id
            GROUP BY Logs.Device_FK
        ) lastLog
        OUTER APPLY (
            SELECT Col1, Col2, FORMAT(Col3) AS "Col3"
            FROM dbo.Logs
            WHERE Logs.Id = lastLog.Id
        ) l

The reason is the you repeatedly scan the Logs table. And you even have two cross apply in that query. The repeated scanning of this table is evidently more expensive than building the index and then using that index.

Nothing strange or unexpected here.

SQL Server could possibly do an index spool so it can used that for each visit to the Logs table. Perhaps the optimizer evaluated that strategy and discarded it since its estimates showed that it wouldn't be beneficial (incorrectly, perhaps). One very first step would be to study the execution plan, the estimates compared to actual values and take it from there.

StackExchange Database Administrators Q#280887, answer score: 5