你可以进行一些近似计算,以了解何时开始使用你的索引。
t=# drop table s07;
DROP TABLE
t=# create table s07 (i int, r int, t text);
CREATE TABLE
t=# insert into s07 select 1,1,'some text';
INSERT 0 1
t=# insert into s07 select 2,2,'some text';
INSERT 0 1
t=# insert into s07 select 3,3,'some text';
INSERT 0 1
t=# insert into s07 select 4,4,'some text';
INSERT 0 1
t=# create index s07i on s07 (i);
CREATE INDEX
t=# analyze s07;
ANALYZE
t=# SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname LIKE 's07%';
relname | relkind | reltuples | relpages
s07 | r | 4 | 1
s07i | i | 4 | 2
(2 rows)
尽管索引具有相同数量的行和更少的列,但在少量数据上,它实际上需要两倍的空间!关系-1页,索引-2页,因此规划程序执行顺序扫描:
t=
QUERY PLAN
---------------------------------------------------------------------------------------------
Seq Scan on s07 (cost=0.00..1.05 rows=4 width=4) (actual time=0.003..0.004 rows=3 loops=1)
Filter: (i < 4)
Rows Removed by Filter: 1
Planning time: 0.086 ms
Execution time: 0.013 ms
(5 rows)
所以在填充一些数据后:
t=# insert into s07 select i,i,'some text' from generate_series(1,99,1) i;
INSERT 0 99
t=# analyze s07;
ANALYZE
t=# SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname LIKE 's07%';
relname | relkind | reltuples | relpages
s07 | r | 103 | 1
s07i | i | 103 | 2
(2 rows)
t=# explain analyze select i from s07 where i < 4;
QUERY PLAN
Seq Scan on s07 (cost=0.00..2.29 rows=7 width=4) (actual time=0.008..0.016 rows=6 loops=1)
Filter: (i < 4)
Rows Removed by Filter: 97
Planning time: 0.119 ms
Execution time: 0.029 ms
(5 rows)
同一张图片。因此,要放置更多的数据:
t=# insert into s07 select i,i,'some text' from generate_series(1,299,1) i;
INSERT 0 299
t=# analyze s07;
ANALYZE
t=# SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname LIKE 's07%';
relname | relkind | reltuples | relpages
s07 | r | 402 | 3
s07i | i | 402 | 2
(2 rows)
t=# explain analyze select i from s07 where i < 4;
QUERY PLAN
Bitmap Heap Scan on s07 (cost=4.22..7.33 rows=9 width=4) (actual time=0.005..0.007 rows=9 loops=1)
Recheck Cond: (i < 4)
Heap Blocks: exact=1
-> Bitmap Index Scan on s07i (cost=0.00..4.21 rows=9 width=0) (actual time=0.002..0.002 rows=9 loops=1)
Index Cond: (i < 4)
Planning time: 0.099 ms
Execution time: 0.017 ms
(7 rows)
使用条件索引进行这种计算当然会更加复杂,但基本上在这里可以看到,即使从一页中过滤100行也比加载两页更便宜。
现在当然你可以通过配置更改此行为,例如当我们有100行时,如果运行:
t=
SET
t=
SET
t=
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Index Only Scan using s07i on s07 (cost=0.14..15.16 rows=7 width=4) (actual time=0.012..0.014 rows=6 loops=1)
Index Cond: (i < 4)
Heap Fetches: 6
Planning time: 0.058 ms
Execution time: 0.028 ms
(5 rows)
尽管页面数量适用于Seq扫描:
t=# SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname LIKE 's07%';
relname | relkind | reltuples | relpages
s07 | r | 103 | 1
s07i | i | 103 | 2
(2 rows)
当然,您可以通过临时设置enable_seqscan=off
来检查执行计划和时间。
更新
即使在文档中也提到100行对于索引扫描来说是太少的数量:
选择100行中的1行几乎不可能成为索引扫描的候选项,因为这100行可能适合于单个磁盘页,并且没有计划可以胜过顺序获取1个磁盘页。
set enable_seqscan=off
,以确保它能使用你的索引 :) - stas.yaranovremaining <= '300'
条件),则可能根本不会使用它,即使您的表有大量行。 - teppic