database - 使用 Postgres 在 varchar 列上使用 distinct/group by 进行慢速查询

Question

我有一个 company 表和一个 industry 表，有一个多对多关系表链接这两个表，名为 company_industry。 company 表目前大约有 750.000 行。

现在我需要一个查询来查找给定行业的所有唯一城市名称，其中至少有一家公司。所以基本上我必须找到与给定行业相关的所有公司，并为这些公司选择唯一的城市名称。

我可以编写很好地执行此操作的查询，但达不到我正在寻找的性能。之前我对性能有点怀疑，因为 city_name 列的类型是 VARCHAR。不幸的是，我目前不可以自由地将数据库架构更改为更规范化的内容。

我做的第一件事是在 city_name 列上添加索引，然后我尝试了以下查询。

SELECT c.city_name AS city
FROM industry AS i 
INNER JOIN company_industry AS ci ON (ci.industry_id = i.id)
INNER JOIN company AS c ON (c.id = ci.company_id)
WHERE i.id = 288
GROUP BY city;

上述查询平均需要大约两秒钟的时间来执行。将 GROUP BY 替换为 DISTINCT 时也是如此。下面是上述查询的执行计划。

HashAggregate  (cost=56934.21..56961.61 rows=2740 width=9) (actual time=2421.364..2421.921 rows=1962 loops=1)
  ->  Hash Join  (cost=38972.69..56902.50 rows=12687 width=9) (actual time=954.377..2411.194 rows=12401 loops=1)
        Hash Cond: (ci.company_id = c.id)
        ->  Nested Loop  (cost=0.28..13989.91 rows=12687 width=4) (actual time=0.041..203.442 rows=12401 loops=1)
              ->  Index Only Scan using industry_pkey on industry i  (cost=0.28..8.29 rows=1 width=4) (actual time=0.015..0.018 rows=1 loops=1)
                    Index Cond: (id = 288)
                    Heap Fetches: 0
              ->  Seq Scan on company_industry ci  (cost=0.00..13854.75 rows=12687 width=8) (actual time=0.020..199.087 rows=12401 loops=1)
                    Filter: (industry_id = 288)
                    Rows Removed by Filter: 806309
        ->  Hash  (cost=26036.52..26036.52 rows=744152 width=13) (actual time=954.113..954.113 rows=744152 loops=1)
              Buckets: 4096  Batches: 64  Memory Usage: 551kB
              ->  Seq Scan on company c  (cost=0.00..26036.52 rows=744152 width=13) (actual time=0.008..554.662 rows=744152 loops=1)
Total runtime: 2422.185 ms

我尝试将查询更改为使用如下子查询，这使查询速度大约提高了一倍。

SELECT c.city_name
FROM company AS c
WHERE EXISTS(
  SELECT 1
  FROM company_industry
  WHERE industry_id = 288 AND company_id = c.id
)
GROUP BY c.city_name;

以及这个查询的执行计划:

HashAggregate  (cost=47108.71..47136.11 rows=2740 width=9) (actual time=1270.171..1270.798 rows=1962 loops=1)
  ->  Hash Semi Join  (cost=14015.50..47076.98 rows=12690 width=9) (actual time=194.548..1251.785 rows=12401 loops=1)
        Hash Cond: (c.id = company_industry.company_id)
        ->  Seq Scan on company c  (cost=0.00..26036.52 rows=744152 width=13) (actual time=0.008..537.856 rows=744152 loops=1)
        ->  Hash  (cost=13856.88..13856.88 rows=12690 width=4) (actual time=194.399..194.399 rows=12401 loops=1)
              Buckets: 2048  Batches: 1  Memory Usage: 436kB
              ->  Seq Scan on company_industry  (cost=0.00..13856.88 rows=12690 width=4) (actual time=0.012..187.449 rows=12401 loops=1)
                    Filter: (industry_id = 288)
                    Rows Removed by Filter: 806309
Total runtime: 1271.030 ms

更好，但希望你们能帮助我做得更好。

基本上，查询的昂贵部分似乎是查找唯一的城市名称(正如预期的那样)，即使在列上有索引，性能也不够好。我在分析执行计划方面很生疏，但我把它们包括在内，这样你们就可以确切地看到发生了什么。

我怎样才能更快地检索这些数据？

我正在使用 Postgres 9.3.5，DDL 如下:

CREATE TABLE company (
  id SERIAL PRIMARY KEY NOT NULL,
  name VARCHAR(150) NOT NULL,
  city_name VARCHAR(50),
);

CREATE TABLE company_industry (
  company_id INT NOT NULL REFERENCES company (id) ON UPDATE CASCADE,
  industry_id INT NOT NULL REFERENCES industry (id) ON UPDATE CASCADE,
  PRIMARY KEY (company_id, industry_id)
);

CREATE TABLE industry (
  id SERIAL PRIMARY KEY NOT NULL,
  name VARCHAR(100) NOT NULL
);

CREATE INDEX company_city_name_index ON company (city_name);

Answer 1

两个查询计划中都有一个 Seq Scan on company_industry，这实际上应该是(位图)索引扫描。 Seq Scan on company 也是如此。

似乎是缺少索引 的问题 - 或者您的数据库中存在某些不正确的地方。如果出现问题，请在继续之前进行备份。检查成本设置和统计是否有效:

• Keep PostgreSQL from sometimes choosing a bad query plan

如果设置良好，我会检查相关指标(详见下文)。也许 REINDEX 可以修复它:

REINDEX TABLE company;
REINDEX TABLE company_industry;

也许你需要做更多:

• Optimize Postgres query on timestamp range

此外，您还可以简化查询:

SELECT c.city_name AS city
FROM   company_industry ci
JOIN   company          c ON c.id = ci.company_id
WHERE  ci.industry_id = 288
GROUP  BY 1;

注意事项

如果您的 PK 约束在 (company_id, industry_id) 上添加另一个(唯一的)索引 (industry_id, company_id) (颠倒顺序!)。为什么？

• Is a composite index also good for queries on the first field?

Seq Scan on company 同样麻烦。 company(id) 似乎没有索引，但是您的 ER 图显示了 PK，所以这不可能？
最快的选择是在 (id, city_name) 上建立多列索引 - 如果(且仅当)您从中获得仅索引扫描。

因为您已经有了给定行业的 ID，所以根本不需要包含表 industry 表。

ON 子句中的表达式不需要括号。

这很不幸:

Unfortunately I do currently not have the liberty of being able to change the database schema to something more normalized.

您的简单模式对于几乎没有冗余且对可用缓存内存几乎没有压力的小表很有意义。但是城市名称在大表中可能是高度冗余的。 规范化会显着缩小表和索引的大小，这是影响性能的最重要因素。
具有冗余存储的非规范化形式有时可以加速目标查询，有时不能，这取决于。但它总是对其他一切产生不利影响。冗余存储会占用更多可用缓存，因此必须尽快清除其他数据。即使您在局部获得了一些东西，您也可能会失去整体。
在这种特殊情况下，为 city_id int 列获取不同的值也会便宜得多，因为 integer 值比(可能很长的)字符串更小且比较速度更快。 company 中 (id, city_id) 的多列索引将小于 (id, city_name) 的索引，并且处理速度更快。折叠许多重复项后再加入一个相对便宜。

如果您需要最佳性能，您可以随时添加一个 MATERIALIZED VIEW 用于特殊目的，使用预先计算的结果(易于聚合并使用 industry_id 的索引)，但要避免在主表中使用大量冗余数据。