postgresql - Postgres pg_trgm - 为什么按相似度排序很慢

Question

我有表 Users，该列上有列 displayName (text) 和 pg_trgm gin index。

CREATE INDEX "Users-displayName-pg-trgm-index"
  ON "Users"
  USING gin
  ("displayName" COLLATE pg_catalog."default" gin_trgm_ops);

这是我的查询:

SELECT "User"."id"
    ,"User"."displayName"
    ,"User"."firstName"
    ,"User"."lastName"
    ,"User"."email"
    ,"User"."password"
    ,"User"."isVerified"
    ,"User"."isBlocked"
    ,"User"."verificationToken"
    ,"User"."birthDate"
    ,"User"."gender"
    ,"User"."isPrivate"
    ,"User"."role"
    ,"User"."coverImageUrl"
    ,"User"."profileImageUrl"
    ,"User"."facebookId"
    ,"User"."deviceType"
    ,"User"."deviceToken"
    ,"User"."coins"
    ,"User"."LocaleId"
    ,"User"."createdAt"
    ,"User"."updatedAt"
FROM "Users" AS "User"
WHERE (similarity("User"."displayName", 'John') > 0.2)
ORDER BY similarity("User"."displayName", 'John')
    ,"User"."id" ASC LIMIT 25;

上面的查询需要 ~200ms 来返回结果。当我删除

ORDER BY similarity("User"."displayName", 'John')

并仅通过 id 进行排序，然后查询速度可达 30ms。

我正在查询具有 50k 用户的表。

这是解释分析:http://explain.depesz.com/s/lXC

出于某种原因，我没有看到任何索引使用情况(displayName 上的gin pg_trgm)

---

似乎当我更换线

WHERE (similarity("User"."displayName", 'John') > 0.2)

与

WHERE ("User"."displayName" % 'John')

查询速度超快 - 谁能告诉我为什么？我认为 % 运算符只是检查 similarity(...) 是否大于 treshold... 那么有什么区别呢？

Answer 1

PostgreSQL 不为函数使用索引，它只为运算符使用索引。

按 similarity() 排序的查询为每一行调用该函数，然后对行进行排序。

使用 % 的查询使用索引并对匹配的那些运行相似性函数(没有索引只扫描函数)。

如果你想按相似度最小(如问题)排序相似度大于 0.2 的那些，你应该使用 distance operator <-> .

像这样:

WHERE "User"."displayName" <-> 'John' < 0.8
ORDER BY "User"."displayName" <-> 'John' DESC

距离为 1 - 相似度因此为 0.8