PostgreSQL数据库查看执行计划，优化SQL语句

查看执行计划

结论：SQL查询的优化指标包括执行时间、查询计划、IO操作、索引使用情况、内存使用情况、锁定、并发和CPU使用情况。

建表语句

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    registration_date DATE NOT NULL
);

-- 插入一些示例数据
INSERT INTO users (username, email, registration_date)
VALUES
    ('alice', 'alice@example.com', '2023-01-15'),
    ('bob', 'bob@example.com', '2022-11-20'),
    ('charlie', 'charlie@example.com', '2023-03-05');

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    user_id INT REFERENCES users(user_id)
);

-- 插入一些示例数据
INSERT INTO orders (order_date, total_amount, user_id)
VALUES
    ('2023-01-20', 150.00, 1),  -- 为用户 Alice 创建一个订单
    ('2022-12-05', 200.50, 2),  -- 为用户 Bob 创建一个订单
    ('2023-03-10', 75.25, 3),   -- 为用户 Charlie 创建一个订单
    ('2023-02-15', 300.00, 1);  -- 再次为用户 Alice 创建一个订单

走全表扫描：

可以解释SQL语句如何执行，不会实际去执行查询，会返回查询计划

这会返回一个详细的查询计划，包括表的访问方式（如顺序扫描或索引扫描）、连接方法（如嵌套循环连接或哈希连接）、是否排序、使用的索引等信息。

postgres=# explain select * from users,orders where users.user_id=orders.user_id;
                              QUERY PLAN                              
----------------------------------------------------------------------
 Hash Join  (cost=14.72..43.11 rows=1450 width=372)
   Hash Cond: (orders.user_id = users.user_id)
   ->  Seq Scan on orders  (cost=0.00..24.50 rows=1450 width=28)
   ->  Hash  (cost=12.10..12.10 rows=210 width=344)
         ->  Seq Scan on users  (cost=0.00..12.10 rows=210 width=344)
(5 rows)

postgres=# 

返回查询计划，执行查询，显示执行统计信息，包括实际执行时间和行数等

postgres=# explain analyze select * from users,orders where users.user_id=orders.user_id;
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=14.72..43.11 rows=1450 width=372) (actual time=0.020..0.023 rows=4 loops=1)
   Hash Cond: (orders.user_id = users.user_id)
   ->  Seq Scan on orders  (cost=0.00..24.50 rows=1450 width=28) (actual time=0.007..0.008 rows=4 loops=1)
   ->  Hash  (cost=12.10..12.10 rows=210 width=344) (actual time=0.006..0.006 rows=3 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on users  (cost=0.00..12.10 rows=210 width=344) (actual time=0.002..0.002 rows=3 loops=1)
 Planning Time: 0.094 ms
 Execution Time: 0.041 ms
(8 rows)

postgres=# 

这是一个 PostgreSQL 数据库的执行计划（EXPLAIN ANALYZE）的输出。让我们逐步解析这个执行计划的各个部分：

1. Hash Join:

   • Hash Join 表示使用哈希连接算法来连接两个表。在这里，它指示数据库使用哈希连接来将 orders 表和 users 表连接起来。

2. Cost:

   • cost=14.72..43.11 表示数据库估算执行这个查询的成本为 14.72 到 43.11 单位。这个成本估算依赖于查询优化器的统计信息和数据库配置。

3. Rows:

   • rows=1450 表示查询优化器估算这个计划生成的行数为 1450 行。

4. Width:

   • width=372 表示查询的输出行的宽度为 372 字节。

5. Actual Time:

   • actual time=0.020..0.023 表示实际执行时间，即实际查询所花费的时间。在这个例子中，整个查询执行时间为 0.020 到 0.023 毫秒。

6. Actual Rows:

   • rows=4 loops=1 表示实际返回的行数为 4 行，并且这个计划只执行了 1 次循环。

7. Plans:

   • Seq Scan on orders: 这段说明了执行计划的第一部分。它表示对 orders 表进行顺序扫描（Sequential Scan）。实际运行时间为 0.007 到 0.008 毫秒，返回了 4 行数据。

   • Hash: 这段说明了执行计划的第二部分。它表示创建了一个哈希表来处理 users 表。实际运行时间为 0.006 毫秒，返回了 3 行数据。

   • Seq Scan on users: 这段说明了执行计划的第三部分。它表示对 users 表进行顺序扫描。实际运行时间为 0.002 毫秒，返回了 3 行数据。

8. Planning Time:

   • Planning Time: 0.094 ms 表示优化器生成这个执行计划所花费的时间为 0.094 毫秒。

9. Execution Time:

   • Execution Time: 0.041 ms 表示整个查询的实际执行时间为 0.041 毫秒。

10. Buckets:

    • Buckets: 1024 表示哈希表使用了 1024 个桶（buckets）。在哈希连接中，数据库会将匹配条件相同的行放入同一个桶中，这样可以在连接时更快地找到匹配的行。桶的数量通常由数据库的配置和查询优化器根据统计信息自动决定，目的是优化哈希连接的效率。

11. Batches:

    • Batches: 1 表示哈希表构建过程中只使用了 1 个批次（batch）。在某些情况下，数据库可能会将哈希表的构建分成多个批次来处理大量数据，以便更好地利用内存和处理器资源。但在这里，只使用了一个批次，可能由于数据量较小或者内存足够。

12. Memory Usage:

    • Memory Usage: 9kB 表示在构建哈希表时使用了约 9KB 的内存。哈希表是在内存中构建的数据结构，用于加快数据查找和连接的速度。这个值是数据库实际分配给哈希表构建过程的内存量，它旨在最大化性能并避免过多的内存使用。

综上所述，这个执行计划描述了如何执行一个哈希连接（Hash Join）来联合 orders 表和 users 表，以及每个步骤的具体执行时间和返回行数。

这两个参数是关于哈希表构建过程中使用的内部优化细节，通常在执行计划的哈希连接步骤中会显示：

走索引扫描：

postgres=# explain analyze select * from users,orders where users.user_id=orders.user_id and users.user_id=3;
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.14..30.56 rows=7 width=372) (actual time=0.021..0.023 rows=1 loops=1)
   ->  Index Scan using users_pkey on users  (cost=0.14..2.36 rows=1 width=344) (actual time=0.013..0.014 rows=1 loops=1)
         Index Cond: (user_id = 3)
   ->  Seq Scan on orders  (cost=0.00..28.12 rows=7 width=28) (actual time=0.006..0.006 rows=1 loops=1)
         Filter: (user_id = 3)
         Rows Removed by Filter: 3
 Planning Time: 0.083 ms
 Execution Time: 0.042 ms
(8 rows)

postgres=# 

这段执行计划描述了一个包含嵌套循环连接（Nested Loop Join）的查询操作，让我们一步步解析每个部分的含义：

1. Nested Loop Join:

   • Nested Loop (cost=0.14..30.56 rows=7 width=372): 这表示使用了嵌套循环连接算法来执行查询。这种连接方式逐行地将第一个表（users）的匹配行与第二个表（orders）的所有行进行比较。

2. Actual Time and Actual Rows:

   • actual time=0.021..0.023 rows=1 loops=1: 实际执行时间为 0.021 到 0.023 毫秒，返回了 1 行数据。loops=1 表示嵌套循环连接只执行了一次循环。

3. Index Scan on users:

   • Index Scan using users_pkey on users (cost=0.14..2.36 rows=1 width=344): 这段说明了执行计划的第一部分，使用了 users_pkey 索引来对 users 表进行索引扫描。实际运行时间为 0.013 到 0.014 毫秒，返回了 1 行数据。条件是 user_id = 3，所以这里只返回了一个具有特定 user_id 的用户行。

4. Seq Scan on orders:

   • Seq Scan on orders (cost=0.00..28.12 rows=7 width=28): 这段说明了执行计划的第二部分，对 orders 表进行了顺序扫描。实际运行时间为 0.006 毫秒，返回了 1 行数据。条件是 user_id = 3，但由于没有使用索引，所以需要遍历整个表来找到匹配的行。

5. Filter:

   • Filter: (user_id = 3): 在 orders 表的顺序扫描中，使用了过滤条件 user_id = 3，以过滤出符合条件的行。在这里，总共有 7 行中的 6 行被过滤掉了，只剩下符合条件的 1 行。

6. Planning Time and Execution Time:

   • Planning Time: 0.083 ms: 优化器生成执行计划的时间为 0.083 毫秒。
   • Execution Time: 0.042 ms: 整个查询的实际执行时间为 0.042 毫秒。

• Index Scan using users_pkey on users: 这部分说明在 users 表上使用了 users_pkey 索引进行扫描。
• Index Cond: (user_id = 3): 这说明在 users 表的索引扫描中，只选择了那些 user_id 等于 3 的行。

综上所述，这个执行计划显示了如何执行一个查询，首先在 users 表中使用索引找到特定 user_id 的用户信息，然后在 orders 表中顺序扫描并过滤出符合相同 user_id 的订单信息。这种查询计划适用于较小的数据集和特定条件下的快速查询需求。

走位图扫描：

postgres=# explain analyze select * from users,orders where users.user_id=orders.user_id and users.user_id in (1,2);
                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=4.67..33.05 rows=14 width=372) (actual time=0.024..0.026 rows=3 loops=1)
   Hash Cond: (orders.user_id = users.user_id)
   ->  Seq Scan on orders  (cost=0.00..24.50 rows=1450 width=28) (actual time=0.006..0.007 rows=4 loops=1)
   ->  Hash  (cost=4.64..4.64 rows=2 width=344) (actual time=0.011..0.012 rows=2 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Bitmap Heap Scan on users  (cost=2.51..4.64 rows=2 width=344) (actual time=0.009..0.009 rows=2 loops=1)
               Recheck Cond: (user_id = ANY ('{1,2}'::integer[]))
               Heap Blocks: exact=1
               ->  Bitmap Index Scan on users_pkey  (cost=0.00..2.51 rows=2 width=0) (actual time=0.007..0.007 rows=2 loops=1)
                     Index Cond: (user_id = ANY ('{1,2}'::integer[]))
 Planning Time: 0.107 ms
 Execution Time: 0.048 ms
(12 rows)

这个执行计划涉及到一个查询，让我们逐步分析每个部分的含义：

1. Hash Join:

   • Hash Join (cost=4.67..33.05 rows=14 width=372) (actual time=0.024..0.026 rows=3 loops=1): 这表示查询使用了哈希连接（Hash Join）算法来将 users 表和 orders 表进行连接。哈希连接是一种联接大型数据集的高效方法，它利用哈希表来加速数据的匹配。

2. Hash Cond: (orders.user_id = users.user_id):

   • Hash Cond: (orders.user_id = users.user_id): 这说明在哈希连接中，连接条件是 orders 表和 users 表的 user_id 字段相等。

3. Seq Scan on orders:

   • Seq Scan on orders (cost=0.00..24.50 rows=1450 width=28) (actual time=0.006..0.007 rows=4 loops=1): 这部分表示对 orders 表进行了顺序扫描，实际返回了 4 行数据。这是因为 orders 表可能没有使用索引，而是扫描整个表来找到匹配条件的行。

4. Hash:

   • Hash (cost=4.64..4.64 rows=2 width=344) (actual time=0.011..0.012 rows=2 loops=1): 在这里，数据库使用哈希表来处理来自 users 表的数据，以便进行连接操作。实际上只有 2 行数据参与了这一步骤。

5. Bitmap Heap Scan on users:

   • Bitmap Heap Scan on users (cost=2.51..4.64 rows=2 width=344) (actual time=0.009..0.009 rows=2 loops=1): 这里执行了位图堆扫描（Bitmap Heap Scan）在 users 表上，以检索满足 user_id IN (1,2) 条件的行。实际上，只有 2 行数据符合条件。

6. Bitmap Index Scan on users_pkey:

   • Bitmap Index Scan on users_pkey (cost=0.00..2.51 rows=2 width=0) (actual time=0.007..0.007 rows=2 loops=1): 这一步是针对 users 表上 users_pkey 索引的位图索引扫描，用于快速定位满足 user_id IN (1,2) 的行。实际上也只返回了 2 行数据。

7. Planning Time and Execution Time:

   • Planning Time: 0.107 ms: 优化器生成执行计划的时间为 0.107 毫秒。
   • Execution Time: 0.048 ms: 整个查询的实际执行时间为 0.048 毫秒。
     这两个字段是 PostgreSQL 执行计划中的一部分，表示查询执行过程中的一些详细信息：

8. Recheck Cond: (user_id = ANY (‘{1,2}’::integer[])):

   • Recheck Cond 表示在执行索引扫描后，数据库重新检查条件以确保返回正确的结果。这种情况通常出现在使用索引扫描进行快速定位后，需要进一步验证条件的情况。
   • (user_id = ANY ('{1,2}'::integer[])) 指明了重新检查的条件，即 user_id 必须等于数组 {1, 2} 中的任何一个值。这种情况通常发生在使用索引扫描（如位图索引扫描）后，数据库需要确保没有漏掉任何符合条件的行。

9. Heap Blocks: exact=1:

   • Heap Blocks 表示在执行扫描或者重新检查时，从堆中读取的块数目。在这里，exact=1 指示只有一个堆块被读取。堆块是指数据库实际存储数据的物理块，通常是8KB大小，一个块可以包含多行数据。
   • 这个信息对于性能调优和优化查询效率非常重要，因为减少读取的堆块数可以降低IO开销，提高查询效率。

综上所述，这个执行计划描述了如何通过哈希连接将 users 表和 orders 表中的数据进行联接操作。查询首先对 users 表进行位图索引扫描，找到符合条件的行，然后使用哈希表将这些行与 orders 表中的数据匹配。整体查询性能表现良好，执行时间非常短。

位图堆扫描、位图索引和位图在数据库中都是重要的概念，下面我来简单解释一下：

1. 位图索引（Bitmap Index）：

   • 位图索引是一种数据库索引结构，用于加速对列（或多列）的查询。它适用于列中有相对较少不同值的情况，例如性别、状态、类别等。位图索引将每个可能的值都映射到一个位图（bitmap）中，其中每个位表示该值在数据表中是否存在。
   • 例子：
     • 假设有一个 gender 列，只包含 Male 和 Female 两种性别。位图索引会创建两个位图，一个用于 Male，一个用于 Female。如果某行的性别是 Male，那么在 Male 位图中对应位置的位就会被置为1。这样，查询性别为 Male 的所有行时，数据库可以快速通过位图找到这些行，而不需要扫描整个数据表。

2. 位图堆扫描（Bitmap Heap Scan）：

   • 位图堆扫描是一种数据库查询执行策略，结合了位图索引和堆（表数据的物理存储）的操作。它用于在使用位图索引快速定位了符合条件的行后，从实际的数据堆中读取这些行。
   • 例子：
     • 在一个销售订单数据库中，如果要查询某一天内所有大于等于100件的订单，可以使用一个位图索引找到所有满足数量条件的订单号，然后通过位图堆扫描从订单表中检索这些订单的详细信息。

3. 位图（Bitmap）：

   • 在数据库中，位图是一种数据结构，用二进制位表示某种条件或状态的存在与否。每个位都代表某一行或某一行的属性，通常用于快速的集合运算。
   • 例子：
     • 在数据库查询优化中，位图可以用于表示多个条件的并集或交集。例如，可以使用位图来表示属于不同用户组的用户，并执行快速的集合操作，如并集、交集或差集。

这些概念通常用于数据库系统的性能优化和查询执行计划的优化，能够显著提高复杂查询的效率和响应速度。

在执行 EXPLAIN 或 EXPLAIN ANALYZE 后，PostgreSQL 返回的结果通常包括以下几种关键信息：

**QUERY PLAN：**查询计划的主要步骤和顺序。
**Node Type：**每个计划节点的类型，如 Seq Scan（顺序扫描）、Index Scan（索引扫描）、Nested Loop（嵌套循环连接）等。
**Relation Name：**涉及的表或索引的名称。
**Filter：**应用于结果的过滤条件。
**Rows 和 Width：**估计的行数和每行的字节大小。
**Cost：**估计的执行成本，这是 PostgreSQL 用于优化查询计划的重要指标之一。
通过分析这些信息，可以了解 PostgreSQL 是如何执行查询的，识别潜在的性能瓶颈，并优化查询以提高性能。