PostgreSQL的部分索引

每个数据库的部分索引还不一样

我以前用过MySQL的部分索引。不过说实话使用场景不多。于是上次本来打算在书中也写这个。结果徐老师说PG的不一样。后来我尝试了。果然不一样。

xxg=# explain select * from xxg;
QUERY PLAN

Seq Scan on xxg (cost=0.00…45691.00 rows=100000 width=12)
(1 row)

xxg=# \d xxg
Table “public.xxg”
Column | Type | Collation | Nullable | Default
--------±--------±----------±---------±--------
id | integer | | |
a | integer | | |
b | integer | | |
Indexes:
“t1” btree (id)

ID列有一个索引。
xxg=#
xxg=# explain select * from xxg where id<10;
QUERY PLAN

Index Scan using t1 on xxg (cost=0.42…20.90 rows=10 width=12)
Index Cond: (id < 10)
(2 rows)
这个SQL用到了ID索引，属于正常。

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

创建一个PG的部分索引 --它的部分不是索引列的部分，而是结果集的部分。

但是创建时候犯了个错误 （我给a列创建，条件写的是id。这是半年前的事情。当时没注意，结果是死活不起作用）

xxg=# create index xxg_a on xxg (a) where id<10;
CREATE INDEX

xxg=# explain select * from xxg where a<10;
QUERY PLAN

Seq Scan on xxg (cost=0.00…45941.00 rows=10 width=12)
Filter: (a < 10)
(2 rows)

后来才发现原来是ID列。非常尴尬。用ID查询是可以了。于是发现好像where之前的括号没什么用处。

xxg=# explain select * from xxg where id<10;
QUERY PLAN

Index Scan using xxg_a on xxg (cost=0.14…16.59 rows=10 width=12)
(1 row)

然后就创建一个范围的部分索引。最后发现在这个范围内的可以使用所以，而范围以外就无法使用。当然这也是有特定场景的意义。一般场景用不到。

xxg=# create index xxg_b on xxg (b) where b>10 and b<100;
CREATE INDEX

xxg=# explain select * from xxg where b=22;
QUERY PLAN

Index Scan using xxg_b on xxg (cost=0.14…8.16 rows=1 width=12)
Index Cond: (b = 22)
(2 rows)

xxg=# explain select * from xxg where b=220;
QUERY PLAN

Seq Scan on xxg (cost=0.00…45941.00 rows=1 width=12)
Filter: (b = 220)
(2 rows)

时隔半年写了一下。当时书中后来没把这个加进去。以后再结合实际加到后续中吧。