SQL Server 表有 100k 条记录，2 个内连接速度极慢

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

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

我有以下表格：

• matches（id，开始时间）
• 比赛队伍(id, match_id, team_id, 分数)
• 比赛选手(id,阵容_id,玩家_id)，其中阵容_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

对于 100k 条记录，此查询大约需要 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"

Update

在这里添加了DDL：http://pastie.org/10529040 http://pastie.org/10529040

Update 2

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

Update 3

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

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

耦合稳定点：

• 所有表都应该有一个主键，并且需要有一个约束作为主键
• 如果您在其上加入 btree 索引，那么您要进行 JOINing、ORDER BUYing、GROUP BUYing 的任何大型表都将得到优化（如上所述）

因为引擎会自己“思考”，所以一旦您在表上添加索引，您通常需要告诉 Postgres“重新分析表”。另外，有时您需要“清理”它以清除 Postgres 认为存在的任何“死”行。

为此，请执行以下操作：

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

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

NOTE:您不需要在主键上放置索引，因为主键约束会创建自动唯一索引。

只要您对 match_teams 表有 PRIMARY KEY 约束（id），那么你应该只需要放一个btreematch_teams 表上的索引如下所示：

CREATE INDEX match_teams_match_id_idx ON match_teams USING btree (match_id);