PG中文社区 /
mdi-home
首页 社区新闻 中文文档 加入ACE {{ item.text }} 登录
mdi-home 首页 mdi-chat-processing 社区新闻 mdi-book-open-variant 中文文档 mdi-account-multiple-check 加入ACE mdi-file-multiple-outline 相关资料 mdi-blank {{item.text}} mdi-exit-to-app 退出账号
利用PostgreSQL实现毫秒级全文检索

原作者:谢丽  创作时间:2015-05-13 00:00:00+08  
doudou586 发布于2015-05-18 11:11:37           评论: 0   浏览: 9582   顶: 1148  踩: 971 

Lateral是一家内容推荐服务提供商,其模拟程序使用PostgreSQL存储文档。每个文档包含一个text列和一个存储标题、日期和URL等元数据的JSON列。他们希望为模拟程序创建快速搜索功能,搜索文档全文和标题,生成推荐内容。近日,Lateral首席技术官Max撰文介绍了他们的做法。

为了实现这一目标,可以选择开源解决方案Apache Solr或Elasticsearch,也可以选择托管解决方案Elastic或Algolia,但出于以下考虑,他们选择了PostgreSQL的全文搜索功能:

  • 不需要额外安装软件或库
  • 可以重用他们在应用程序中使用的数据库接口
  • 不需要配置额外的服务器
  • 不增加成本
  • 数据可以存储在可控的地方
  • 不需要在不同的数据源之间同步数据

虽然PostgreSQL搜索的精度和大规模查询速度存在缺陷,但Max认为,它可以满足他们的应用场景。以下是他们的做法:

  • 创建一个列tsv,存储tsvector值;
  • 在新建的列上创建索引,并用下面的语句填充列:
    UPDATE data_rows SET tsv
    =setweight(to_tsvector(coalesce(meta->>'title','')), 'A') 
    ||setweight(to_tsvector(coalesce(text,'')), 'D');
    

    此处需要注意,JSON列的权重为A,text列的权重为D;
  • 创建tsv列更新函数;在表上创建触发器,当更新和新增行时,执行tsv列更新函数。
  • 当一切就绪后,替换下面代码中的“你的查询”并执行:
    SELECT id, meta->>'title' as title, meta FROM (
      SELECT id, meta, tsv
      FROM data_rows, plainto_tsquery('你的查询') AS q
      WHERE (tsv @@ q)
    ) AS t1 ORDER BY ts_rank_cd(t1.tsv, plainto_tsquery('你的查询')) DESC LIMIT 5;
    

    经测试,该查询大约50毫秒即可完成。如果返回文档全文,则会增加大约350毫秒,这更多的可能是受网络负载影响。如果只返回文档中的200个字符,则仅仅增加大约100毫秒。


  • 评论:0   浏览: 9582                   顶: 1148  踩: 971 

    请在登录后发表评论,否则无法保存。


    发表评论:
    加入我们
    QQ群1:5276420
    QQ群2:3336901
    QQ群3:254622631
    文档群:150657323
    文档翻译平台:按此访问
    社区邮件列表:按此订阅
    商业支持
    扫码关注
    加入我们
    QQ群1:5276420
    QQ群2:3336901
    QQ群3:254622631
    文档群:150657323
    文档翻译平台:按此访问
    社区邮件列表:按此订阅
    商业支持
    扫码关注
    © PostgreSQL中文社区 ... (自2010年起)