PostgreSQL查询性能分析和优化(2)

在我们的日志中使用慢查询，我们将使用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 

Linux下RPM包方式安装PostgreSQL 

Linux下安装PostgreSQL 

Linux下PostgreSQL安装部署指南 

Linux下安装 PostgreSQL 并设置基��参数 

Fedota 24 将数据库升级到 PostgreSQL 9.5 

CentOS 6.5下PostgreSQL服务部署 

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