时间戳

首页 > 解决方案 > 如何索引并使 WHERE 子句不区分大小写？

postgresql - 如何索引并使 WHERE 子句不区分大小写？

问题描述

在PostgreSQL 12中有这个表，没有索引

CREATE TABLE tbl
(
    ...
    foods json NOT NULL
)

样本记录：

foods:
{ 
    "fruits": [" 2 orange ", "1 apple in chocolate", " one pint of berry"],
    "meat": ["some beef", "ground beef", "chicken",...],
    "veg": ["cucumber"]
}

需要选择所有满足的记录：

  1. 水果含有orange。
  2. AND 肉包含beefor chicken。

select * from tbl where foods->> 'fruits' LIKE '%ORANGE%' and (foods->> 'meat' LIKE '%beef%' or foods->> 'meat' LIKE '%chicken%')

它是一个优化的查询吗？（我来自 RDBMS 世界）

如何索引以获得更快的响应而不是过度杀伤，以及如何使 PostgreSQL 不区分大小写？

标签： postgresql

解决方案

这会让你不开心。

您需要两个 trigram GIN 索引来加快速度：

CREATE EXTENSION pg_trgm;

CREATE INDEX ON tbl USING gin ((foods ->> 'fruits') gin_trgm_ops);
CREATE INDEX ON tbl USING gin ((foods ->> 'meat') gin_trgm_ops);

这些索引可能会变大并且会影响数据修改性能。

然后，您需要重写查询以使用ILIKE.

最后，查询可能比您想要的要慢，因为它将使用三个索引扫描和一个（可能很昂贵的）位图堆扫描。

但是对于这样的数据结构和子字符串匹配，你不能做得更好。

推荐阅读