为什么Postgresql使用过滤器而不是索引？

Question

为什么Postgresql使用过滤器而不是索引？

12

我有一个名为 ad_item 的postgresql表，拥有以下列。

id | name | remaining | created_at

然后我对剩下的索引进行筛选，只保留小于等于300的。

create index remaining_index on ad_item using btree(id) where remaining <= 300

但是当我对查询执行explain analyze时。

explain analyze select id from ad_item where remaining <= 300

它正在显示以下内容。

Seq Scan on ad_item  (cost=0.00..2.06 rows=1 width=4) (actual time=0.010..0.013 rows=1 loops=1)
  Filter: (remaining <= '300'::numeric)
  Rows Removed by Filter: 4
Planning time: 0.115 ms
Execution time: 0.026 ms

为什么它没有使用我的remaining_index？那个索引是多余的吗？

谢谢。

- moeseth

3

由于您的表太小（只有5行），使索引的使用效率不高。 - user330315

你可以在查询之前执行set enable_seqscan=off，以确保它能使用你的索引 :) - stas.yaranov

@a_horse_with_no_name，你好，太棒了。所以我相信随着行数的增加，它会开始使用索引，然后会更快？谢谢。 - moeseth

如果索引不是非常选择性强的（即如果大多数行匹配您的remaining <= '300'条件），则可能根本不会使用它，即使您的表有大量行。 - teppic

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Vao Tsun · Accepted Answer

你可以进行一些近似计算，以了解何时开始使用你的索引。

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=# explain analyze select i from s07 where i < 4;
                                         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 cpu_tuple_cost to 1;
SET
t=# set cpu_index_tuple_cost to 0.000001;
SET
t=# explain analyze select i from s07 where i < 4;
                                                   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个磁盘页。