Postgresql - 使用 OR/AND 的多列 gin 索引

Question

我正在“公司”表上运行以下查询:

SELECT *
  FROM companies
  WHERE name ILIKE '%foo%'
    AND (city ILIKE 'bar%' OR state ILIKE 'bar%')

我正在尝试使用 gin 索引，并且我为名称、城市和州添加了一个单独的索引:

CREATE INDEX trgm_idx_companies_name ON companies USING gin (name gin_trgm_ops);
CREATE INDEX trgm_idx_companies_city ON companies USING gin (city gin_trgm_ops);
CREATE INDEX trgm_idx_companies_state ON companies USING gin (state gin_trgm_ops);

但是，运行查询时只使用名称索引。我怀疑答案在于多列索引，但无论我尝试什么变体，我都无法让它工作。

Answer 1

我的猜测是“名称”比“城市”或“州”更具排他性。据推测，您在表格中经常重复相同的城市和州，但名称不会经常重复。

例如，如果这些是医院，完全不同的城市和州的两家医院使用相同名称“纪念医院”也不是不可能，但与两家医院同名“纪念医院”相比，这种情况要少得多同一个城市和州，这种情况经常发生(德克萨斯州休斯顿有一百家医院)。

因此，分析器可能意识到搜索“Memorial Hospital”可能会返回 X 个结果，X 是一个非常小的数字，只扫描这些 X 个结果比尝试使用索引要快。为什么索引一个有五个记录的表？诸如此类。

作为测试用例，试试这个:

SELECT *
FROM companies
WHERE  (city ILIKE 'bar%' OR state ILIKE 'bar%')

我猜你会发现查询同时使用索引和位图或。这应该让您对索引的工作有一定的信心——它们只是您的查询所不需要的。