背景
对于较大数据量的表，如果在索引字段上面有小结果集JOIN，用nestloop JOIN是比较好的方法。
但是nestloop带来的一个问题就是离散IO，这个是无法回避的问题，特别是硬件IO能力不行的情况下，性能会比较糟糕。
有什么优化方法呢？
PostgreSQL提供了一个命令，可以修改物理存储的顺序，减少离散IO就靠它了。
例子
创建两张表
postgres=# create unlogged table test01(id int primary key, info text);
CREATE TABLE
postgres=# create unlogged table test02(id int primary key, info text);
CREATE TABLE产生一些离散primary key数据
postgres=# insert into test01 select trunc(random()*10000000), md5(random()::text) from generate_series(1,10000000) on conflict on constraint test01_pkey do nothing;
INSERT 0 6322422
postgres=# insert into test02 select trunc(random()*10000000), md5(random()::text) from generate_series(1,10000000) on conflict on constraint test02_pkey do nothing;
INSERT 0 6320836分析表
postgres=# analyze test01;
postgres=# analyze test02;清除缓存，并重启
$ pg_ctl stop -m fast
# echo 3 > /proc/sys/vm/drop_caches
$ pg_ctl start第一次调用，耗费大量的离散IO，执行时间18.490毫秒（我这台机器是SSD，IOPS能力算好的，差的机器时间更长）
postgres=# explain (analyze,verbose,timing,costs,buffers) select t1.*,t2.* from test01 t1,test02 t2 where t1.id=t2.id and t1.id between 1 and 1000;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=19.25..7532.97 rows=623 width=74) (actual time=0.465..17.221 rows=402 loops=1)
Output: t1.id, t1.info, t2.id, t2.info
Buffers: shared hit=1929 read=1039 dirtied=188
-> Bitmap Heap Scan on public.test01 t1 (cost=18.82..2306.39 rows=623 width=37) (actual time=0.416..8.019 rows=640 loops=1)
Output: t1.id, t1.info
Recheck Cond: ((t1.id >= 1) AND (t1.id <= 1000))
Heap Blocks: exact=637
Buffers: shared hit=5 read=637 dirtied=123
-> Bitmap Index Scan on test01_pkey (cost=0.00..18.66 rows=623 width=0) (actual time=0.254..0.254 rows=640 loops=1)
Index Cond: ((t1.id >= 1) AND (t1.id <= 1000))
Buffers: shared hit=4 read=1
-> Index Scan using test02_pkey on public.test02 t2 (cost=0.43..8.38 rows=1 width=37) (actual time=0.013..0.013 rows=1 loops=640)
Output: t2.id, t2.info
Index Cond: (t2.id = t1.id)
Buffers: shared hit=1924 read=402 dirtied=65
Planning time: 26.668 ms
Execution time: 18.490 ms
(17 rows)第二次，缓存命中5.4毫秒
postgres=# explain (analyze,verbose,timing,costs,buffers) select t1.*,t2.* from test01 t1,test02 t2 where t1.id=t2.id and t1.id between 1 and 1000;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=19.25..7532.97 rows=623 width=74) (actual time=0.392..5.150 rows=402 loops=1)
Output: t1.id, t1.info, t2.id, t2.info
Buffers: shared hit=2968
-> Bitmap Heap Scan on public.test01 t1 (cost=18.82..2306.39 rows=623 width=37) (actual time=0.373..1.760 rows=640 loops=1)
Output: t1.id, t1.info
Recheck Cond: ((t1.id >= 1) AND (t1.id <= 1000))
Heap Blocks: exact=637
Buffers: shared hit=642
-> Bitmap Index Scan on test01_pkey (cost=0.00..18.66 rows=623 width=0) (actual time=0.218..0.218 rows=640 loops=1)
Index Cond: ((t1.id >= 1) AND (t1.id <= 1000))
Buffers: shared hit=5
-> Index Scan using test02_pkey on public.test02 t2 (cost=0.43..8.38 rows=1 width=37) (actual time=0.004..0.004 rows=1 loops=640)
Output: t2.id, t2.info
Index Cond: (t2.id = t1.id)
Buffers: shared hit=2326
Planning time: 0.956 ms
Execution time: 5.434 ms
(17 rows)根据索引字段调整表的物理顺序，降低离散IO。
postgres=# cluster test01 using test01_pkey;
CLUSTER
postgres=# cluster test02 using test02_pkey;
CLUSTER
postgres=# analyze test01;
postgres=# analyze test02;清除缓存，重启数据库
$ pg_ctl stop -m fast
# echo 3 > /proc/sys/vm/drop_caches
$ pg_ctl start第一次调用，降低到了5.4毫秒
postgres=# explain (analyze,verbose,timing,costs,buffers) select t1.*,t2.* from test01 t1,test02 t2 where t1.id=t2.id and t1.id between 1 and 1000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.86..5618.07 rows=668 width=74) (actual time=0.069..4.072 rows=402 loops=1)
Output: t1.id, t1.info, t2.id, t2.info
Buffers: shared hit=2323 read=12
-> Index Scan using test01_pkey on public.test01 t1 (cost=0.43..30.79 rows=668 width=37) (actual time=0.040..0.557 rows=640 loops=1)
Output: t1.id, t1.info
Index Cond: ((t1.id >= 1) AND (t1.id <= 1000))
Buffers: shared hit=5 read=6
-> Index Scan using test02_pkey on public.test02 t2 (cost=0.43..8.35 rows=1 width=37) (actual time=0.004..0.004 rows=1 loops=640)
Output: t2.id, t2.info
Index Cond: (t2.id = t1.id)
Buffers: shared hit=2318 read=6 -- 注意在cluster之后，shared hit并没有下降，因为LOOP了多次，但是性能确比cluster 之前提升了很多，因为需要访问的HEAP page少了，OS cache可以瞬间命中。
Planning time: 42.356 ms
Execution time: 5.426 ms
(13 rows)第二次调用，3.6毫秒
postgres=# explain (analyze,verbose,timing,costs,buffers) select t1.*,t2.* from test01 t1,test02 t2 where t1.id=t2.id and t1.id between 1 and 1000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.86..5618.07 rows=668 width=74) (actual time=0.055..3.414 rows=402 loops=1)
Output: t1.id, t1.info, t2.id, t2.info
Buffers: shared hit=2335
-> Index Scan using test01_pkey on public.test01 t1 (cost=0.43..30.79 rows=668 width=37) (actual time=0.037..0.374 rows=640 loops=1)
Output: t1.id, t1.info
Index Cond: ((t1.id >= 1) AND (t1.id <= 1000))
Buffers: shared hit=11
-> Index Scan using test02_pkey on public.test02 t2 (cost=0.43..8.35 rows=1 width=37) (actual time=0.003..0.004 rows=1 loops=640)
Output: t2.id, t2.info
Index Cond: (t2.id = t1.id)
Buffers: shared hit=2324
Planning time: 1.042 ms
Execution time: 3.620 ms
(13 rows)小结
通过cluster, 将表的物理顺序和索引对齐，所以如果查询的值是连续的，在使用嵌套循环时可以大幅减少离散IO，取得非常好查询优化的效果。
如果查询的值是跳跃的，那么这种方法就没有效果啦，不过好在PostgreSQL有bitmap index scan，在读取heap tuple前，会对ctid排序，按排序后的ctid取heap tuple，也可以起到减少离散IO的作用。
