postgresql - 如何使用日期列创建 pg_trgm 复合索引

Question

SELECT col1, max(date) as max_date
FROM table
WHERE col1 ILIKE 'name'
GROUP BY col1

这里的 col1 是 varchar 和 date 是 timestamp with time zone 数据类型。因此创建了扩展 CREATE EXTENSION pg_trgm

然后尝试了以下索引并得到了错误:

1:问题:错误:运算符类“gin_trgm_ops”不接受带时区的数据类型时间戳

CREATE INDEX CONCURRENTLY trgm_table_col1_date_index
ON table 
USING gin (provider_id, date gin_trgm_ops);

2:问题:错误:访问方法“gin”不存在运算符类“text_pattern_ops”

 CREATE INDEX CONCURRENTLY trgm_table_col1_date_index
    ON table 
    USING gin (provider_id, date text_pattern_ops);

如何为上述查询创建索引以加快执行速度？非常感谢任何帮助!

编辑:所以这在语法上有效但不会加快查询速度:

CREATE INDEX CONCURRENTLY trgm_provider_date_index
ON table 
USING gin (provider_id gin_trgm_ops, date_var) 

Answer 1

据我所知，Postgres 无法使用 GIN 索引优化 min()/max() 查询。

您可以将 date 列添加到 GIN 索引(以及其他标量类型，如 int、text 等)通过安装 btree_gin贡献模块。然而，由于 GIN 索引方法没有义务以任何特定顺序存储它们的条目，Postgres 不能假设它们对查找最大值有用，因此它们将被忽略。

(我在文档中找不到对此的具体引用，但我相信它与 the limitation which prevents anything other than a B-tree from being used to optimise an ORDER BY 相同。)