Оптимизируйте агрегат max(), который также работает без строк.

У меня есть запрос, который я пытаюсь оптимизировать для PostgreSQL 9.2:

select coalesce(max(id),0) as m from tbl

Для запуска требуется вечность, поэтому я решил, что могу переписать его как

select id from tbl order by id desc limit 1

за исключением того, что он должен вернуть 0, если в таблице нет строк. Я пробовал несколько комбинаций операторов case, но они, похоже, не работают. Какие-либо предложения?

План для пустой таблицы:

Aggregate (cost=11.25..11.26 rows=1 width=4)
 -> Seq Scan on tbl (cost=0.00..11.00 rows=100 width=4)

Стоимость составляет 58 000 с таблицей с 1 190 000 строк, хотя план выполнения тот же.


postgresql aggregate-functions postgresql-9.2 postgresql-performance
person tandoan    schedule 13.01.2013    source источник
comment
Что такое a m? Опечатка? должно быть select coalesce(max(id),0) AS m from tbl   -  person wildplasser    schedule 13.01.2013
comment
@a_horse_with_no_name id — это первичный ключ. Это план для пустой таблицы. Стоимость составляет 58 000 с таблицей с 1 190 000 строк, хотя план выполнения тот же.   -  person tandoan    schedule 13.01.2013
comment
План должен выглядеть следующим образом: explain.depesz.com/s/hp8 (в таблице 5000000 строк)   -  person a_horse_with_no_name    schedule 13.01.2013
comment
автовакуум включен. после анализа оценка строки равна 1, стоимость 59k. Запущенный set enable_seqscan=off по-прежнему выполняет последовательное сканирование.   -  person tandoan    schedule 13.01.2013
comment
Должно быть что-то, что вы нам не говорите. Трудно поверить, что PostgreSQL 9.2 на самом деле выполняет полное сканирование таблицы при выполнении max() на PK (см. мою ссылку на объяснение.depesz.com).   -  person a_horse_with_no_name    schedule 13.01.2013
comment
См. также здесь: sqlfiddle.com/#!12/77e30/1   -  person a_horse_with_no_name    schedule 13.01.2013
comment
Хм. Недавно он был обновлен с 8.3.16 до 9.2. Будет ли это иметь какое-то значение?   -  person tandoan    schedule 13.01.2013

Ответы (2)


arrow_upward
4
arrow_downward

за исключением того, что он должен вернуть 0, если в таблице нет строк

SELECT COALESCE((SELECT max(id) FROM tbl), 0)

Поскольку строки не возвращаются, вы не можете просто использовать COALESCE(), чтобы указать значение по умолчанию для этого специального вида NULL (без строки). Поместите его в подзапрос.
Прекрасный момент: это также возвращает 0, если все существующие строки tbl содержат id IS NULL.

Ключом к производительности является индекс по tbl.id. Это может быть первичное (или уникальное) ключевое ограничение, которое реализуется посредством уникального индекса для столбца или любого простого индекса B-дерева:

CREATE index foo on tbl(id)

Тесно связанный вопрос:
Как отобразить значение по умолчанию, если в запросе не найдено совпадений?

person Erwin Brandstetter    schedule 14.01.2013

arrow_upward
0
arrow_downward

Может быть, это:

select
    case
        when (select id from tbl limit 1) is null then 0
        else (select id from tbl order by id desc limit 1)
    end;
person creaktive    schedule 13.01.2013
comment
select count(1) from tbl приведет к полному сканированию таблицы, что приведет к ОЧЕНЬ медленному запросу. - person Ihor Romanchenko; 13.01.2013