postgresql 复合索引中的列顺序

enyaitl3 于 2022-12-18 发布在 PostgreSQL

关注(0)|答案(2)|浏览(126)

我在一个有超过1300万条记录的表上使用复合索引，index order is (center_code, created_on, status).center_code和status都是varchar（100），而不是NULL，created_on是没有时区的时间戳。
我在哪里读到过复合索引中索引的顺序很重要，我们必须检查唯一值的数量，并将唯一值数量最多的放在复合索引的第一位。

center_code可以有4000个不同的值。
状态可以有5个不同的值。
created_on的最小值为2017-12-12 02:00:49.465317+00。

问题是created_on的唯一值的数量是多少？我应该把它放在复合索引的第一位吗？
对日期列进行索引可以基于日期、基于小时或基于秒。
问题是：
一个简单的SELECT查询只使用这个复合索引而不使用其他索引，需要500毫秒以上的时间。
表上的索引：

Indexes:
    "pa_key" PRIMARY KEY, btree (id)
    "pa_uniq" UNIQUE CONSTRAINT, btree (wbill)
    "pa_center_code_created_on_status_idx_new" btree (center_code, created_on, status)

查询为：

EXPLAIN ANALYSE 
SELECT "pa"."wbill" 
FROM "pa" 
WHERE ("pa"."center_code" = 'IND110030AAC' 
AND "pa"."status" IN ('Scheduled') 
AND "pa"."created_on" >= '2018-10-10T00:00:00+05:30'::timestamptz);

查询计划：

Index Scan using pa_center_code_created_on_status_idx_new on pa  (cost=0.69..3769.18 rows=38 width=13) (actual time=5.592..15.526 rows=78 loops=1)
   Index Cond: (((center_code)::text = 'IND110030AAC'::text) AND (created_on >= '2018-10-09 18:30:00+00'::timestamp with time zone) AND ((status)::text = 'Scheduled'::text))
     Planning time: 1.156 ms
     Execution time: 519.367 ms

型
任何帮助都将不胜感激。

postgresql

来源：https://stackoverflow.com/questions/53059176/order-of-columns-in-compound-indexes

2条答案

按热度按时间

q5lcpyga1#

索引扫描条件为

(((center_code)::text = 'IND110030AAC'::text) AND
  (created_on >= '2018-10-09 18:30:00+00'::timestamp with time zone) AND
 ((status)::text = 'Scheduled'::text))

但是索引扫描本身仅在(center_code, created_on)上，而status上的条件被用作滤波器。
遗憾的是，这在执行计划中不可见，但它遵循以下规则：

仅当满足条件的行在索引中相邻时，索引扫描才会使用条件。

让我们考虑这个例子（按索引顺序）：

center_code  | created_on          | status
--------------+---------------------+-----------
 IND110030AAC | 2018-10-09 00:00:00 | Scheduled
 IND110030AAC | 2018-10-09 00:00:00 | Xtra
 IND110030AAC | 2018-10-10 00:00:00 | New
 IND110030AAC | 2018-10-10 00:00:00 | Scheduled
 IND110030AAC | 2018-10-11 00:00:00 | New
 IND110030AAC | 2018-10-11 00:00:00 | Scheduled

您将看到查询需要第4行和第6行。
PostgreSQL无法同时扫描这三个条件下的索引，因为所需的行不相邻。它只能扫描前两个条件，因为所有满足这两个条件的行都是相邻的。
您的多列索引规则错误。索引左侧的列必须是=在条件中用作比较运算符的列。
理想的索引应该是(center_code, status, created_on)上的1。

赞(0）回复(0）举报 2022-12-18

64jmpszr2#

我在工作中学到的一个技巧是，当您创建复合idx时，带有条件（=）的列应该优先，其他条件（〉，〈，〉=，〈=，IN）应该紧随其后。

赞(0）回复(0）举报 2022-12-18