Оптимизировать групповой максимальный запрос
select *
from records
where id in ( select max(id) from records group by option_id )
Этот запрос отлично работает даже на миллионах строк. Однако, как вы можете видеть из результата объяснения:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=30218.84..31781.62 rows=620158 width=44) (actual time=1439.251..1443.458 rows=1057 loops=1)
-> HashAggregate (cost=30218.41..30220.41 rows=200 width=4) (actual time=1439.203..1439.503 rows=1057 loops=1)
-> HashAggregate (cost=30196.72..30206.36 rows=964 width=8) (actual time=1438.523..1438.807 rows=1057 loops=1)
-> Seq Scan on records records_1 (cost=0.00..23995.15 rows=1240315 width=8) (actual time=0.103..527.914 rows=1240315 loops=1)
-> Index Scan using records_pkey on records (cost=0.43..7.80 rows=1 width=44) (actual time=0.002..0.003 rows=1 loops=1057)
Index Cond: (id = (max(records_1.id)))
Total runtime: 1443.752 ms
(cost=0.00..23995.15 rows=1240315 width=8) <- Здесь говорится, что он сканирует все строки, и это явно неэффективно.
Я также попытался изменить порядок запроса:
select r.* from records r
inner join (select max(id) id from records group by option_id) r2 on r2.id= r.id;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=30197.15..37741.04 rows=964 width=44) (actual time=835.519..840.452 rows=1057 loops=1)
-> HashAggregate (cost=30196.72..30206.36 rows=964 width=8) (actual time=835.471..835.836 rows=1057 loops=1)
-> Seq Scan on records (cost=0.00..23995.15 rows=1240315 width=8) (actual time=0.336..348.495 rows=1240315 loops=1)
-> Index Scan using records_pkey on records r (cost=0.43..7.80 rows=1 width=44) (actual time=0.003..0.003 rows=1 loops=1057)
Index Cond: (id = (max(records.id)))
Total runtime: 840.809 ms
(cost=0.00..23995.15 rows=1240315 width=8) <- Все еще сканирует все строки.
Я пытался с и без индекса на (option_id) , (option_id, id) , (option_id, id desc) Ни один из них не имел никакого влияния на план запроса.
Есть ли способ выполнить групповой максимальный запрос в Postgres без сканирования всех строк?
То, что я ищу, программно, это индекс, который хранит максимальный идентификатор для каждого option_id как они вставлены в таблицу записей. Таким образом, когда я запрашиваю максимумы option_ids, мне нужно только сканировать индексные записи столько раз, сколько есть разные option_ids.
я видел select distinct on ответы на все вопросы от высокопоставленных пользователей (спасибо @Clodoaldo Neto за предоставленные мне ключевые слова для поиска). Вот почему это не работает:
create index index_name on records(option_id, id desc)
select distinct on (option_id) *
from records
order by option_id, id desc
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=0.43..76053.10 rows=964 width=44) (actual time=0.049..1668.545 rows=1056 loops=1)
-> Index Scan using records_option_id_id_idx on records (cost=0.43..73337.25 rows=1086342 width=44) (actual time=0.046..1368.300 rows=1086342 loops=1)
Total runtime: 1668.817 ms
Это здорово, он использует индекс. Однако использование индекса для сканирования всех идентификаторов не имеет особого смысла. Согласно моим казням, это на самом деле медленнее, чем простое последовательное сканирование.
Интересно, что MySQL 5.5 может оптимизировать запрос, просто используя индекс на records(option_id, id)
mysql> select count(1) from records;
+----------+
| count(1) |
+----------+
| 1086342 |
+----------+
1 row in set (0.00 sec)
mysql> explain extended select * from records
inner join ( select max(id) max_id from records group by option_id ) mr
on mr.max_id= records.id;
+------+----------+--------------------------+
| rows | filtered | Extra |
+------+----------+--------------------------+
| 1056 | 100.00 | |
| 1 | 100.00 | |
| 201 | 100.00 | Using index for group-by |
+------+----------+--------------------------+
3 rows in set, 1 warning (0.02 sec)
4 ответа
Предполагая относительно мало строк вoptions для многих строк вrecords,
Как правило, у вас есть справочная таблицаoptions на который ссылаются из records.option_idв идеале с ограничением внешнего ключа. Если вы этого не сделаете, я предлагаю создать один для обеспечения ссылочной целостности:
CREATE TABLE options (
option_id int PRIMARY KEY
, option text UNIQUE NOT NULL
);
INSERT INTO options
SELECT DISTINCT option_id, 'option' || option_id -- dummy option names
FROM records;
Тогда нам больше не нужно эмулировать сканирование свободного индекса, и это становится очень простым и быстрым. Коррелированные подзапросы могут использовать простой индекс на (option_id, id),
SELECT option_id
,(SELECT max(id)
FROM records
WHERE option_id = o.option_id
) AS max_id
FROM options o
ORDER BY 1;
Это включает в себя варианты без совпадения в таблице records, Вы получаете NULL за max_id и вы можете легко удалить такие строки во внешнем SELECT если нужно.
Или (тот же результат):
SELECT option_id
, (SELECT id
FROM records
WHERE option_id = o.option_id
ORDER BY id DESC NULLS LAST
) AS max_id
FROM options o
ORDER BY 1;
Может быть немного быстрее. Подзапрос использует порядок сортировки DESC NULLS LAST - так же, как агрегатная функция max() который игнорирует значения NULL. Сортировка просто DESC будет иметь значение NULL первым:
Итак, идеальный показатель для этого:
CREATE INDEX on records (option_id, id DESC NULLS LAST);
Не имеет большого значения, пока определены столбцы NOT NULL,
Там все еще может быть последовательное сканирование на маленьком столе optionsЭто самый быстрый способ получить все строки. ORDER BY может принести сканирование индекса (только), чтобы извлечь предварительно отсортированные строки.
Большой стол records Доступ только через (растровое) сканирование индекса или, если возможно, сканирование только индекса.
SQL Fiddle показывает два сканирования только по индексу для простого случая.
Или использовать LATERAL присоединяется для аналогичного эффекта в Postgres 9.3+:
PostgreSQL не поддерживает произвольное сканирование, которое MySQL может использовать для подобных запросов. Это Using index for group-by вы видите на плане MySQL.
По сути, он возвращает первую или последнюю запись в диапазоне, соответствующем подмножеству составного ключа, а затем ищет следующее или предыдущее значение этого подмножества.
В вашем случае он сначала возвращает последнее значение всего индекса на (option_id, id) (который по определению содержит MAX(id) для величайшего option_id), затем ищет последнее значение с рядом с самым большим option_id и так далее.
Оптимизатор PostgreSQL не может построить такой план, однако PostgreSQL позволяет вам эмулировать его в SQL. Если у вас много записей, но мало option_idСтоит делать.
Для этого сначала создайте индекс:
CREATE INDEX ix_records_option_id ON records (option_id, id);
затем выполните этот запрос:
WITH RECURSIVE q (option_id) AS
(
SELECT MIN(option_id)
FROM records
UNION ALL
SELECT (
SELECT MIN(option_id)
FROM records
WHERE option_id > q.option_id
)
FROM q
WHERE option_id IS NOT NULL
)
SELECT option_id,
(
SELECT MAX(id)
FROM records r
WHERE r.option_id = q.option_id
)
FROM q
WHERE option_id IS NOT NULL
Смотрите это на sqlfiddle.com: http://sqlfiddle.com/
Вы упоминаете, что хотите индекс, который индексирует только max(id) для каждого option_id. В настоящее время это не поддерживается PostgreSQL. Если такая функция будет добавлена в будущем, она, вероятно, будет реализована с помощью механизма создания материализованного представления совокупного запроса и последующей индексации материализованного представления. Я не ожидал бы по крайней мере пару лет, хотя.
Однако теперь вы можете использовать рекурсивный запрос, чтобы он пропускал индекс до каждого уникального значения option_id. Смотрите вики-страницу PostgreSQL для общего описания техники.
То, как вы можете использовать это для вашего случая, это написать рекурсивный запрос, чтобы вернуть отдельные значения option_id, а затем для каждого из них выбрать max(id):
with recursive dist as (
select min(option_id) as option_id from records
union all
select (select min(option_id) from records where option_id > dist.option_id)
from dist where dist.option_id is not null
)
select option_id,
(select max(id) from records where records.option_id=dist.option_id)
from dist where option_id is not null;
Это некрасиво, но вы можете скрыть это за взглядом.
В моих руках это работает в 43 мс, а не 513 мс для on distinct разнообразие.
Вероятно, это можно сделать примерно вдвое быстрее, если вы найдете способ включить max(id) в рекурсивный запрос, но я не смог найти способ сделать это. Проблема в том, что эти запросы имеют довольно ограниченный синтаксис, вы не можете использовать "limit" или "order by" в сочетании с UNION ALL.
Этот запрос касается страницы, широко разбросанной по всему индексу, и если эти страницы не помещаются в кэш, вы будете выполнять много неэффективного ввода-вывода. Однако, если этот тип запроса популярен, то у 1057 листовых страниц индекса будет небольшая проблема с сохранением в кеше.
Вот как настроить мой тестовый пример:
create table records as select floor(random()*1057)::integer as option_id, floor(random()*50000000)::integer as id from generate_series(1,1240315);
create index on records (option_id ,id);
explain analyze;
select distinct on (option_id) *
from records
order by option_id, id desc
Индексы будут использоваться только в том случае, если количество элементов будет благоприятным. Тем не менее, вы можете попробовать составной индекс
create index index_name on records(option_id, id desc)