简介
在Postgresql及GP集群上分别进行索引调优的测试，重点研究索引对排序查询的影响。
测试环境
数据库信息：
测试表信息：
- 表名：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. 有索引
创建索引：
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”
不再进行查询测试，因为与上述时间一致。
