我对SQL完全陌生，我正在努力加快大型数据的联接查询速度。我开始添加索引(但老实说，我对它们没有深入的了解)，没有看到太大的变化，我决定以一个更简单的模拟示例为基准。我使用的是MacOS10.14.6上PostgreSQL11.5的psql接口。数据服务器在我的计算机上本地托管。我对任何缺乏相关信息表示歉意，这是我第一次发布有关SQL的信息。
数据库的结构
我创建了两个最初完全相同的数据库，db和db_idx。我从不在数据库中的表上放置任何索引或键，而我尝试在db_idx中的表中放置索引和键。然后，我分别在db和db_idx中运行简单的联接查询，并比较性能。具体来说，db_idx由两个表组成：
- 具有100000行和以下结构的客户端表：
Table "public.client"
Column | Type | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
client_id | integer | | not null |
client_name | text | | |
Indexes:
"pkey_c" PRIMARY KEY, btree (client_id)
- 一个具有70000行和以下结构的client_additional表：
Table "public.client_additional"
Column | Type | Collation | Nullable | Default
------------+---------+-----------+----------+---------
client_id | integer | | not null |
client_age | integer | | |
Indexes:
"pkey_ca" PRIMARY KEY, btree (client_id)
"cov_idx" btree (client_id, client_age)
client_additional表中的client_id列包含客户机的客户端_id值的子集。请注意主键和我在client_additional上创建的其他索引。我原以为这些会提高基准查询速度(见下文)，但事实并非如此。
重要的是，数据库完全相同(相同的结构，相同的值)，只是它没有索引或键
附带说明：客户端和客户端附加表可能应该是一个单独的表，因为它们提供的信息完全相同(客户端级别)。然而，我在现实生活中使用的数据库是这样构建的：一些表按"主题"划分为几个表，尽管它们提供了相同级别的信息。我不知道这对我的问题是否重要。
基准查询
我使用以下查询，它模拟了我需要对真实数据进行的许多操作：
SELECT
client_additional.client_id,
client_additional.client_age,
client.client_name
FROM client
INNER JOIN client_additional
ON client.client_id = client_additional.client_id;
基准结果
在这两个数据库上，基准查询大约需要630毫秒。删除db_idx中的键和/或索引不会更改任何内容。这些基准测试结果会传递到更大的数据大小：在索引和非索引的情况下，速度是相同的。
这就是我的处境。我该如何解释这些结果？我可以提高加入速度吗？如何提高？
使用EXPLAIN谓词查看SQL引擎打算如何解决查询。(不同的SQL引擎以不同的方式呈现这一点。)您可以最终确定是否会使用索引。
此外，您首先需要加载带有批测试数据的表，因为EXPLAIN会告诉您SQL引擎现在打算做什么，并且这个决定部分基于表的大小和各种其他统计信息。如果表实际上是空的，SQL引擎可能会认为索引现在对没有帮助
SQL引擎使用各种非常聪明的技巧来优化性能，因此实际上很难获得有用的时间测试。但是，如果EXPLAIN告诉您正在使用索引，那么这几乎就是您正在寻找的答案。
设置一个小型测试数据库，添加一些行并运行查询：
CREATE TABLE client
(
client_id integer PRIMARY KEY,
client_name text
);
CREATE TABLE client_additional
(
client_id integer PRIMARY KEY,
client_age integer
);
INSERT INTO client (client_id, client_name) VALUES (generate_series(1,100000),'Phil');
INSERT INTO client_additional (client_id, client_age) VALUES (generate_series(1,70000),21);
ANALYZE;
EXPLAIN ANALYZE SELECT
client_additional.client_id,
client_additional.client_age,
client.client_name
FROM
client
INNER JOIN
client_additional
ON
client.client_id = client_additional.client_id;
给了我这个计划：
Hash Join (cost=1885.00..3590.51 rows=70000 width=11) (actual time=158.958..44 1.222 rows=70000 loops=1)
Hash Cond: (client.client_id = client_additional.client_id)
-> Seq Scan on client (cost=0.00..1443.00 rows=100000 width=7) (actual time =0.019..100.318 rows=100000 loops=1)
-> Hash (cost=1010.00..1010.00 rows=70000 width=8) (actual time=158.785..15 8.786 rows=70000 loops=1)
Buckets: 131072 Batches: 1 Memory Usage: 3759kB
-> Seq Scan on client_additional (cost=0.00..1010.00 rows=70000 width =8) (actual time=0.016..76.507 rows=70000 loops=1)
Planning Time: 0.357 ms
Execution Time: 506.739 ms
从中可以看到，两个表都被顺序扫描，每个表的值都被散列，并进行了散列连接。Postgres认为这是执行该查询的最佳方式。
如果你要在没有主键的情况下重新创建表(因此删除了每个表PK列上的隐式索引)，你会得到完全相同的计划，因为Postgres已经确定，执行这个查询的最快方法是忽略索引，对表的值进行哈希，然后对两组哈希值进行哈希连接以获得结果。
更改客户端表中的行数后，如下所示：
TRUNCATE Client;
INSERT INTO client (client_id, client_name) VALUES (generate_series(1,200000),'phil');
ANALYZE;
然后我重新运行了相同的查询，我看到了这个计划：
Merge Join (cost=1.04..5388.45 rows=70000 width=13) (actual time=0.050..415.50
3 rows=70000 loops=1)
Merge Cond: (client.client_id = client_additional.client_id)
-> Index Scan using client_pkey on client (cost=0.42..6289.42 rows=200000 width=9) (actual time=0.022..86.897 rows=70001 loops=1)
-> Index Scan using client_additional_pkey on client_additional (cost=0.29..2139.29 rows=70000 width=8) (actual time=0.016..86.818 rows=70000 loops=1)
Planning Time: 0.517 ms
Execution Time: 484.264 ms
在这里，您可以看到索引扫描已经完成，因为Postgres已经根据表中当前的行数确定了这个计划是一个更好的计划。
重点是，当Postgres觉得索引会产生更快的结果时，它会使用索引，但使用索引之前的阈值比你预期的要高一些。
一切顺利，
Phil
您在两个表上有一个主键，将用于joins。如果您真的想看到查询速度减慢，请删除主键。
发生了什么？好吧，我的猜测是，无论有没有二级索引，执行计划都是一样的。你需要看看计划本身。
与大多数其他数据库不同，Postgres并没有从覆盖索引中获益，因为锁定信息只存储在数据页中。因此，总是需要访问数据页。