Путешествие запроса Select через внутренности Постгреса

[ilyaplot]

Спасибо за отличный материал. Без понимания того, как оно работает изнутри не получится полностью оптимизировать свою бд. Жду продолжения.

[java_lamer]

Почему при анализе и перезаписе он не выкинул сортировку, как явно не нужную при limit 1

[rdruzyagin]

Ответ кроется в следующем абзаце:

Как видите, Sort функционирует совсем не так, как Limit. Он сразу загружает все доступные данные из субплана в буфер прежде, чем что-либо возвращать. Затем он сортирует буфер с помощью алгоритма Quicksort и, наконец, возвращает первое отсортированное значение.

Весь блок данных будет отсортирован, а потом Limit из него уже будет подтягивать нужное количество. Сортировка не является ненужной, т.к. в посгресе таблицы не имеют какого-то неявного по-умолчанию установленного порядка хранения записей.

[java_lamer]

да, извиняюсь, поспешил не учел, что могут быть другие записи с таким именем и нам нужно вернуть именно с наименьшим id

[ploop]

Кстати, а что будет быстрее (правильнее)
классическое
select * from table where id = (select max(id) from table)
или
select * from table order by id limit 1


[LekaOleg]

Кстате мне тоже интересно) Буду благодарен за ответ!

[rdruzyagin]

Я думаю что второй вариант, т.к. при наличии индекса у вас будет единичный index scan.

В первом случае, вам надо сделать index scan для поиска максимального элемента во вложенном запросе, а потом еще index lookup для выборки нужного ряда.

[ploop]

Второй вариант ещё удобен при наличии громоздких условий.
Завтра смогу протестировать на разных (больших) таблицах, если кому интересно, с индексами и без.

[ploop]

Итак, две таблицы
t1 — 10556080 записей
t2 — 5864255 записей

1) Индексированные поля

select * from t1 where id = (select max(id) from t1) -- 12ms
select * from t1 order by id desc limit 1            -- 12ms

select * from t2 where id = (select max(id) from t2) -- 12ms
select * from t2 order by id desc limit 1            -- 12ms

Засечь разницу не удалось, выполняется моментально

2) Неиндексированные поля

select * from t1 where summa = (select max(summa) from t1) -- 5206ms
select * from t1 order by summa desc limit 1               -- 2159ms

select * from t2 where summa = (select max(summa) from t2) -- 4990ms
select * from t2 order by summa desc limit 1               -- 2240ms

Прирост в ~50%
Логика понятна.

[rdruzyagin]

Explain analyze более показателен будет в данном случае, я думаю. «Голое» время выполнения мало о чем говорит.

[ploop]

Да, конечно. В том же порядке

1) С индексом

EXPLAIN ANALYZE 
select * from t1 where id = (select max(id) from t1)

------------------
Index Scan using t1_pkey on t1  (cost=0.09..9.52 rows=1 width=136) (actual time=0.100..0.102 rows=1 loops=1)
  Index Cond: ((id)::bigint = $1)
  InitPlan 2 (returns $1)
    ->  Result  (cost=0.08..0.09 rows=1 width=0) (actual time=0.084..0.085 rows=1 loops=1)
          InitPlan 1 (returns $0)
            ->  Limit  (cost=0.00..0.08 rows=1 width=8) (actual time=0.078..0.080 rows=1 loops=1)
                  ->  Index Scan Backward using t1_pkey on t1  (cost=0.00..885209.13 rows=10556080 width=8) (actual time=0.075..0.075 rows=1 loops=1)
                        Index Cond: ((id)::bigint IS NOT NULL)
Total runtime: 0.179 ms

EXPLAIN ANALYZE 
select * from t1 order by id desc limit 1  

------------------
Limit  (cost=0.00..0.08 rows=1 width=136) (actual time=0.028..0.029 rows=1 loops=1)
  ->  Index Scan Backward using t1_pkey on t1  (cost=0.00..858818.93 rows=10556080 width=136) (actual time=0.024..0.024 rows=1 loops=1)
Total runtime: 0.120 ms

2) без

EXPLAIN ANALYZE 
select * from t1 where summa = (select max(summa) from t1)

------------------
Seq Scan on t1  (cost=279653.01..559306.01 rows=948 width=136) (actual time=14349.590..14431.243 rows=1 loops=1)
  Filter: (summa = $0)
  InitPlan 1 (returns $0)
    ->  Aggregate  (cost=279653.00..279653.01 rows=1 width=6) (actual time=12237.067..12237.068 rows=1 loops=1)
          ->  Seq Scan on t1  (cost=0.00..253262.80 rows=10556080 width=6) (actual time=0.003..5837.878 rows=10556080 loops=1)
Total runtime: 14431.325 ms

EXPLAIN ANALYZE 
select * from t1 order by summa desc limit 1  

------------------
Limit  (cost=306043.20..306043.20 rows=1 width=136) (actual time=11477.247..11477.248 rows=1 loops=1)
  ->  Sort  (cost=306043.20..332433.40 rows=10556080 width=136) (actual time=11477.245..11477.245 rows=1 loops=1)
        Sort Key: summa
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Seq Scan on t1  (cost=0.00..253262.80 rows=10556080 width=136) (actual time=0.008..5866.153 rows=10556080 loops=1)
Total runtime: 11477.302 ms

[VVit1]

А нельзя подробней раскрыть тему дерева AST у постгреса?
Нет ли в природе каких изысканий на тему чтобы с клиента задавать сразу готовое дерево такого запроса сериализованного каким-либо образом. Простая его распаковка на СУБД кроме некоторого ускорения за счет исключения процедуры парсинга еще может дать некоторое упрощение процедуры составления запроса в случае механической генерации кода SQL и повышения гибкости, т.к. процесс генерации такого «человекочитаемого» SQL для обращения к СУБД это прокрустово ложе с весьма ограниченными выразительными способностями относительно того что можно сравнительно просто представить прямо в ASТ.

[rdruzyagin]

Для раскрытия темы нужен целый отдельный цикл материалов, думается мне :)
А вообще, это еще одна затея из разряда как творчески выстрелить себе в ногу. И базе данных тоже заодно. Насколько мне известно, еще никто не придумал решений на тему передачи AST напрямую в СУБД. Дерево тоже ведь надо кодировать в какой-то формат для передачи и на принимающей стороне (внутри СУБД) преобразовывать во внутренние структуры языка Си, которые используются для хранения деревьев. Это тоже накладные расходы. Фактически — обучить базу еще одной формальной грамматике. Полученное AST все равно надо гонять через реврайт и оптимизатор, т.к. не факт что ваше нагенеренное дерево будет высокопроизводительным вариантом.

В общем, все это — очень большие заморочки с неоднозначным «профитом».

[VVit1]

Ага. Ок. Пасибо. Действительно, работы дофига а польза неочевидна.

[robert_ayrapetyan]

Поверьте, это абсолютно бесполезная затея. Как раз SQL и есть «человекочитаемое» с «выразительными способностями», дерево AST без спец.знаний не построишь. С точки зрения экономии ресурсов — вы не сэкономите на спичках, вся фаза до execution редко занимает более пары мс.

[VVit1]

Мысль была даже не в экономии на спичках, а что часто приходится заниматься «ручной» генерацией кода SQL по каким либо действиям пользователя из ГУЯ.
В этом случае конструкции SQL быстро начинают становиться многоэтажными а средств для выражения каких-то действий катастрофически не хватать, т.е. так ты как-бы понимаешь, что выполнить ту плюшку при вот таком условии для СУБД не проблема, просто в SQL нет синтаксиса для их совместного употребления.
Но даже немного «обозрев» сложность парсера SQL быстро приходишь к выводу что построение такой «альтернативной» системы передачи команд… действительно имеет мало шансов быть реализованной во что-нить полезное и работающее.

[rdruzyagin]

Так сложность в том, что вам не хватает выразительности SQL или средств, генерирующих итоговый SQL?

В первое мне трудно поверить, имея за плечами многолетний опыт разгребания многоэтажных «портянок», в которых порой такую неочевидную ахинею умудряются записать, что просто поражаешься)

[Ivan22]

А еще я представил себе DBA, такого сидящего в своей уютной каморке, попивающего спирт чай и смотрящего в логи профайлера!!! для отлова запросов тяжело грузящих его сервак. И что. Что он увидит??? Дерево AST?

[AlexeyTokar]

Очень странно, что Постгрес не использует оптимизацию по отсечению результатов с limit при сканировании, что выглядит логично.
MySQL, к примеру, использует: http://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html

[AlexeyTokar]

Вот еще интересные наблюдения, как LIMIT 1 наоборот убивает производительность: http://datamangling.com/2014/01/17/limit-1-and-performance-in-a-postgres-query/

[Ivan22]

Постгрес использует оптимизацию по отсечению результатов с limit при сканировании

[AlexeyTokar]

из статьи это не очевидно (точнее очевино как раз обратное)

[Sergi0]

поиск идёт по имени, а выборка по id, при этом гарантий, что первая же встреченная запись будет иметь минимальный id — нет.

[Sergi0]

промахнулся уровнем :)