在PostgreSQL中使用lower函数与索引

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？

- Pavel_K

---

检查执行计划，你就会知道。但如果只有前16个字符是重要的，为什么你定义name时限制了512个字符？ - user330315

https://www.postgresql.org/docs/current/static/using-explain.html - user330315

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个字符，可能会出现信息丢失的情况，但它可以使用索引。第一个条件可以过滤掉假阳性。

不幸的是，当涉及排序时，没有类似这样的技巧。

- Laurenz Albe

---

非常感谢您的帖子。它解释了很多问题。我以为我要疯了。 - undefined

顺便说一句，询问您真正的问题而不是关于解决方案的问题会更好。请参阅此处。 - undefined

我没有意识到这是一个理论问题（这没关系）。这个问题看起来像你有一个实际的问题。 - undefined

不，没错。你需要一个第二个索引来支持排序，或者你可以使用C排序规则进行排序。 - undefined

不必嘲讽PostgreSQL。想一想索引是如何组织的，然后设想一下你的索引，并尝试将其用于排序。你的索引并不知道abcdefghijklmnopqry是在abcdefghijklmnopqrz之前还是之后排序的。没有任何数据库系统能够做到魔法般的事情。 - undefined