【PGCCC】 复合索引和部分索引，竟然能让查询速度提升 275 倍！

使用复合索引的缺点

复合索引示例

让我们考虑一个用于存储销售数据的简单表，该表具有主键，但没有其他索引。该表可能如下所示

postgres=# \d sales
                                    表“public.sales”
   列|类型|排序规则|可空|默认                 
-------------+-----------+-----------+-------------+---------------------------------------- 
sale_id |整数||非空|nextval('sales_sale_id_seq'::regclass) 
customer_id |整数||非空|  
product_id |整数||非空|  
sale_date |日期||非空| 
金额|数字(10,2)||非空| 
索引：
    “sales_pkey”主键，btree(sale_id)

让我们执行一个简单的 SELECT 查询来获取 product_id = 408 的所有销售信息，其中sale_date是 2024-08-17 。

postgres = # EXPLAIN ANALYZE SELECT * FROM sales WHERE product_id = 408 AND sale_date = '2024-08-17';
                                                    查询计划                                                     
------------------------------------------------------------------------------------------------------------------
收集（成本=1000.00..7310.30 行=3 宽度=22）（实际时间=4.374..19.059 行=4 循环=1）
   计划的工人：2
   启动的工人：2 
   -> 销售并行序列扫描（成本=0.00..6310.00 行=1 宽度=22）（实际时间=9.093..14.098 行=1 循环=3）
         过滤器：（（product_id = 408）AND（sale_date = '2024-08-17'::date））
         过滤器删除的行：166665
计划时间：0.251 毫秒
执行时间：19.078 毫秒
（8 行）

PostgreSQL 规划器选择使用并行顺序扫描来获取所需结果，这是有道理的，因为目前还没有索引。这需要 19.078 毫秒才能完成。

现在，让我们在同一个表中的 product_id和sale_date列上创建一个复合索引。

创建索引 idx_sales_product_id_sale_date ON sales(product_id, sale_date);
创建索引

现在，让我们再次运行相同的 SELECT查询。

解释分析选择*从销售中获取产品id = 408 和销售日期 = '2024-08-17'；
                                                              查询计划                                                               
--------------------------------------------------------------------------------------------------------------------------------------------------销售位图堆扫描（成本=4.45..16.22 行=3 宽度=22）（实际时间=0.048..0.055 行=4 循环=1）
   重新检查条件：（（product_id = 408）AND（sale_date = '2024-08-17'::date））
   堆块：精确=4 
   -> idx_sales_product_id_sale_date 位图索引扫描（成本=0.00..4.45 行=3 宽度=0）（实际时间=0.045..0.045 行=4 循环=1）
         索引条件：（（product_id = 408）AND（sale_date = '2024-08-17'::date））
计划时间：0.265 毫秒
执行时间： 0.074 毫秒
（7 行）

哇！执行时间从  19.078 毫秒缩短至  0.074 毫秒，性能提高了近  275 倍。

部分索引

部分索引仅索引满足特定条件的数据子集，而不是覆盖表中的所有行。它们非常适合只频繁查询部分数据的情况，例如仅索引活跃用户或近期交易。通过定位特定的数据子集，部分索引可以减少存储需求并提高查询性能。

使用部分索引的优点

使用部分索引的缺点

部分索引不涵盖所有数据，因此随着表大小的增加，非索引数据上的连接或过滤可能会导致性能下降。 仅当索引子集被频繁查询时，部分索引才有用；否则，如果查询不符合索引条件，它们可能不会提高性能。

部分索引示例

让我们创建一个表来存储 COVID-19 数据。并在表中插入 300 万条虚拟记录。

如果不存在，则创建表 covid_data（id SERIAL PRIMARY KEY、国家 varchar(20)、标题 varchar(10)、名称 varchar(20)、接种疫苗 varchar(3)）；CREATE TABLE ostgres=# INSERT INTO covid_data (country, title, names, vaccinated) postgres-# SELECT -- 从预定义列表中随机选择国家(ARRAY['USA', 'Canada', 'UK', 'Germany', 'France', 'India', 'China', 'Brazil', 'Australia', 'Japan'])[floor(random() * 10 + 1)], 
-- 从预定义列表中随机选择头衔(ARRAY['Mr.', 'Ms.', 'Dr.', 'Prof.'])[floor(random() * 4 + 1)], 
-- 从预定义列表中随机选择姓名(ARRAY['John', 'Jane', 'Alex', 'Emily', 'Michael', 'Sarah', 'David', 'Laura', 'Robert', 'Linda'])[floor(random() * 10 + 1)], 
-- 随机疫苗接种状态（“是”或“否”）CASE 
  WHEN random() < 0.8 THEN '是' -- 80% 的可能性为“是” 
  ELSE '否' -- 20% 的可能性为“否” END FROM generate_series(1, 3000000);

首先，让我们使用 EXPLAIN ANALYZE运行SELECT查询来分析执行计划并获取结果。

现在，让我们再次运行相同的 SELECT查询并检查执行时间是否减少。

postgres=# EXPLAIN ANALYZE SELECT * FROM covid_data WHERE vaccinated = '是' AND country = '英国' AND title = '教授';
                                                                   查询计划                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------
对 covid_data 进行位图堆扫描（成本=495.29..499.31 行=1 宽度=174）（实际时间=100.977..229.152 行=59435 循环=1）
   重新检查条件：（（（title)::text = 'Prof.'::text）AND（（country)::text = 'UK'::text）AND（（vaccinated)::text = 'Yes'::text））
   堆块：精确=19605 
   -> BitmapAnd（成本=495.29..495.29 行=1 宽度=0）（实际时间=97.916..97.917 行=0 循环=1）
         -> 对 title_full_idx 进行位图索引扫描（成本=0.00..164.93行=15000 宽度=0）（实际时间=23.191..23.192 行=749479 循环=1）
               索引条件：（（title)::text = 'Prof.'::text）
         -> country_full_idx 上的位图索引扫描（成本=0.00..164.93 行=15000 宽度=0）（实际时间=11.334..11.334 行=299158 循环=1）
               索引条件：（（country)::text = 'UK'::text）
         -> vaccinated_full_idx 上的位图索引扫描（成本=0.00..164.93 行=15000 宽度=0）（实际时间=62.001..62.001 行=2400565 循环=1）
               索引条件：（（vaccinated)::text = '是'::文本）
计划时间：0.477 毫秒
执行时间：232.855 毫秒
（12 行）
postgres=# SELECT pg_size_pretty(pg_relation_size('title_full_idx')); pg_size_pretty  
---------------- 
20 MB （ 1 行） postgres=# SELECT pg_size_pretty(pg_relation_size ('country_full_idx')); pg_size_pretty ---------------- 20 MB（1 行） postgres=# SELECT pg_size_pretty( pg_relation_size ( ' vaccinated_full_idx 
')); pg_size_pretty  ---------------- 20 MB （1 行）

这次执行时间减少到 232 毫秒，性能提升了 1.9 倍。PostgreSQL 使用近 60 MB来存储这些索引。

现在，让我们使用部分索引来看看是否可以同时优化速度和空间并取得更好的结果。

创建部分索引

postgres = # CREATE INDEX vaccinated_partial_idx ON covid_data(vaccinated) WHERE vaccinated = '是' AND country = '英国' AND title = '教授'；
创建索引

现在执行相同的 SELECT 查询

postgres=# EXPLAIN ANALYZE SELECT * FROM covid_data WHERE vaccinated = 'Yes' AND country = 'UK' AND title = 'Prof.';
                                                                查询计划                                                                
--------------------------------------------------------------------------------------------------------------------------------------------------
使用 vaccinated_partial_idx 对 covid_data 进行索引扫描（成本=0.29..8.30 行=1 宽度=174）（实际时间=0.023..79.981 行=60644 循环=1）
计划时间：0.237 毫秒
执行时间：83.855 毫秒
（3 行）
postgres=# SELECT pg_size_pretty(pg_relation_size('vaccinated_partial_idx')); 
pg_size_pretty  
---------------- 
424 kB 
（1 行）

哇！通过部分索引，我们实现了5.4 倍的性能提升，并且大小减少了99.29％。