sql - 在 postgresql 中使用具有较低功能的索引

Question

要创建我的表和索引，我使用以下代码:

CREATE TABLE IF NOT EXISTS users (
    id SERIAL NOT NULL,
    name VARCHAR(512) NOT NULL,
    PRIMARY KEY (id));

CREATE INDEX users_name_idx ON users (lower(name::varchar(16)));

我的问题 - users_name_idx 索引用于以下查询:

1. SELECT * FROM users WHERE LOWER(name) LIKE 'somename%'?
2. SELECT * FROM users ORDER BY name?

Answer 1

您的索引不能被您的任何查询使用，因为表达式与查询中的表达式不同:

test=> \d users
                             Table "laurenz.users"
 Column |          Type          | Nullable |              Default              
--------+------------------------+----------+-----------------------------------
 id     | integer                | not null | nextval('users_id_seq'::regclass)
 name   | character varying(512) | not null | 
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_name_idx" btree (lower(name::character varying(16)::text))

test=> SET enable_seqscan = off;

test=> EXPLAIN SELECT * FROM users WHERE LOWER(name) LIKE 'somename%';
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Seq Scan on users  (cost=10000000000.00..10000000012.10 rows=1 width=520)
   Filter: (lower((name)::text) ~~ 'somename%'::text)
(2 rows)

test=> EXPLAIN SELECT * FROM users ORDER BY name;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Sort  (cost=10000000016.39..10000000016.74 rows=140 width=520)
   Sort Key: name
   ->  Seq Scan on users  (cost=10000000000.00..10000000011.40 rows=140 width=520)
(3 rows)

对于要使用的索引，您必须在查询中使用相同的表达式，包括类型转换。

除此之外，除非您的列具有排序规则 C，否则您的索引不能在 LIKE 查询中使用。您已同意使用 text_pattern_ops 运算符类。

我想创建这样一个索引背后的原因是为了减少索引的大小，这是一件值得称赞的事情。

我会推荐这样的索引:

CREATE INDEX ON users (lower(name::varchar(16)) text_pattern_ops);

然后使用这个查询:

SELECT * FROM users
WHERE lower(name) LIKE 'somename%'
  AND lower(name::varchar(16)) LIKE substr('somename%', 1, 16);

如果 somename 超过 15 个字符，第二个条件可能是有损的，但它可以使用索引。第一个条件过滤掉误报。

不幸的是，在订购时没有这样的技巧。