sql - PostgreSQL - 状态字段的正确索引选择(varchar)

Question

我有一个包含很多条目的表和一个长度为 8 的 varchar 字段，表示不同的状态。只有大约 5 种不同的状态，比方说“STATUS1”、“STATUS2”……大多数时候它是 NULL。

当我为该字段建立索引时，它并没有做太多事情，因为有很多相等的值，然后 postgres 不使用索引。

我的问题是:有没有一种方法可以索引这样的字段并使其更快？大多数时候我查询 status IS NULL 并且我认为我不能让它更快。但是，如果我检查 status = 'STATUS1' 会怎样？

Answer 1

您可以使用 partial indexes在某些情况下。假设您有很多类似的查询

SELECT *
  FROM the_table
 WHERE color in ('green', 'blue') AND status = 'STATUS1' ;

如果您创建一个部分索引，这个查询很可能会运行得更快:

CREATE TABLE the_table
(
   color text, 
   status character varying(8)
    /* and anything you need */
) ; 

CREATE INDEX
  ON public.the_table (color)
  WHERE status = 'STATUS1' ;

如果使用 PostgreSQL(或任何其他允许它的数据库)，我可能会创建一个 enumerated type同样，而不是 varchar。您有两个优势:只允许枚举值(因此“自动检查”)，以及存储信息(和索引)所需的空间小于 varchar(8):

CREATE TYPE status_type AS ENUM
   ('STATUS1',
    'STATUS2',
    'STATUS3');

然后用它创建表:

CREATE TABLE the_table
(
   color text, 
   status status_type
    /* and anything you need */
) ; 

如果您需要(以编程方式)知道枚举的允许值(例如，创建菜单)，check here .

如果数据库不允许枚举，我会规范化为一个小型 [ish] 表(anonymous_id_PK，status_value)对。