Order BY превращает запрос 30 мс в запрос 7120 мс. Известная проблема производительности?

Question

У меня есть таблица пользователей с записями 1м:

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 требуется 7120 мс, чтобы вернуть 25 строк. Если я уберу 'ORDER BY guest = false, fname ASC', запрос займет всего 31 мс.

У меня есть следующие индексы:

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

Если в столбце мало разных значений, индекс для этого столбца не имеет большого значения. Это имеет место с логическим столбцом.

Я бы протестировал создание частичного индекса на 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;

Answer 4

На первый взгляд, ваша проблема заключается в необходимости полностью оценить предложение 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]))