AWS Builder Center is the official home for builders on AWS. Share and read what others are working on, follow people who inspire you, explore training and workshops, and find tools to support what you're building.
如何记录 Amazon RDS PostgreSQL 或 Aurora PostgreSQL 查询的执行计划以调优查询性能？
我想知道我的查询正在使用哪些执行计划，以便我可以相应地对其进行调整。如何为 Amazon Relational Database Service (Amazon RDS) PostgreSQL 数据库实例或 Amazon Aurora PostgreSQL 数据库集群记录这些查询的执行计划？
解决方法
您可以使用 auto_explain 模块来记录查询的执行计划。
1. 将 auto_explain 添加到 shared_preload_Library 参数中。
2. 重启数据库实例或集群。
3. 要启用 auto_explain，必须将 auto_explain.log_min_duration 参数更改为 -1 以外的值。此值是以毫秒为单位的最短语句执行时间，如果语句被传递并且语句运行，则会记录其执行计划。如果将参数设置为 0，则会记录所有计划。
**重要事项：**将 auto_explain.log_min_duration 参数设置为 0 会导致性能下降和大量存储空间消耗。这可能会导致实例出现问题。
4. 在数据库上运行查询。
5. 查看或下载 PostgreSQL 日志文件，包括执行计划。以下示例执行计划具有执行时间（4673.182 毫秒）和带查询文本的执行计划：
2020-01-02 03:08:44 UTC:27.0.3.156(21356):postgres@postgres:[2040]:LOG: duration: 4673.182 ms plan: Query Text: SELECT COUNT(*) FROM hoge ORDER BY 1; Sort (cost=215575.00..215575.00 rows=1 width=8) Sort Key: (count(*)) -> Aggregate (cost=215574.98..215574.99 rows=1 width=8) -> Seq Scan on hoge (cost=0.00..190336.18 rows=10095518 width=0)
使用 auto_explain 参数来调优查询性能
除了启用 pg_autoexplain 之外，auto_explain.log_min_duration 参数还允许您使用其他有用的参数。您可以使用以下 auto_explain 参数（您可以在不重新启动的情况下进行更改）来调优查询性能。有关更多信息，请参阅每个参数的 PostgreSQL 文档 。
auto_explain.log_analyze
在记录执行计划时，auto_explain.log_analyze 参数会打印 EXPLAIN ANALYZE 输出（而不是 EXPLA IN 输出）。此参数可能会因开销而降低查询性能，并且默认值处于关闭状态。有关更多信息，请参阅 Postgres 文档中的 auto_explain.log_analyze。
请参阅以下示例：
2020-02-01 07:42:09 UTC:27.0.3.145(29943):master@postgres:[789]:LOG: duration: 18.658 ms plan: Query Text: SELECT COUNT(*) FROM t1 INNER JOIN t2 USING(id); Aggregate (cost=577.45..577.46 rows=1 width=8) (actual time=18.641..18.641 rows=1 loops=1) -> Hash Join (cost=269.98..552.45 rows=9999 width=0) (actual time=8.108..16.576 rows=9999 loops=1) Hash Cond: (t1.id = t2.id) -> Seq Scan on t1 (cost=0.00..144.99 rows=9999 width=4) (actual time=0.839..4.151 rows=9999 loops=1) -> Hash (cost=144.99..144.99 rows=9999 width=4) (actual time=7.186..7.186 rows=9999 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 480kB -> Seq Scan on t2 (cost=0.00..144.99 rows=9999 width=4) (actual time=0.667..4.289 rows=9999 loops=1)
auto_explain.log_buffers
使用 auto_explain.log_buffers 参数来确定在记录执行计划时是否打印缓冲区使用情况统计信息。使用此参数与将缓冲区选项与解释一起使用具有相同的效果。 一定要使用 auto_explain.log_buffers 启用 auto_explain.log_analyze。默认情况下，此参数处于关闭状态，仅当您是超级用户时才能更改此参数。有关更多信息，请参阅 PostgreSQL 文档中的 auto_explain.log_buffers。
请参见以下示例输出：
2020-02-01 08:02:02 UTC:27.0.3.145(27477):master@postgres:[15514]:LOG: duration: 14.875 ms plan: Query Text: SELECT COUNT(*) FROM t1 INNER JOIN t2 USING(id); Aggregate (cost=577.45..577.46 rows=1 width=8) (actual time=14.861..14.861 rows=1 loops=1) Buffers: shared hit=93 -> Hash Join (cost=269.98..552.45 rows=9999 width=0) (actual time=5.293..12.768 rows=9999 loops=1) Hash Cond: (t1.id = t2.id) Buffers: shared hit=93 -> Seq Scan on t1 (cost=0.00..144.99 rows=9999 width=4) (actual time=0.007..2.385 rows=9999 loops=1) Buffers: shared hit=45 -> Hash (cost=144.99..144.99 rows=9999 width=4) (actual time=5.250..5.250 rows=9999 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 480kB Buffers: shared hit=45 -> Seq Scan on t2 (cost=0.00..144.99 rows=9999 width=4) (actual time=0.009..2.348 rows=9999 loops=1) Buffers: shared hit=45
auto_explain.log_nested_statements
使用 auto_explain.log_nested_statements 参数可考虑嵌套语句进行日志记录。默认情况下，将记录顶级查询计划，除非您启用此参数。默认值处于关闭状态。有关更多信息，请参阅 PostgreSQL 文档中的 auto_explain.nested_statements。
请参阅以下示例：
CREATE OR REPLACE FUNCTION count_tables() RETURNS integer AS $$ DECLARE i integer; j integer; BEGIN SELECT COUNT(*) INTO i FROM t1; SELECT COUNT(*) INTO j FROM t2; RETURN i + j; END; $$ LANGUAGE plpgsql;
请参见以下示例输出：
2020-02-01 08:15:25 UTC:27.0.3.145(27477):master@postgres:[15514]:LOG: duration: 0.651 ms plan: Query Text: SELECT COUNT(*) FROM t1 Aggregate (cost=169.99..170.00 rows=1 width=8) -> Seq Scan on t1 (cost=0.00..144.99 rows=9999 width=0) 2020-02-01 08:15:25 UTC:27.0.3.145(27477):master@postgres:[15514]:CONTEXT: SQL statement "SELECT COUNT(*) FROM t1" PL/pgSQL function count_tables() line 6 at SQL statement 2020-02-01 08:15:25 UTC:27.0.3.145(27477):master@postgres:[15514]:LOG: duration: 0.637 ms plan: Query Text: SELECT COUNT(*) FROM t2 Aggregate (cost=169.99..170.00 rows=1 width=8) -> Seq Scan on t2 (cost=0.00..144.99 rows=9999 width=0) 2020-02-01 08:15:25 UTC:27.0.3.145(27477):master@postgres:[15514]:CONTEXT: SQL statement "SELECT COUNT(*) FROM t2" PL/pgSQL function count_tables() line 7 at SQL statement 2020-02-01 08:15:25 UTC:27.0.3.145(27477):master@postgres:[15514]:LOG: duration: 1.435 ms plan: Query Text: SELECT count_tables(); Result (cost=0.00..0.26 rows=1 width=4)
auto_explain.log_timing
使用 auto_explain.log_timing 参数来控制在记录执行计划时是否打印每个节点的时间安排信息。使用此参数的工作方式类似于将时间安排与解释一起使用。禁用 auto_explain.log_timing 可以减轻重复读取系统时钟的开销。默认值处于打开状态。有关更多信息，请参阅 PostgreSQL 文档中的 auto_explain.log_timing。
auto_explain.log_triggers
在记录执行计划时，使用 auto_explain.log_triggers 参数包括触发器执行统计信息。使用 auto_explain.log_triggers 时，还必须启用 auto_explain.log_analyze 默认值为关闭状态。
有关更多信息，请参阅 PostgreSQL 文档中的 auto_explain.log_triggers。
请参阅以下示例：
CREATE FUNCTION emp_log_trigger_func() RETURNS trigger AS $emp_stamp$ DECLARE count integer; BEGIN SELECT COUNT(*) INTO count FROM emp WHERE empname = NEW.empname; INSERT INTO emp_log(created_at, operation, user_name) VALUES(current_timestamp, TG_OP, user); RETURN NEW; END; $emp_stamp$ LANGUAGE plpgsql; CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp FOR EACH ROW EXECUTE PROCEDURE emp_log_trigger_func();
请参见以下示例输出：
2020-02-01 08:57:44 UTC:27.0.3.145(27477):master@postgres:[15514]:LOG: duration: 1.463 ms plan: Query Text: INSERT INTO emp_log(created_at, operation, user_name) VALUES(current_timestamp, TG_OP, user) Insert on emp_log (cost=0.00..0.03 rows=1 width=168) (actual time=1.461..1.461 rows=0 loops=1) -> Result (cost=0.00..0.03 rows=1 width=168) (actual time=0.009..0.010 rows=1 loops=1) 2020-02-01 08:57:44 UTC:27.0.3.145(27477):master@postgres:[15514]:CONTEXT: SQL statement "INSERT INTO emp_log(created_at, operation, user_name) VALUES(current_timestamp, TG_OP, user)" PL/pgSQL function emp_log_trigger_func() line 3 at SQL statement 2020-02-01 08:57:44 UTC:27.0.3.145(27477):master@postgres:[15514]:LOG: duration: 1.602 ms plan: Query Text: INSERT INTO emp VALUES('name', 100, current_timestamp, 'hoge'); Insert on emp (cost=0.00..0.01 rows=1 width=76) (actual time=1.600..1.600 rows=0 loops=1) -> Result (cost=0.00..0.01 rows=1 width=76) (actual time=0.006..0.006 rows=1 loops=1) Trigger emp_stamp: time=1.584 calls=1
auto_explain.log_verbose
使用 auto_explain.log_verbose 参数来确定在记录执行计划时是否打印详细信息。使用此参数的工作方式类似于将详细信息选项与解释一起使用。默认值处于关闭状态。有关更多信息，请参阅 PostgreSQL 文档中的 auto_explain.log_verbose。
请参阅以下示例：
2020-02-01 09:03:20 UTC:27.0.3.145(27477):master@postgres:[15514]:LOG: duration: 3.492 ms plan: Query Text: SELECT COUNT(*) FROM t1 INNER JOIN t2 USING(id); Aggregate (cost=577.45..577.46 rows=1 width=8) Output: count(*) -> Hash Join (cost=269.98..552.45 rows=9999 width=0) Hash Cond: (t1.id = t2.id) -> Seq Scan on public.t1 (cost=0.00..144.99 rows=9999 width=4) Output: t1.id -> Hash (cost=144.99..144.99 rows=9999 width=4) Output: t2.id Buckets: 16384 Batches: 1 Memory Usage: 480kB -> Seq Scan on public.t2 (cost=0.00..144.99 rows=9999 width=4) Output: t2.id
或者，如果您想查看更少调优查询的解释计划，则可以在这些查询上运行 EXPLAIN ANALYZE。这免去了日志记录的开销，并逐步显示查询的计划以及每个步骤的执行时间。如果手动调用 EXPLAIN ANALYZE 语句，则可以识别事件，例如生成临时文件的步骤、哪个步骤正在执行 Seq 扫描等。
postgres=> EXPLAIN ANALYZE SELECT * FROM hoge ORDER BY 1; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- Sort (cost=7343904.60..7444904.60 rows=40400000 width=4) (actual time=37795.033..45133.431 rows=40400000 loops=1) Sort Key: id Sort Method: external merge Disk: 553512kB -> Seq Scan on hoge (cost=0.00..582762.00 rows=40400000 width=4) (actual time=0.579..8184.498 rows=40400000 loops=1) Planning Time: 0.549 ms Execution Time: 47161.516 ms (6 rows)
相关信息
auto_explain 的 PostgreSQL 文档
用于错误报告和日志记录的 PostgreSQL 文档
关于使用 EXPLAIN 的 PostgreSQL 文档
相关内容
- AWS 官方已更新 7 个月前
