Comments 24
Спасибо за отличный материал. Без понимания того, как оно работает изнутри не получится полностью оптимизировать свою бд. Жду продолжения.
Почему при анализе и перезаписе он не выкинул сортировку, как явно не нужную при limit 1
Ответ кроется в следующем абзаце:
Весь блок данных будет отсортирован, а потом Limit из него уже будет подтягивать нужное количество. Сортировка не является ненужной, т.к. в посгресе таблицы не имеют какого-то неявного по-умолчанию установленного порядка хранения записей.
Как видите, Sort функционирует совсем не так, как Limit. Он сразу загружает все доступные данные из субплана в буфер прежде, чем что-либо возвращать. Затем он сортирует буфер с помощью алгоритма Quicksort и, наконец, возвращает первое отсортированное значение.
Весь блок данных будет отсортирован, а потом Limit из него уже будет подтягивать нужное количество. Сортировка не является ненужной, т.к. в посгресе таблицы не имеют какого-то неявного по-умолчанию установленного порядка хранения записей.
да, извиняюсь, поспешил не учел, что могут быть другие записи с таким именем и нам нужно вернуть именно с наименьшим id
Кстати, а что будет быстрее (правильнее)
классическое
классическое
select * from table where id = (select max(id) from table)
илиselect * from table order by id limit 1
Кстате мне тоже интересно) Буду благодарен за ответ!
Я думаю что второй вариант, т.к. при наличии индекса у вас будет единичный index scan.
В первом случае, вам надо сделать index scan для поиска максимального элемента во вложенном запросе, а потом еще index lookup для выборки нужного ряда.
В первом случае, вам надо сделать index scan для поиска максимального элемента во вложенном запросе, а потом еще index lookup для выборки нужного ряда.
Второй вариант ещё удобен при наличии громоздких условий.
Завтра смогу протестировать на разных (больших) таблицах, если кому интересно, с индексами и без.
Завтра смогу протестировать на разных (больших) таблицах, если кому интересно, с индексами и без.
Итак, две таблицы
t1 — 10556080 записей
t2 — 5864255 записей
1) Индексированные поля
Засечь разницу не удалось, выполняется моментально
2) Неиндексированные поля
Прирост в ~50%
Логика понятна.
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%
Логика понятна.
Explain analyze более показателен будет в данном случае, я думаю. «Голое» время выполнения мало о чем говорит.
Да, конечно. В том же порядке
1) С индексом
2) без
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
А нельзя подробней раскрыть тему дерева AST у постгреса?
Нет ли в природе каких изысканий на тему чтобы с клиента задавать сразу готовое дерево такого запроса сериализованного каким-либо образом. Простая его распаковка на СУБД кроме некоторого ускорения за счет исключения процедуры парсинга еще может дать некоторое упрощение процедуры составления запроса в случае механической генерации кода SQL и повышения гибкости, т.к. процесс генерации такого «человекочитаемого» SQL для обращения к СУБД это прокрустово ложе с весьма ограниченными выразительными способностями относительно того что можно сравнительно просто представить прямо в ASТ.
Нет ли в природе каких изысканий на тему чтобы с клиента задавать сразу готовое дерево такого запроса сериализованного каким-либо образом. Простая его распаковка на СУБД кроме некоторого ускорения за счет исключения процедуры парсинга еще может дать некоторое упрощение процедуры составления запроса в случае механической генерации кода SQL и повышения гибкости, т.к. процесс генерации такого «человекочитаемого» SQL для обращения к СУБД это прокрустово ложе с весьма ограниченными выразительными способностями относительно того что можно сравнительно просто представить прямо в ASТ.
Для раскрытия темы нужен целый отдельный цикл материалов, думается мне :)
А вообще, это еще одна затея из разряда как творчески выстрелить себе в ногу. И базе данных тоже заодно. Насколько мне известно, еще никто не придумал решений на тему передачи AST напрямую в СУБД. Дерево тоже ведь надо кодировать в какой-то формат для передачи и на принимающей стороне (внутри СУБД) преобразовывать во внутренние структуры языка Си, которые используются для хранения деревьев. Это тоже накладные расходы. Фактически — обучить базу еще одной формальной грамматике. Полученное AST все равно надо гонять через реврайт и оптимизатор, т.к. не факт что ваше нагенеренное дерево будет высокопроизводительным вариантом.
В общем, все это — очень большие заморочки с неоднозначным «профитом».
А вообще, это еще одна затея из разряда как творчески выстрелить себе в ногу. И базе данных тоже заодно. Насколько мне известно, еще никто не придумал решений на тему передачи AST напрямую в СУБД. Дерево тоже ведь надо кодировать в какой-то формат для передачи и на принимающей стороне (внутри СУБД) преобразовывать во внутренние структуры языка Си, которые используются для хранения деревьев. Это тоже накладные расходы. Фактически — обучить базу еще одной формальной грамматике. Полученное AST все равно надо гонять через реврайт и оптимизатор, т.к. не факт что ваше нагенеренное дерево будет высокопроизводительным вариантом.
В общем, все это — очень большие заморочки с неоднозначным «профитом».
Поверьте, это абсолютно бесполезная затея. Как раз SQL и есть «человекочитаемое» с «выразительными способностями», дерево AST без спец.знаний не построишь. С точки зрения экономии ресурсов — вы не сэкономите на спичках, вся фаза до execution редко занимает более пары мс.
Мысль была даже не в экономии на спичках, а что часто приходится заниматься «ручной» генерацией кода SQL по каким либо действиям пользователя из ГУЯ.
В этом случае конструкции SQL быстро начинают становиться многоэтажными а средств для выражения каких-то действий катастрофически не хватать, т.е. так ты как-бы понимаешь, что выполнить ту плюшку при вот таком условии для СУБД не проблема, просто в SQL нет синтаксиса для их совместного употребления.
Но даже немного «обозрев» сложность парсера SQL быстро приходишь к выводу что построение такой «альтернативной» системы передачи команд… действительно имеет мало шансов быть реализованной во что-нить полезное и работающее.
В этом случае конструкции SQL быстро начинают становиться многоэтажными а средств для выражения каких-то действий катастрофически не хватать, т.е. так ты как-бы понимаешь, что выполнить ту плюшку при вот таком условии для СУБД не проблема, просто в SQL нет синтаксиса для их совместного употребления.
Но даже немного «обозрев» сложность парсера SQL быстро приходишь к выводу что построение такой «альтернативной» системы передачи команд… действительно имеет мало шансов быть реализованной во что-нить полезное и работающее.
Очень странно, что Постгрес не использует оптимизацию по отсечению результатов с limit при сканировании, что выглядит логично.
MySQL, к примеру, использует: http://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html
MySQL, к примеру, использует: http://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html
Sign up to leave a comment.
Путешествие запроса Select через внутренности Постгреса