performance - 帮助加速 PostgreSQL 查询

Question

我正在尝试尽可能加快此查询的速度。它不是很慢，但我需要它尽可能快。

SELECT name
FROM (
  SELECT cities.name || ', ' || regions.name || ', ' || countries.code AS name
  FROM cities
  INNER JOIN regions ON regions.id = cities.region_id
  INNER JOIN countries ON countries.id = regions.country_id
) AS t1
GROUP BY name
HAVING LOWER(name) ILIKE 'asheville%'
ORDER BY name ASC
LIMIT 10;

存在这些索引:

UNIQUE INDEX index_cities_on_name_and_region_id ON cities USING btree (name, region_id)
UNIQUE INDEX index_countries_on_code ON countries USING btree (code)
UNIQUE INDEX index_countries_on_name ON countries USING btree (name)
UNIQUE INDEX index_regions_on_code_and_country_id ON regions USING btree (code, country_id)

城市表包含 248016 条记录。countries 表包含 252 条记录。regions 表包含 4005 条记录。

这是查询的解释输出:http://explain.depesz.com/s/fWe

如有任何帮助，我们将不胜感激。基本上我只是在寻找建议或者指出我可能遗漏的东西。

Answer 1

在您的子查询中，您应该同时返回您已经返回的 name 和 cities.name as cname。然后，你应该在 cname 而不是 name 上做你的 ilike。问题是现在没有办法让 PostgreSQL 真正推断出 'ashville%' 中没有任何逗号，它可以只查看城市名称子查询，所以它真的必须(并且是，根据你的解释)迭代并构建每一个可能的字符串，以便进行最后的过滤。如果您将 cities.name 返回到上层查询，它将显着提高性能，因为现在它严重不能使用您拥有的任何索引。

真的，你应该一路走到这里，只需删除查询中的字符串连接并返回你真正想要的:select cities.name as city, regions.name as region, countries.code as country，并将排序修改为 order by t1.city, t1.region, t1.country。

此外，您是否真的在寻找具有'ashville%' 的城市，或者这只是寻找具有'ashville 的城市的间接方式'，但你必须在内部处理逗号分隔？然后，在外面，使用 lower(t1.city) = 'ashville'(注意 =: lower(x) 就像 'lower' 是毫无意义的慢)。

此外，您还需要修复这些索引:您真正想要的是 create index whatever on cities((lower(name)))，因为这是您实际搜索的内容，而不是 name:如果您正在搜索与索引中的内容无关的内容，则无法使用这些索引。

(稍后您可能会查看按名称排序，并担心它不会再被加速，但这没关系:这里的目标是快速从大量可能的位置向下过滤到你要操作的一小部分；剩下的可以在内存中快速排序，因为你可能要处理 10-20 个结果。)

因此，由于regions.id和countries.id可能是primary key，所以其他的索引可以删除仅针对此查询。

最后，将查询扁平化为一个级别，删除group by，并将其替换为distinct。问题是我们要确保在尝试过滤器之前我们不强制 PostgreSQL 生成完整集:我们要确保它对目标有足够的了解，以便能够使用城市索引直接快速扫描到可以匹配的城市，然后开始填写地区和国家信息。

(PostgreSQL 通常非常非常擅长做这件事，即使是通过子查询，但是因为我们有一个 group by 子句通过 having，我可以看到它不再能够推断的情况。)

(编辑)实际上，等等:您在 cities (name, region_id) 上有一个唯一索引，所以您甚至不需要 distinct...它正在做的是使查询毫无意义地变得更加复杂。我只是继续将其从查询中删除:结果将是相同的，因为您不可能最终得到在同一地区/国家/地区的同一城市被返回两次的结果。

select
    cities.name as city,
    regions.name as region,
    countries.code as country
from cities
join regions on
    regions.id = cities.region_id
join countries on
    countries.id = regions.country_id
where
    lower(cities.name) = 'asheville'
order by
    cities.name,
    regions.name,
    countries.code
limit 10;

create index "cities(lower(name))" on cities ((lower(name)));

(编辑)如果，顺便说一句，你真的想做一个前缀匹配，那么你会想改变 = 'asheville' 回到 like 'ashevill% '(注意like: no i)，修改索引指定如下:

create index "cities(lower(name))" on cities ((lower(name)) text_pattern_ops);