Order BY 将 30 毫秒的查询转换为 7120 毫秒的查询.已知的性能问题

Question

我有一个包含 1m 条记录的用户表：

User (id, fname, lname, deleted_at, guest)

我有以下查询正在针对 postgres 9.1 db 运行：

SELECT "users".* 
FROM "users" 
WHERE (users.deleted_at IS NULL) AND (SUBSTRING(lower(fname), 1, 1) = 's') 
ORDER BY guest = false, fname ASC 
LIMIT 25 OFFSET 0

使用 pgAdmin 3，此 SQL 需要 7120 毫秒才能返回 25 行。如果我删除"ORDER BY guest=false，fname ASC"，查询只需31ms。

我有以下索引：

add_index "users", ["fname"], :name => "index_users_on_fname"
add_index "users", ["guest", "fname"], :name => "index_users_on_guest_and_fname"
add_index "users", ["deleted_at"], :name => "index_users_on_deleted_at"
add_index "users", ["guest"], :name => "index_users_on_guest"

有什么想法吗？谢谢！

更新为解释

"Limit  (cost=43541.55..43541.62 rows=25 width=1612) (actual time=1276.777..1276.783 rows=25 loops=1)"
"  ->  Sort  (cost=43541.55..43558.82 rows=6905 width=1612) (actual time=1276.775..1276.777 rows=25 loops=1)"
"        Sort Key: ((NOT guest)), fname"
"        Sort Method: top-N heapsort  Memory: 37kB"
"        ->  Seq Scan on users  (cost=0.00..43346.70 rows=6905 width=1612) (actual time=5.143..1272.563 rows=475 loops=1)"
"              Filter: ((deleted_at IS NULL) AND pubic_profile_visible AND ((fname)::text ~~ 's%'::text))"
"Total runtime: 1276.967 ms"

Answer 1

首先，从 PostgreSQL 9.1 开始，您可以使用left()来简化表达式：

substring(lower(fname), 1, 1)
lower(left(fname, 1)) -- equivalent, but simpler and faster

在转换为小写之前取第一个字符的速度也略快。
接下来，清理查询：

SELECT * 
FROM   users 
WHERE  deleted_at IS NULL
AND    lower(left(fname, 1)) = 's'
ORDER  BY guest DESC NULLS LAST, fname
LIMIT  25 OFFSET 0;

guest DESC NULLS LAST的结果与guest = FALSE相同，只是没有计算每一行的新值。
接下来，创建此多列部分索引：

CREATE INDEX users_multi_idx
ON users (lower(left(fname, 1)), guest DESC NULLS LAST, fname)
WHERE deleted_at IS NULL;

跑

ANALYZE users;

或者，更好的是，CLUSTER（如果您没有需要不同顺序的更重要的查询） - 然后ANALYZE：

CLUSTER users using users_multi_idx;

而且它将比您以前尝试过的任何东西都要快得多。因为现在，查询按顺序从索引中读取行，并且表已以相同的顺序物理重写，导致只有很少的页面命中率......

Answer 2

在我看来，

你可以在这里有一些更好的索引;您正在基于deleted_at字段进行筛选，然后对guest字段进行排序，但这些字段不在公共索引中。暂时忽略您的其他WHERE子句，您似乎正在导致引擎挖掘所有记录，或者只是单独检查每条记录的guest值;我不明白您包含guest的索引如何提供帮助。

如果将guest字段与deleted_at字段一起包含在索引中（后者是第一个），则可能会在其中获得一些好处。

Answer 3

乍一看，您的问题是需要全面评估 where 子句，以便获得您需要按之后排序的所有（不仅仅是前 25 行）......尝试添加一个包含substring(lower(fname), 1, 1))让我们暂时将其命名为s的列，并在deleted_at, s上添加一个索引，或者如果这是您将在(deleted is null), (s = 's')上用索引来制定的唯一值。

您可以使用触发器使s列保持最新。

为了暂时加快速度，您可以将substring(lower(fname), 1, 1))重写为lower(substring(fname, 1, 1))，或者如果postgresql具有此语法lower(fname[1]))

Answer 4

如果列中的非重复值很少，则该列上的索引没有多大价值。布尔列就是这种情况。

我会测试在SUBSTRING(lower(fname), 1, 1)上创建部分索引

CREATE INDEX users_substr_null_ix ON users (SUBSTRING(lower(fname), 1, 1))
WHERE users.deleted_at IS NULL;

并在 fname 上测试部分索引：

CREATE INDEX users_fname_not_guest_ix ON users (fname)
WHERE not guest;

甚至更好

CREATE INDEX users_substr_null__not_guest_ix ON users (SUBSTRING(lower(fname), 1, 1), fname)
WHERE users.deleted_at IS NULL and not guest;