在 PostgreSQL 中，连接类型的选择由查询优化器自动决定，但你可以通过调整配置或使用 EXPLAIN 来控制连接策略。哈希连接（Hash Join）通常在处理大数据集时表现较好，尤其是当连接列上没有索引时，而合并连接（Merge Join）适用于已经排序的或可以通过索引访问的数据。

1. 哈希连接（Hash Join）
   哈希连接在连接表时会构建一个哈希表，适合没有索引的情况下连接大表。它的性能优势在于处理大型数据集时，能够通过将数据分块来减少比较次数。

示例：使用哈希连接
假设你有两个表 orders 和 customers，你要根据 customer_id 列进行连接。这个例子展示了如何使用哈希连接：

EXPLAIN ANALYZE
SELECT
    o.order_id,
    o.order_date,
    c.customer_name
FROM
    orders o
JOIN
    customers c ON o.customer_id = c.customer_id;

默认情况下，PostgreSQL 会根据查询的执行计划自动选择最合适的连接方式。你可以通过禁用某些连接类型来强制使用哈希连接。例如，禁用嵌套循环连接（Nested Loop Join）：

SET enable_nestloop = off;

EXPLAIN ANALYZE
SELECT
    o.order_id,
    o.order_date,
    c.customer_name
FROM
    orders o
JOIN
    customers c ON o.customer_id = c.customer_id;

这样，查询将优先使用哈希连接（如果条件合适）。你可以通过 EXPLAIN ANALYZE 查看查询计划。

2. 合并连接（Merge Join）
   合并连接适用于已经排序的数据或者可以通过索引获取的数据。它通过将两个数据集按连接条件进行排序，然后合并这些有序的数据。

示例：使用合并连接
假设你有两个表 orders 和 customers，你要根据 customer_id 列进行连接，且这两个表已按 customer_id 排序或有索引：

EXPLAIN ANALYZE
SELECT
    o.order_id,
    o.order_date,
    c.customer_name
FROM
    orders o
JOIN
    customers c ON o.customer_id = c.customer_id;

如果 orders 和 customers 表的 customer_id 列有索引，或者表已经按 customer_id 排序，PostgreSQL 会自动选择合并连接。如果你希望强制使用合并连接，可以通过以下方式进行配置：

SET enable_hashjoin = off;
SET enable_nestloop = off;

EXPLAIN ANALYZE
SELECT
    o.order_id,
    o.order_date,
    c.customer_name
FROM
    orders o
JOIN
    customers c ON o.customer_id = c.customer_id;

禁用哈希连接和嵌套循环连接后，查询会优先使用合并连接。

3. 选择连接类型
   要选择合适的连接方式，主要依赖以下几个因素：

哈希连接：当连接列没有索引或表数据量很大时，哈希连接通常更有效，因为它通过哈希表处理连接。
合并连接：当连接列已经有索引或者表数据已排序时，合并连接更高效，因为它通过排序和归并操作更快速地完成连接。
嵌套循环连接：适用于小表连接或当表之间有索引时，可以高效地逐行扫描。
4. 查询示例：如何强制使用哈希连接或合并连接
假设你要优化一个查询，强制使用哈希连接或合并连接，可以使用如下命令：

-- 禁用嵌套循环连接，强制使用哈希连接
SET enable_nestloop = off;

-- 禁用哈希连接，强制使用合并连接
SET enable_hashjoin = off;

-- 查询执行计划
EXPLAIN ANALYZE
SELECT
    o.order_id,
    o.order_date,
    c.customer_name
FROM
    orders o
JOIN
    customers c ON o.customer_id = c.customer_id;

总结
哈希连接：适用于没有索引的大数据集，尤其是连接条件没有排序的情况。
合并连接：适用于连接列已经有索引或数据已经排序的情况。
通过设置 SET enable_*，你可以强制 PostgreSQL 使用特定类型的连接策略，从而根据表的特性优化查询性能。

在执行计划中，PostgreSQL 对查询进行了优化并选择了特定的连接方式和操作步骤。下面是执行计划中的每个值的详细解释：

Sort  (cost=14.34..14.34 rows=1 width=1347) (actual time=0.114..0.114 rows=2 loops=1)
  Sort Key: sr.gmt_modified DESC
  Sort Method: quicksort  Memory: 25kB

Sort：这表示查询需要对结果进行排序，排序的关键字是 sr.gmt_modified DESC（按 gmt_modified 字段降序排序）。
cost=14.34…14.34：这是排序的估算成本，表示排序操作从 cost=14.34 开始，并在完成后仍然是 cost=14.34。这里的成本单位是相对的估算值，表示查询的执行难度或资源消耗。
rows=1：表示这个步骤预计将返回 1 行数据，但实际返回了 2 行（actual rows=2）。
width=1347：表示每行数据的大小（单位是字节），即每行数据在内存中占用的空间大小。
actual time=0.114…0.114：表示排序操作的实际执行时间。从 0.114 毫秒开始，到 0.114 毫秒结束，表示该操作的持续时间。
loops=1：表示排序操作执行了 1 次。

连接操作

->  Nested Loop  (cost=5.81..14.33 rows=1 width=1347) (actual time=0.093..0.105 rows=2 loops=1)

Nested Loop：表示使用嵌套循环连接。PostgreSQL 在内部执行嵌套循环连接，即外部查询中的每一行都与内部查询的每一行进行比较。
cost=5.81…14.33：表示这个操作的估算成本。从 5.81 开始，到 14.33 结束。
rows=1：表示此操作预计将返回 1 行数据。
actual time=0.093…0.105：表示此操作的实际执行时间为从 0.093 毫秒开始，到 0.105 毫秒结束。
loops=1：表示该嵌套循环连接操作执行了 1 次。

Merge Join 连接操作

->  Merge Join  (cost=5.81..6.05 rows=1 width=1418) (actual time=0.081..0.086 rows=2 loops=1)

Merge Join：表示使用合并连接（Merge Join）。这个连接方式通常用于当连接的两张表已经按连接条件排序时，可以高效地逐行合并数据。
cost=5.81…6.05：估算成本，表示此连接从 5.81 开始，到 6.05 结束。
rows=1：预计返回 1 行数据。
actual time=0.081…0.086：实际执行时间为 0.081 毫秒到 0.086 毫秒，执行过程较短。
loops=1：该连接操作执行了 1 次。

Sort 操作

->  Sort  (cost=3.17..3.17 rows=1 width=1026) (actual time=0.061..0.061 rows=2 loops=1)
  Sort Key: sr.dept_id
  Sort Method: quicksort  Memory: 25kB

Sort：表示 PostgreSQL 需要对数据进行排序，排序的关键字是 sr.dept_id。
cost=3.17…3.17：估算成本，从 3.17 开始，到 3.17 结束。
rows=1：预计返回 1 行数据。
actual time=0.061…0.061：实际执行时间为 0.061 毫秒。
loops=1：表示该排序操作执行了 1 次。
Sort Key：排序的关键字是 sr.dept_id。
Sort Method: quicksort：排序方法为快速排序。
Memory: 25kB：表示排序操作使用了 25KB 的内存。

Merge Join 连接操作

->  Merge Join  (cost=3.13..3.16 rows=1 width=1026) (actual time=0.054..0.056 rows=2 loops=1)

Merge Join：再次使用合并连接。
cost=3.13…3.16：估算成本为从 3.13 到 3.16。
rows=1：预计返回 1 行数据。
actual time=0.054…0.056：实际执行时间为 0.054 毫秒到 0.056 毫秒。
loops=1：该连接操作执行了 1 次。

Merge Join 连接操作

->  Merge Join  (cost=2.06..2.09 rows=1 width=1018) (actual time=0.032..0.036 rows=2 loops=1)

Merge Join：使用合并连接。
cost=2.06…2.09：估算成本为 2.06 到 2.09。
rows=1：预计返回 1 行数据。
actual time=0.032…0.036：实际执行时间为 0.032 毫秒到 0.036 毫秒。
loops=1：该连接操作执行了 1 次。
Seq Scan 操作

->  Seq Scan on sys_user_role sr  (cost=0.00..1.03 rows=1 width=744) (actual time=0.014..0.015 rows=2 loops=1)

Seq Scan：表示顺序扫描 sys_user_role 表。
cost=0.00…1.03：估算成本从 0.00 到 1.03。
rows=1：预计扫描到 1 行数据。
actual time=0.014…0.015：实际扫描时间为 0.014 毫秒到 0.015 毫秒。
loops=1：表示此操作执行了 1 次。

Seq Scan 操作

->  Seq Scan on sys_unit su  (cost=0.00..1.01 rows=1 width=356) (actual time=0.002..0.003 rows=1 loops=1)

Seq Scan：对 sys_unit 表进行顺序扫描。
cost=0.00…1.01：估算成本从 0.00 到 1.01。
rows=1：预计扫描到 1 行数据。
actual time=0.002…0.003：实际扫描时间为 0.002 毫秒到 0.003 毫秒。
loops=1：表示此操作执行了 1 次。

Index Scan 操作

->  Index Scan using idx_p_id on sys_dic_city sdc  (cost=0.00..8.27 rows=1 width=43) (actual time=0.015..0.016 rows=2 loops=2)
  Index Cond: ((p_id)::text = (sr.p_id)::text)

Index Scan：表示对 sys_dic_city 表进行了索引扫描，使用了 idx_p_id 索引。
cost=0.00…8.27：估算成本从 0.00 到 8.27。
rows=1：预计扫描到 1 行数据。
actual time=0.015…0.016：实际扫描时间为 0.015 毫秒到 0.016 毫秒。
loops=2：该操作执行了 2 次。

总结
成本 (cost)：这是 PostgreSQL 为每个操作估算的执行成本，通常由 CPU 和 I/O 成本组成。
实际时间 (actual time)：表示实际执行操作所用的时间。
行数 (rows)：表示操作返回的行数。
内存 (Memory)：表示排序操作占用的内存量。
连接类型：各种连接操作（如嵌套循环连接、合并连接等）决定了查询如何处理数据。
这些数据和信息帮助数据库优化器选择最有效的查询执行计划。

减少子查询的一个重要目标是提高查询效率，避免不必要的重复计算和避免增加执行计划的复杂度。子查询（特别是非关联子查询）往往会导致查询性能下降，因为它们可能会多次执行相同的查询操作，或者在计算过程中浪费不必要的资源。

例子：通过减少子查询来优化查询
假设我们有一个查询，其中包含子查询，查询用户的角色信息并关联其他表：
原始查询：使用子查询

SELECT 
    u.user_id,
    u.user_name,
    (
        SELECT role_name 
        FROM sys_role 
        WHERE role_id = u.role_id
    ) AS role_name
FROM sys_user u
WHERE u.status = 'active';

这个查询中的子查询 (SELECT role_name FROM sys_role WHERE role_id = u.role_id) 在每一行用户数据上都会执行一次，可能导致不必要的重复查询，特别是在 sys_user 表中有大量数据时，查询效率会很低。

优化：通过 JOIN 替换子查询
为了减少每次查询时都执行子查询，我们可以将子查询替换为 JOIN 操作，避免重复查询 sys_role 表。

优化后的查询：使用 JOIN 替代子查询

SELECT 
    u.user_id,
    u.user_name,
    r.role_name
FROM sys_user u
JOIN sys_role r ON u.role_id = r.role_id
WHERE u.status = 'active';

优化后的解释
减少了子查询：子查询被 JOIN 操作替代，避免了在每一行数据上执行多次查询。
提高了性能：使用 JOIN 语句可以更高效地在两个表之间进行连接，而不需要执行子查询，这对于大数据集特别有帮助。
执行计划优化：数据库优化器可以为 JOIN 操作生成更优化的执行计划，比如使用合适的索引来加速连接操作。

进一步的优化
如果表中数据量较大，使用合适的索引可以进一步提高查询性能。例如：

为 sys_user 表的 role_id 列建立索引，可以加速与 sys_role 表的连接。
为 sys_role 表的 role_id 列建立索引，以优化查找 role_name 的速度。
结论
通过减少子查询，尤其是非关联子查询，通常可以显著提高查询性能。使用 JOIN 语句不仅减少了重复计算，还能够让数据库优化器更有效地利用索引和执行计划。

以下是一个使用子查询的原始案例，以及如何通过优化减少子查询的影响，提升查询性能。

原始查询：带有子查询
假设我们有两个表：

orders 表：存储订单信息（订单 ID、客户 ID、订单金额等）。
customers 表：存储客户信息（客户 ID、客户名称等）。
我们希望查询每个订单的客户名称和该客户的总订单金额。

原始写法：子查询

SELECT 
    o.order_id,
    o.amount AS order_amount,
    c.customer_name,
    (SELECT SUM(o2.amount) 
     FROM orders o2 
     WHERE o2.customer_id = o.customer_id) AS total_order_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

问题：
子查询 (SELECT SUM(o2.amount) FROM orders o2 WHERE o2.customer_id = o.customer_id) 会为每一行订单数据执行一次，导致性能问题。
当 orders 表非常大时，这种操作会变得非常耗时。

优化后的查询：使用聚合和 JOIN 替代子查询
我们可以通过预先计算每个客户的总订单金额，并将结果通过 JOIN 操作引入，从而避免每行数据重复执行子查询。

WITH customer_totals AS (
    SELECT 
        customer_id, 
        SUM(amount) AS total_order_amount
    FROM orders
    GROUP BY customer_id
)
SELECT 
    o.order_id,
    o.amount AS order_amount,
    c.customer_name,
    ct.total_order_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN customer_totals ct ON o.customer_id = ct.customer_id;

优化点说明
使用 WITH 子句：

在 customer_totals 中预先计算每个客户的总订单金额。
只需扫描 orders 表一次即可完成总金额的计算。
避免重复计算：

原始子查询每处理一行订单都会重复计算该客户的总金额，而优化后的查询只需计算一次。
JOIN 替代子查询：

用 JOIN 将预计算的结果合并到主查询中，减少查询复杂度。

优化后的执行计划优势
减少表扫描次数：
原始查询需要对 orders 表扫描多次（每行订单一次），而优化后的查询只扫描一次。
充分利用索引：
如果对 orders.customer_id 列建立了索引，可以更高效地完成聚合操作。
更快的响应时间：
对于大数据集，优化后的查询可以显著减少执行时间。

实际场景应用
这种优化适用于以下场景：

子查询的计算结果在同一查询中可以复用。
子查询涉及聚合操作或复杂计算，且表数据量较大。
查询的性能瓶颈在子查询的重复执行上。
通过这种优化，我们可以大幅提升查询的效率，使其更适合在高并发场景中使用。