PostgreSQL查询性能分析和优化
介绍
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 = 1PostgreSQL配置调整
如果您使用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;
现在这是输出的解释
MongoDB 的详细介绍：请点这里
MongoDB 的下载地址：请点这里