PostgreSQL查询性能分析和优化

123次阅读

共计 5848 个字符，预计需要花费 15 分钟才能阅读完成。

介绍

PostgreSQL 是一个支持几乎所有 SQL 构造的开源对象关系 DBMS。在本教程中，我们将尝试提供优化查询的工具。当您需要提高数据库的性能时，请考虑如何使用受影响的表。有时，表主要用于插入，在其他情况下仅用于选择数据，但通常您需要提前知道偏差。

查询规划

当 PostgreSQL 收到查询时，首先要解析它，并且可能会重写它，通常在考虑性能时这不是问题。接下来的事情是生成一个执行计划，实际上它比这更多，但是让事情变得简单。

查询分析

要了解 PostgreSQL 如何生成计划，我们使用 EXPLAIN 查询，我们也使用 ANALYZE。以下是 Postgresql 的描述。

EXPLAIN：显示 PostgreSQL 计划程序为提供的语句生成的执行计划。
ANALYZE：收集有关数据库中表的内容的统计信息。

准备测试环境

我们不建议在生产服务器上执行 EXPLAIN ANALYZE 查询。为此，我们需要使用完全相同的数据库模式来设置一个测试 postgres。使用此模式，我们建议使用受影响的表的随机行，并检查每列的数据分布。拥有每个列值的随机分布将允许我们用更多的实际数据填充测试表。

现在我们有测试环境，我们需要配置 PostgreSQL 来记录慢查询。要执行此操作搜索 log_min_duration_statement 键并将其设置为正值，如 300 毫秒。

log_min_duration_statement = 300

现在开始使用你的应用程序来知道哪些查询速度很慢。我们将在后续步骤中分析这些查询。

这里有一个非常慢的查询示例：

2017-07-15 15:06:10 ART LOG:  duration: 300898.426 ms  statement: SELECT example_table.id AS example_table_id, example_table.identification AS example_table_identification, example_table.column1 AS example_table_column1, example_table.column2 AS example_table_column2, example_table.column4 AS example_table_column4, example_table.column3 AS example_table_column3, example_table.column5 AS example_table_column5, example_table."column6" AS "example_table_column6", example_table.psa AS example_table_psa, example_table.fingerprint AS example_table_fingerprint, example_table.duplicate AS example_table_duplicate
        FROM example_table
        WHERE example_table.column1 <= 444.53736 AND example_table.column1 >= 414.53736 AND example_table.column2 <= 4.0 AND example_table.column2 >= 4.0 AND example_table.column4 <= 1 AND example_table.column4 >= 1 AND example_table.column3 <= 6 AND example_table.column3 >= 6 AND example_table."column6" <= 4.7748 AND example_table."column6" >= 2.7748 AND example_table.column5 = 1

PostgreSQL 配置调整

如果您使用 postgres 默认配置，那么在开始出现性能问题时，您应该查看该文件，因为配置是针对平均用例。如果你有一个非常大的服务器机会大，你需要更改你的配置。。

Vacuum 检查测试环境

在测试环境中这样做是没有用的，因为 Vacuum 会优化内部数据库结构！Vacuum 检查你的生产环境。

Vacuum 是 Postgres 查询删除删除或废弃的元组。

首先检查 lastime，Vacuum 进行查询：

SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_all_tables
WHERE schemaname = 'public';

我的情况时，列 last_vacuum 显示它是在几个月前完成的。我的用例是读取，因为当表已经被填充，没有更多的插入或更新完成。以防万一，我做了 Vacuum：

VACUUM ANALYZE table_name;

您可以配置自动 Vacuum，但请记住，在执行 VACUUM 查询时，Vacuum 过程可能会使数据库变慢。在我们的示例中，我们使用 10000 行作为阈值，这意味着在 10000 次插入，更新或删除后，该表是自动 Vacuum。

ALTER TABLE table_name
SET (autovacuum_vacuum_scale_factor = 0.0);

ALTER TABLE table_name
SET (autovacuum_vacuum_threshold = 10000);

ALTER TABLE table_name
SET (autovacuum_analyze_scale_factor = 0.0);

ALTER TABLE table_name
SET (autovacuum_vacuum_threshold = 10000);

我们建议您使用一个积极的自动 Vacuum 设置来做 VACUUM FULL。

查询分析示例

我们的表格模式如下：

                                      Table "public.example_table"
         Column         |       Type        |                       Modifiers
------------------------+-------------------+-------------------------------------------------------
 id                     | integer           | not null default nextval('example_table_id_seq'::regclass)
 identification         | character varying |
 column1                | double precision  |
 column2                | integer           |
 column4                | integer           |
 column3                | integer           |
 column5                | integer           |
 column6                | double precision  |
 fingerprint            | character varying |
 duplicate              | boolean           |
Indexes:
    "example_table_pkey" PRIMARY KEY, btree (id)
    "example_table_identification_key" UNIQUE CONSTRAINT, btree (identification)
    "example_table_identification_idx" btree (identification)
    "multi_index" btree (column1, column2, column4, column3, "column6", column5)
Referenced by:
    TABLE "history" CONSTRAINT "history_id_fkey" FOREIGN KEY (decoy_id) REFERENCES example_table(id)
    TABLE "result" CONSTRAINT "result_id_fkey" FOREIGN KEY (decoy_id) REFERENCES example_table(id)

在我们的日志中使用慢查询，我们将使用 explain analysis 来诊断可能出现的问题。我们的表格使用相同的生产环境数据分布填充了 100 Millon 行。

EXPLAIN ANALYZE SELECT example_table.id AS example_table_id, example_table.identification AS example_table_identification, example_table.column1 AS example_table_column1, example_table.column2 AS example_table_column2, example_table.column4 AS example_table_column4, example_table.column3 AS example_table_column3, example_table.column5 AS example_table_column5, example_table."column6" AS "example_table_column6", example_table.psa AS example_table_psa, example_table.fingerprint AS example_table_fingerprint, example_table.duplicate AS example_table_duplicate

以下是说明分析结果：

Index Scan using multi_index on example_table  (cost=0.57..664243.33 rows=98 width=212) (actual time=0.943..387378.003 rows=5685 loops=1)
   Index Cond: ((column1 <= 284.3018::double precision) AND (column1 >= 254.3018::double precision) AND (column4 <= 3) AND (column4 >= 3) AND (column3 <= 5) AND (column3 >= 5) AND ("column6" <= 2.4414::double precision) AND ("column6" >= 0.4414::double precision) AND (column5 = 0))
   Filter: (((column2)::numeric <= 5.0) AND ((column2)::numeric >= 5.0))
   Rows Removed by Filter: 27429
 Total runtime: 387403.832 ms
(5 rows)

对表执行计数

SELECT count(\*) from example_table;

正如我们可以看到，postgres 正在使用我们期望的 multi_index。但执行时间是完全不可接受的。由于我们有一个索引扫描，我们还执行一个 VACUUM FULL。由于我们在 column5 和 column3 上使用相等性，所以我们尝试在这些列中添加部分索引。

特别是 column5 将我们的表分成两部分，但 column3 并非如此。我们通常执行其他值的查询，如 2 和 1。

CREATE INDEX column3_and_column5_idx ON example_table ON example_table where column5=0 and column3=1 or column3=2 or column3=5; CREATE INDEX column3_and_column5_idx ON example_table ON example_table where column5=1 and column3=1 or column3=2 or column3=5;

现在这是输出的解释

Index Scan using multi_index on example_table (cost=0.56..92997.23 rows=25 width=211) (actual time=1.023..231.494 rows=14626 loops=1)
   Index Cond: ((column1 <= 284.3018::double precision) AND (column1 >= 254.3018::double precision) AND (column4 <= 3) AND (column4 >= 3) AND (column3 <= 5) AND (column3 >= 5) AND ("column6" <= 2.4414::double precision) AND ("column6" >= 0.4414::double precision) AND (column5 = 0))
   Filter: (((column2)::numeric <= 5.0) AND ((column2)::numeric >= 5.0))
   Rows Removed by Filter: 62943
 Total runtime: 232.013 ms

Ubuntu 16.04 下安装 PostgreSQL 和 phpPgAdmin http://www.linuxidc.com/Linux/2016-08/134260.htm

Linux 下 RPM 包方式安装 PostgreSQL http://www.linuxidc.com/Linux/2016-03/128906.htm

Linux 下安装 PostgreSQL http://www.linuxidc.com/Linux/2016-12/138765.htm

Linux 下 PostgreSQL 安装部署指南 http://www.linuxidc.com/Linux/2016-11/137603.htm

Linux 下安装 PostgreSQL 并设置基��参数 http://www.linuxidc.com/Linux/2016-11/137324.htm

Fedota 24 将数据库升级到 PostgreSQL 9.5 http://www.linuxidc.com/Linux/2016-11/137374.htm

CentOS 6.5 下 PostgreSQL 服务部署 http://www.linuxidc.com/Linux/2017-01/139144.htm

MongoDB 的详细介绍 ：请点这里
MongoDB 的下载地址 ：请点这里

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-07/145768.htm

正文完

星哥说事-微信公众号