PostgreSQL 部分索引
本文介绍在 PostgreSQL 中如何创建部分索引。
PostgreSQL 允许您创建部分索引。部分索引由助于减少索引大小,因为它没有为列中的所有数据创建索引,它只为符合条件的行中的数据建立索引。
如果您有 WHERE
使用常量值的常用条件,则部分索引很有用,如下所示:
SELECT *
FROM table_name
WHERE column_name = constant_value;
让我们看一下 Sakila 示例数据库中的 customer
表:
\d customer
Table "public.customer"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+-----------------------------------------------
customer_id | integer | | not null | nextval('customer_customer_id_seq'::regclass)
store_id | smallint | | not null |
first_name | character varying(45) | | not null |
last_name | character varying(45) | | not null |
email | character varying(50) | | |
address_id | smallint | | not null |
activebool | boolean | | not null | true
create_date | date | | not null | 'now'::text::date
last_update | timestamp without time zone | | | now()
active | integer | | |
Indexes:
"customer_pkey" PRIMARY KEY, btree (customer_id)
"customer_lower_idx" btree (lower(last_name::text))
"idx_fk_address_id" btree (address_id)
"idx_fk_store_id" btree (store_id)
"idx_last_name" btree (last_name)
例如,您通常对不活跃的客户感兴趣,并经常进行一些跟进以让他们回来购买更多东西。
以下查询查找所有不活动的客户:
SELECT *
FROM customer
WHERE active = 0;
要执行此查询,查询计划器需要扫描 customer
表,如以下 EXPLAIN
语句所示:
EXPLAIN
SELECT *
FROM customer
WHERE active = 0;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on customer (cost=0.00..15.49 rows=15 width=70)
Filter: (active = 0)
(2 rows)
您可以通过为 active
列创建索引来优化此查询, 如下所示:
CREATE INDEX idx_customer_active
ON customer(active);
该索引实现了其目的,但是,它包含许多从未搜索过的行,即所有活跃客户。
要定义仅包含非活动客户的索引,请使用以下语句:
CREATE INDEX idx_customer_inactive
ON customer(active)
WHERE active = 0;
该语句定义了一个部分索引 idx_customer_inactive
,只有符合 WHERE
条件 active = 0
中的 active
值才会被索引。 相比较为所有 active
值建立的索引 idx_customer_active
,此索引的大小会很小。
从现在开始,只要该 WHERE
子句出现在查询中,PostgreSQL 就会考虑部分索引:
EXPLAIN
SELECT *
FROM customer
WHERE active = 0;
QUERY PLAN
---------------------------------------------------------------------------------------
Index Scan using idx_customer_active on customer (cost=0.15..11.69 rows=15 width=70)
Index Cond: (active = 0)
(2 rows)
结论
本文介绍了 PostgreSQL 部分索引以及如何使用它来指定应添加到索引中的行。