如何索引和使WHERE子句大小写不敏感?

Question

将此表放在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. 和肉类含有beef或chicken。

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

是一个优化的查询吗?(我来自RDBMS世界)

如何索引更快的响应和不过度，也如何使PostgreSQL大小写不敏感?

Answer 1

这会让你不开心。

您将需要两个三重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。

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

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