重新聚簇表
最近更新时间: 2025-02-18 16:02:00
重新聚簇表可以减少扫描走某个索引值扫描表的页数。
psql -h 172.16.0.61 -p 11002 -d postgres -U tbase -c "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)
postgres=# CLUSTER t1 USING t1_f2_idx;
CLUSTER
postgres=#
dn001 --- psql -h 172.16.0.61 -p 11002 -d postgres -U tbase -c "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)聚簇前要扫描的block数为5869，聚簇后为116，性能提高2倍。