重新聚簇表
更新时间 2025-02-05 09:36:58
最近更新时间: 2025-02-05 09:36:58
TeleDB支持按某个索引重新聚簇表，即按某个索引重新排序表数据，这样在使用到这个索引时，可以扫描更少的页，提高SQL效率。本文为您介绍该场景下的测试情况。
注意聚簇表需要重写表，可能会对业务有影响；聚簇完成后新写入的数据不会再按聚簇的索引排序，对于数据变更频繁的表并不适用。
以下是关于该场景的测试情况：
-
重新聚簇前SQL执行计划和耗时：
可以看到聚簇前要扫描的block数为5869（Buffers: shared hit=5869），耗时25ms。
teledb=# explain (analyze,buffers) select count(1) from t1 where f2=1;" QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=7208.99..7209.00 rows=1 width=8) (actual time=25.505..25.505 rows=1 loops=1) Buffers: shared hit=5869 -> Bitmap Heap Scan on t1 (cost=124.87..7185.62 rows=9348 width=0) (actual time=3.567..23.002 rows=10051 loops=1) Recheck Cond: (f2 = 1) Heap Blocks: exact=5838 Buffers: shared hit=5869 -> Bitmap Index Scan on t1_f2_idx (cost=0.00..122.53 rows=9348 width=0) (actual time=2.405..2.405 rows=10051 loops=1) Index Cond: (f2 = 1) Buffers: shared hit=31 Planning time: 0.626 ms Execution time: 25.659 ms (11 rows) -
按索引t1_f2_idx重新聚簇：
teledb=# CLUSTER t1 USING t1_f2_idx; CLUSTER -
重新聚簇后SQL执行计划和耗时：
可以看到聚簇后扫描的block数为116（Buffers: shared hit=116），耗时9.9ms。性能提升了2倍以上。
teledb=# explain (analyze,buffers) select count(1) from t1 where f2=1; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=7201.23..7201.24 rows=1 width=8) (actual time=9.808..9.808 rows=1 loops=1) Buffers: shared hit=116 -> Bitmap Heap Scan on t1 (cost=124.87..7177.86 rows=9348 width=0) (actual time=1.312..7.348 rows=10051 loops=1) Recheck Cond: (f2 = 1) Heap Blocks: exact=85 Buffers: shared hit=116 -> Bitmap Index Scan on t1_f2_idx (cost=0.00..122.53 rows=9348 width=0) (actual time=1.219..1.219 rows=10051 loops=1) Index Cond: (f2 = 1) Buffers: shared hit=31 Planning time: 0.696 ms Execution time: 9.969 ms (11 rows)
