Order BY 将 30ms 查询变成 7120ms 查询。已知的性能问题？

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

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

我有以下针对 postgres 9.1 数据库运行的查询：

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 正在执行7120ms返回 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"

首先，从 PostgreSQL 9.1 开始你可以使用left() http://www.postgresql.org/docs/current/interactive/functions-string.html简化表达式：

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

获取第一个字符的速度也稍快一些before转换为小写。
接下来，清理查询：

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 http://www.postgresql.org/docs/current/interactive/sql-select.html#SQL-ORDERBY结果与guest = FALSE，只是无需为每一行计算新值。
接下来，创建这个多列部分索引 http://www.postgresql.org/docs/current/interactive/indexes.html:

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

Run

ANALYZE users;

或者，甚至更好，CLUSTER http://www.postgresql.org/docs/current/interactive/sql-cluster.html（如果您没有更重要的查询需要不同的顺序） - 并且then ANALYZE:

CLUSTER users using users_multi_idx;

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