Anyone have any idea why this query takes 3-5 times as long to return the same data as this query?

The query to Tags is basically instant, and the execution plan seems to confirm that, with nearly all the time spent in PostsWithDeleted in both queries.

but for some reason, despite selecting the same data, the simpler query is much faster

(of course, I eventually want to select for multiple tags at once, removing the hardcoded WHERE TagName = 'android', but that won't work with the current execution speed)

Also FWIW I got my end goal working via a completely different approach, though I'm still curious what's up with the speed difference in those first two queries

@RyanM it looks like it does the outer apply against the whole Tags table first and then applies the filter in the nested loop. It becomes a bit more clear if you use the showplan_text: data.stackexchange.com/stackoverflow/query/1402138/… (bottom leaf nodes are executed first). This type of questions go well on Dba.se btw, if you want the nitty gritty details

I think a query hint might exist to tell the query plan optimizer to pick a different approach