Оптимизировать групповой максимальный запрос

Ответ 1

Предполагая относительно немного строк в 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:

• Почему значения NULL появляются при заказе DESC в запросе PostgreSQL?

Итак, идеальный индекс для этого:

CREATE INDEX on records (option_id, id DESC NULLS LAST);

Не важно, сколько столбцов определено NOT NULL.

Все еще может быть последовательное сканирование в маленькой таблице options, что является самым быстрым способом для извлечения всех строк. ORDER BY может ввести индекс (только) для извлечения предварительно отсортированных строк.
Большую таблицу records можно получить только через сканирование индексов (bitmap) - или, если возможно, сканирование с индексом.

SQL Fiddle, показывающий два сканирования только по индексу для простого случая.

Или использовать LATERAL для аналогичного эффекта в Postgres 9.3 +:

• Оптимизировать запрос GROUP BY для получения последней записи на пользователя

Ответ 2

Вы упомянули о необходимости индекса, который индексирует только max (id) для каждого параметра option_id. В настоящее время это не поддерживается PostgreSQL. Если такая функция будет добавлена ​​в будущем, это, вероятно, будет сделано с помощью механизма представления материализованного представления по совокупному запросу, а затем индексации материализованного представления. Я бы не ожидал, по крайней мере, пару лет.

Теперь вы можете использовать рекурсивный запрос, чтобы он пропускал индекс к каждому уникальному значению параметра option_id. См. страницу wiki 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;

Ответ 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/#!15/4d77d/4

Ответ 4

select distinct on (option_id) *
from records
order by option_id, id desc

Индексы будут использоваться, только если cardinality является благоприятным. При этом вы можете попробовать составной индекс

create index index_name on records(option_id, id desc)