GP索引调优测试--排序篇

概述简介 测试环境 PostgreSQL环境测试 无索引 有索引 有无索引的比较 GP环境测试 无索引 有索引 结论 简介 在PostgreSQL及GP集群上分别进行索引调优的测试，重点研究索引对排序查询的影响。 测试环境 数据库信息： PostgreSQL版本: 9.4 GP版本：4.3(基于PostgreSQL 8.2) 测试表信息： 表名：test 总行数：68w 总大小：170MB 测试语句： 简介 测试环境 PostgreSQL环境测试 无索引 有索引 有无索引的比较 GP环境测试 无索引 有索引 结论 简介

在Postgresql及GP集群上分别进行索引调优的测试，重点研究索引对排序查询的影响。

测试环境

数据库信息：

Postgresql版本: 9.4 GP版本：4.3(基于Postgresql 8.2)

测试表信息：

表名：test 总行数：68w 总大小：170MB

测试语句：

查看执行计划: explain analyze select * from test order by test_ID 执行查询: select * from test order by test_ID Postgresql环境测试 1. 无索引

查看执行计划：

“Sort (cost=230780.25..232494.46 rows=685684 wIDth=205) (actual time=3200.642..4079.336 rows=685684 loops=1)”
” Sort Key: test_ID”
” Sort Method: external merge disk: 156136kB”
” -> Seq Scan on test (cost=0.00..28379.84 rows=685684 wIDth=205) (actual time=0.005..128.166 rows=685684 loops=1)”
“Planning time: 0.116 ms”
“Execution time: 4152.203 ms”

从该上述执行计划可以得出：真实执行时间为4079ms，即4s。

在本机上执行查询，查询时间为2:20min，即140s。

2. 有索引

创建索引：

CREATE INDEX test_index ON test USING btree (test_ID);

这里的索引默认为升序排列，并且我们的查询语句中使用到了order by，故执行查询时会走该索引，如下

执行计划：

“Index Scan using test8 on test (cost=0.42..99475.90 rows=685684 wIDth=205) (actual time=0.045..558.244 rows=685684 loops=1)”
“Planning time: 0.449 ms”
“Execution time: 606.375 ms”

从该上述执行计划可以得出：真实执行时间为558.244ms，即0.56s。

在本机(192.168.80.188)上执行查询，查询时间为2:17min，即137s。

3. 有无索引的比较

使用索引后，查询时间从4s减少到了0.56s，即缩短为原来的1/8。

值得注意的是，因为执行计划中的查询时间没有考虑数据传输时间（在这里是从数据库主机传输到我的主机的时间），故大家看到的真实查询时间要长很多。比如创建索引后，执行计划中的查询时间为0.56s，但在我主机上进行查询却要花137s，也就意味着有136s的时间用于传输数据了。

同样的道理，若直接在Postgresql主机上进行查询，应该会快很多。比如，将上述sql在数据库主机上运行，查询时间仅有10s。

GP环境测试 1. 无索引

查看执行计划：

“Gather Motion 4:1 (slice1; segments: 4) (cost=337376.64..339090.53 rows=685556 wIDth=211)”
” Merge Key: test_ID”
” Rows out: 685684 rows at destination with 1303 ms to first row,2969 ms to end,start offset by 3.100 ms.”
” -> Sort (cost=337376.64..339090.53 rows=171389 wIDth=211)”
” Sort Key: test_ID”
” Rows out: Avg 171421.0 rows x 4 workers. Max 171423 rows (seg2) with 1004 ms to first row,1102 ms to end,start offset by 4268012 ms.”
” Executor memory: 79865K bytes avg,79865K bytes max (seg0).”
” Work_mem used: 79865K bytes avg,79865K bytes max (seg0). Workfile: (0 spilling,0 reused)”
” -> Seq Scan on test (cost=0.00..12289.56 rows=171389 wIDth=211)”
” Rows out: Avg 171421.0 rows x 4 workers. Max 171423 rows (seg2) with 0.437 ms to first row,69 ms to end,start offset by 4268013 ms.”
“Slice statistics:”
” (slice0) Executor memory: 335K bytes.”
” (slice1) Executor memory: 80055K bytes avg x 4 workers,80055K bytes max (seg0). Work_mem: 79865K bytes max.”
“Statement statistics:”
” Memory used: 128000K bytes”
“Optimizer status: legacy query optimizer”
“Total runtime: 3088.360 ms”

该计划显示查询所需时间约为3s，与Postgresql的查询时间差别不大。

在本机执行查询，时间为2:19min。

2. 有索引

创建索引：

CREATE INDEX test_index2 ON test USING btree (test_ID);

注意，因为当前的GP不支持“排序的索引”，故上述创建的索引并不能应用到涉及排序的查询中，查看查询计划进行验证，确实没有使用该索引，而是采用了顺序扫描，如下

“Gather Motion 4:1 (slice1; segments: 4) (cost=337376.64..339090.53 rows=685556 wIDth=211)”
” Merge Key: test_ID”
” Rows out: 685684 rows at destination with 1161 ms to first row,2784 ms to end,start offset by 1.896 ms.”
” -> Sort (cost=337376.64..339090.53 rows=171389 wIDth=211)”
” Sort Key: test_ID”
” Rows out: Avg 171421.0 rows x 4 workers. Max 171423 rows (seg2) with 1149 ms to first row,1254 ms to end,start offset by 4268008 ms.”
” Executor memory: 79865K bytes avg,0 reused)”
” -> Seq Scan on test (cost=0.00..12289.56 rows=171389 wIDth=211)”
” Rows out: Avg 171421.0 rows x 4 workers. Max 171423 rows (seg2) with 0.224 ms to first row,86 ms to end,start offset by 4268009 ms.”
“Slice statistics:”
” (slice0) Executor memory: 335K bytes.”
” (slice1) Executor memory: 80055K bytes avg x 4 workers,80055K bytes max (seg0). Work_mem: 79865K bytes max.”
“Statement statistics:”
” Memory used: 128000K bytes”
“Optimizer status: legacy query optimizer”
“Total runtime: 2896.060 ms”

不再进行查询测试，因为与上述时间一致。

结论 采用索引后，查询时间会大幅减少 确保有足够的网络带宽，否则查询时间的绝大部分都花在了数据传输上 总结

以上是内存溢出为你收集整理的GP索引调优测试--排序篇全部内容，希望文章能够帮你解决GP索引调优测试--排序篇所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。