为什么要扫描PostgreSQL查询表？

Question

我正在运行PostgreSQL 9.3。该数据库是所有医疗非营利机构(国家提供者编号)。我创建了以下索引：

CREATE INDEX indiv_provider_business_mailing_address_state_name_string_index
  ON individuals (provider_business_mailing_address_state_name);
CREATE INDEX indiv_provider_business_practice_location_address_state_name_string_index
  ON individuals (provider_business_practice_location_address_state_name);
CREATE INDEX indiv_upper_provider_business_mailing_address_state_name_string_index
  ON individuals (UPPER(provider_business_mailing_address_state_name));
CREATE INDEX indiv_upper_provider_business_practice_location_address_state_name_string_index
  ON individuals (UPPER(provider_business_practice_location_address_state_name));

但是，查询仍然会扫描：

Postgres Running
  SELECT npi, provider_first_name,
    provider_last_name_legal_name,
    provider_organization_name_legal_business_name,
    provider_first_line_business_mailing_address,
    provider_second_line_business_mailing_address,
    provider_business_mailing_address_city_name,
    provider_business_mailing_address_state_name,
    substr(provider_business_mailing_address_postal_code, 1, 5)
      AS provider_business_mailing_address_postal_code,
    provider_business_mailing_address_telephone_number,
    provider_business_mailing_address_fax_number,
    provider_first_line_business_practice_location_address,
    provider_second_line_business_practice_location_address,
    provider_business_practice_location_address_city_name,
    provider_business_practice_location_address_state_name,
    substr(provider_business_practice_location_address_postal_code, 1, 5)
      AS provider_business_practice_location_address_postal_code,
    provider_business_practice_location_address_telephone_number,
    provider_business_practice_location_address_fax_number
  FROM individuals
  WHERE ((((provider_business_mailing_address_state_name = UPPER(regexp_replace(?, '\s+', ' ', 'g'))))
    OR ((provider_business_practice_location_address_state_name = UPPER(regexp_replace(?, '\s+', ' ', 'g'))))))
  LIMIT 100

解释产出：

Postgres Limit  (cost=0.00..400.58 rows=100 width=141)
  ->  Seq Scan on individuals  (cost=0.00..1210010.61 rows=302063 width=141)
        Filter: ((provider_business_mailing_address_state_name = 'PA'::text) OR (provider_business_practice_location_address_state_name = 'PA'::text))

知道为什么吗？

更新

我按照changed (https://dba.stackexchange.com/a/118850/7924)的建议更改了查询，但它仍然是表扫描：

Postgres Running
  SELECT npi,
    provider_first_name,
    provider_last_name_legal_name,
    provider_organization_name_legal_business_name,
    provider_first_line_business_mailing_address,
    provider_second_line_business_mailing_address,
    provider_business_mailing_address_city_name,
    provider_business_mailing_address_state_name,
    substr(provider_business_mailing_address_postal_code, 1, 5)
      AS provider_business_mailing_address_postal_code,
    provider_business_mailing_address_telephone_number,
    provider_business_mailing_address_fax_number,
    provider_first_line_business_practice_location_address,
    provider_second_line_business_practice_location_address,
    provider_business_practice_location_address_city_name,
    provider_business_practice_location_address_state_name,
    substr(provider_business_practice_location_address_postal_code, 1, 5)
      AS provider_business_practice_location_address_postal_code,
    provider_business_practice_location_address_telephone_number,
    provider_business_practice_location_address_fax_number
  FROM individuals
  WHERE ((((provider_business_mailing_address_state_name = ?))
    OR ((provider_business_practice_location_address_state_name = ?))))
  LIMIT 100

解释产出：

Postgres Limit  (cost=0.00..400.58 rows=100 width=141)
  ->  Seq Scan on individuals  (cost=0.00..1210010.61 rows=302063 width=141)
    Filter: ((provider_business_mailing_address_state_name = 'PA'::text) OR (provider_business_practice_location_address_state_name = 'PA'::text))

Answer 1

表达式索引不使用，也不可用。

您正在索引一个与您所查询的内容不匹配的表达式。表达式在参数端，例如：

UPPER(regexp_replace(?, '\s+', ' ', 'g'))

PostgreSQL是基于替代参数的常量折叠，在您已经提供的情况下，它变成了文字'PA'。然后在provider_business_mailing_address_state_name列中查找它。

如果您打算像表达式索引所暗示的那样，针对provider_business_mailing_address_state_name列的上感知、裁剪版本进行搜索，那么您也必须在SQL中使用该表达式。

OR ((UPPER(regexp_replace(provider_business_practice_location_address_state_name, '\s+', ' ', 'g')) = UPPER(regexp_replace(?, '\s+', ' ', 'g'))))))

但坦率地说，在这种情况下，我建议将表中的数据规范化，而不是依赖表达式索引。