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, 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.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”

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

---

结论

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