PostgreSQL中tsvector列按数组前缀匹配统计行数的查询方法

你可以通过unnest()函数将指定的前缀数组展开为独立行，再关联目标表做统计，单条SQL实现如下：

SELECT
  p,
  COUNT(t.tsfield) AS match_row_count
FROM
  -- 替换ARRAY内的内容为你需要统计的所有前缀字符串
  unnest(ARRAY['prefix1', 'prefix2', 'prefix3']) AS all_prefix(p)
LEFT JOIN "myTable" t
  ON t.tsfield @@ (p || ':*')::tsquery
GROUP BY p;

扩展说明 #

• 如果你需要保留前缀数组的原始排序，可在unnest后加WITH ORDINALITY获取排序序号：

SELECT
  p,
  COUNT(t.tsfield) AS match_row_count
FROM
  unnest(ARRAY['prefix1', 'prefix2', 'prefix3']) WITH ORDINALITY AS all_prefix(p, sort_idx)
LEFT JOIN "myTable" t
  ON t.tsfield @@ (p || ':*')::tsquery
GROUP BY p, sort_idx
ORDER BY sort_idx;

• 若tsfield字段提前创建了GIN类型索引，该查询可以获得较好的执行性能。

内容的提问来源于stack exchange，提问作者Bob