具有 100k 记录的 SQL Server 表，2 个内部连接极慢

Question

我正在将我的数据从 SQL Server 迁移到 Postgres。

我正在更改我的表结构以处理一般体育比赛，但这给我带来了性能问题。

我有以下表格:

• 匹配(id，start_time)
• match_teams(id、match_id、team_id、分数)
• match_players(id、lineup_id、player_id)，其中 lineup_id 是 match_teams.id 上的外键

我正在使用以下查询选择所有匹配项:

SELECT * FROM matches AS m
INNER JOIN match_teams AS t ON m.id = t.match_id
INNER JOIN match_players AS p ON t.id = p.lineup_id

对于 10 万条记录，此查询大约需要 6 分钟:

-- Executing query:
SELECT * FROM matches AS m
INNER JOIN match_teams AS t ON m.id = t.match_id
INNER JOIN match_players AS p ON t.id = p.lineup_id
Total query runtime: 336360 ms.
1142078 rows retrieved.

在 SQL Server 上，我将所有这些数据都放在一个表中，它会在不到 5 秒的时间内返回。在 Postgres 中，我还使用 jsonb 将这些数据放入 1 个表中，并且能够在 40 秒内运行上述查询。

我怎样才能使这个查询更快？我想把它缩短到几秒钟。

在线阅读我发现创建索引可以加快这些连接的速度。我做了以下索引:

CREATE INDEX match_teams_match_id_idx ON match_teams USING btree (match_id);
CREATE INDEX match_players_lineup_id_idx ON match_players USING btree (lineup_id);
CREATE INDEX match_players_player_id_idx ON match_players USING btree (player_id);
CREATE INDEX matches_id_idx ON matches USING btree (id);

这些索引根本没有使查询更快。我错过了一个吗？

这是上述查询的 EXPLAIN ANALYZE VERBOSE 输出:

"Hash Join  (cost=19314.10..67893.04 rows=1135917 width=24) (actual time=401.225..1624.906 rows=1142078 loops=1)"
"  Output: m.id, m.start_time, t.team_id, t.rank, p.player_id"
"  Hash Cond: (p.lineup_id = t.id)"
"  ->  Seq Scan on public.match_players p  (cost=0.00..19818.78 rows=1142078 width=8) (actual time=0.039..356.168 rows=1142078 loops=1)"
"        Output: p.player_id, p.lineup_id"
"  ->  Hash  (cost=15119.58..15119.58 rows=228442 width=24) (actual time=401.123..401.123 rows=228442 loops=1)"
"        Output: m.id, m.start_time, t.team_id, t.rank, t.id"
"        Buckets: 8192  Batches: 4  Memory Usage: 3358kB"
"        ->  Hash Join  (cost=5097.97..15119.58 rows=228442 width=24) (actual time=74.766..310.864 rows=228442 loops=1)"
"              Output: m.id, m.start_time, t.team_id, t.rank, t.id"
"              Hash Cond: (t.match_id = m.id)"
"              ->  Seq Scan on public.match_teams t  (cost=0.00..3519.42 rows=228442 width=16) (actual time=0.004..64.580 rows=228442 loops=1)"
"                    Output: t.team_id, t.rank, t.match_id, t.id"
"              ->  Hash  (cost=3112.21..3112.21 rows=114221 width=12) (actual time=74.728..74.728 rows=114221 loops=1)"
"                    Output: m.id, m.start_time"
"                    Buckets: 16384  Batches: 2  Memory Usage: 2682kB"
"                    ->  Seq Scan on public.matches m  (cost=0.00..3112.21 rows=114221 width=12) (actual time=0.003..34.789 rows=114221 loops=1)"
"                          Output: m.id, m.start_time"
"Planning time: 0.448 ms"
"Execution time: 1799.412 ms"

更新

在此处添加 DDL:http://pastie.org/10529040

更新 2

Postgres 在 AWS RDS 服务器上运行。我尝试在干净的 EC2 服务器和干净的 PGAdmin 安装上运行上述查询。我得到了相同的结果，似乎在 ~2 秒内运行查询，但需要 ~6 分钟来显示数据。

更新 3

我尝试从一个简单的 C# 程序运行此查询，结果在大约 10 秒内返回。这似乎是 PGAdmin 的问题。

Answer 1

Postgres 有一个非常智能的查询引擎。我使用 Postgres，我经常从其他人那里听说“Postgres 很慢”——但我从未经历过这种情况。它可能没有其他 DBMS 可能具有的默认值，因此您只需要了解优化。

耦合稳定点:

• 所有的表都应该有一个主键，它需要有一个CONSTRAINT作为主键
• 如果您在其上放置 btree 索引(如上所述)，您正在连接、ORDER BY、GROUP BY 的任何大表都会优化

因为引擎会为自己“思考”，所以您经常需要告诉 Postgres 在为表添加索引后“重新分析表”。此外，有时您需要“清理”它以从中取出 Postgres 认为它​​具有的任何“死”行。

为此，请执行以下操作:

vacuum [schema].[table_name];
analyze [schema].[table_name];

在将索引放在上面之后执行此操作，它应该会大大加快查询执行速度。

注意:您不需要在 PRIMARY KEY 上放置索引，因为 PRIMARY KEY CONSTRAINT 会自动创建一个唯一的 INDEX。

只要你在 match_teams 表上有一个 PRIMARY KEY 约束(id)，那么你应该只需要像这样在 match_teams 表上放置一个 btree 索引:

使用 btree (match_id) 在 match_teams 上创建索引 match_teams_match_id_idx；