postgre在Lucene或SQL中有更好的性能

Question

我正在使用PostgreSQL数据库。

有一个名为metadatavalue的表，其结构如下：

metadatavalue_id    integer  Primary Key Auto Increment
metadta_field_id    integer  Foreign Key
text_Value      varchar
text_lang       varchar
place           integer

当提交或添加任何内容时，都会添加一个包含近25个元数据字段的项目创建。

元数据值表已包含大约

十五万（150000）份记录。

我正在为一个字段实现一个自动完成功能，比如"作者"其被存储为表中的metadata_ field_。

当我在PgSQL提示符下查询表时，返回结果几乎需要1或2秒。

查询：

SELECT metadatavalue.text_value AS author, count(metadatavalue.text_value) AS count
   FROM metadatavalue
  WHERE (metadatavalue.metadata_field_id IN ( SELECT metadatafieldregistry.metadata_field_id
           FROM metadatafieldregistry
          WHERE metadatavalue.text_value LIKE 'Pra%' AND metadatafieldregistry.metadata_schema_id = 1 AND metadatafieldregistry.element::text = 'contributor'::text))
  GROUP BY metadatavalue.text_value;

由于它是自动完成的，当用户输入值时，查询可能会运行4-5次。

所以，我正在考虑实现基于LUCENE的搜索。

其中，首先从后端创建索引，然后对每个新项目创建索引创建，运行线程为新项编制索引。

我想知道Apache Lucene是更好的选择还是SQL可以进行优化。

编辑：还有另一个包含元数据字段的表，它被用作该值的元数据值表中的外键（metadatafieldregistry.metadata_field_id）。

Answer 1

我想说，如果完成了正确的索引，任何数据库都将优雅地处理至少一百万行，你没有理由进入Lucene或Solr，这将向你介绍新的任务，比如将索引与数据库的大多数当前状态同步。

此外，Lucene或Solr非常适合免费文本搜索。这意味着，如果你在你的Lucene"文档"上搜索"Bob Marley"，那么你会得到所有的文档，其中有"Bob Marly"，"Marley Bob"，或者只有"Bob"，只有"Marley"甚至"Bob…很多文本…Marley"。所以使用Lucene也取决于你试图涵盖的用例类型。

从您显示的查询中，我觉得如果对metadatavalue.text_valuemetadatafieldregistry.metadata_schema_id和metadatafieldregistry.element列进行索引，您将获得良好的性能。也可以尝试将查询转换为联接，而不是in查询。

感谢

Answer 2

在这样一个小的数据集上使用前缀查询，只要所需的列被正确地索引，Solr和PostgreSQL都应该表现得很好。

Answer 3

您没有提到metadatafieldregistry表的模式（实际上您说您只有一个表，但您的查询使用了两个）

查看explain analyze的输出，了解查询计划是什么，以及扫描需要花费什么时间。您的子查询是相关的，这几乎肯定不是一个好的计划：一般来说，模式闻起来有EAV的味道。您可能会发现部分索引很有帮助，它可以生成一个仅包含要对其进行前缀搜索的文本值的索引（可能限制metadata_schema_id和element）