sql-server - 使用位域和全文搜索查询非常慢

Question

我有一个奇怪的问题，如果我执行以下查询，Sql Server（2008 和 2016）大约需要 35 秒才能完成。

SELECT DISTINCT F.FilterTitle, F.FilterSlug, F.FilterOrderList, F.FilterType

FROM products p
JOIN products_translations pt ON pt.ProductId = p.Id AND pt.Culture = 'it'
JOIN facets F ON F.ProductId = p.Id AND F.Culture = 'it' AND F.FilterType = 2
JOIN products_categories pc ON pc.productId = p.id
JOIN categories C ON pc.CategoryId = C.Id
JOIN categories_slugs cps ON cps.CategoryId = C.ParentCategoryId AND cps.Culture = 'it'
JOIN categories_slugs cs ON cs.CategoryId = C.Id AND cs.Culture = 'it'

WHERE cps.value = 'string1' AND cs.value = 'string2'
AND CONTAINS(pt.DescriptionForSearch, '"value*"', LANGUAGE 1040) --this is a full text
AND p.Visible = 1 AND p.Payments = 0 --both are bit fields

ORDER BY F.FilterType, F.FilterOrderList, F.FilterTitle;

如果我评论该行

and p.Visible = 1 and p.Payments = 0

查询只需要几毫秒。如果我删除线也是一样

and contains(pt.DescriptionForSearch, '"value*"' , language 1040)

并保持其他声明不变。我还为两个位字段创建了两个索引而没有更改。

有人可以解释为什么会这样吗？

更新

在此链接上，我忘记包含两个执行计划。

标签： sql-server