@misterjulian будет жить. Поприветствуем!

Добрый день! Подскажите по следующему вопросу Вчера начал пользоваться psql, и столкнулся, что, например, если некорректно ввести, к примеру, в запросе инсерт инто(name) и вот к примеру 'qwe (без закрывающей) то консоль перестает на что дибо реагировать в т ч и на ; . Как решить проблему без перезапуска?

Команда sql должна заканчиваться точкой с запятой (;)

А скобку можно закрыть и в новой строке

Спасибо разобрадся

Привет. Посоветуйте, пожалуйста, книжку по продвинутой работе с SQL (Любой диалект). Где подробно рассказано про уровни изоляции транзакций, кластерные индексы, хранимые процедуры, блокировки и иже с ними. Что-нибудь популярное в духе O'Reilly.

Вот это всё: уровни изоляции, кластерные индексы, хранимые процедуры и прочие блокировки являются сугубо продуктозависимыми вещами, поэтому - только официальная документация по конкретной СУБД.

Вот это всё: уровни изоляции, кластерные индексы, хранимые процедуры и прочие блокировки являются сугубо продуктозависимыми вещами, поэтому - только официальная документация по конкретной СУБД.

По T-SQL пару книжечек подыскал. Плюс посмотрел по вашему совету у Постгреса в доках всё вроде лаконично и по делу с примерами. Спасибо)

Алтынбек Шарипов будет жить. Поприветствуем!

@pensnarik будет жить. Поприветствуем!

Помогите понять планировщик. Есть таблица, индекс по created_at (DESC) (timestamptz). Строки отсекаются по дате. Если в запросе присутствует фильтр по полю STATUS (по нему есть индекс, но он не используется), то запрос выполняется медленно (90% строк имеют статус 'domestic_delivered'). Если условия по STATUS нет, то быстро. Причём если условие на неравенство, то запрос также работает быстро. План медленного запроса: https://explain.depesz.com/s/uAK План быстрого запроса: https://explain.depesz.com/s/N9YO Медленный запрос: explain (analyze, verbose, buffers) SELECT packages.created_at FROM "packages" WHERE (packages.status = 'domestic_delivered') AND (packages.created_at >= '2018-08-29 17:00:00+00') ORDER BY packages.created_at DESC LIMIT 50 Быстрый запрос: explain (analyze, verbose, buffers) SELECT packages.created_at FROM "packages" WHERE (packages.created_at >= '2018-08-29 17:00:00+00') ORDER BY packages.created_at DESC LIMIT 50

в быстром запросе происходит только обращение к индексу, и оно все читает из кэша (buffers hit), к диску даже не обращается. в то же время в медленном, чтобы проверить дополнительное условие, кроме индекса, базе нужно обращаться к таблице, а это уже медленнее и с промахом в кэше (buffers read).

Как тогда объяснить тот факт, что с условием на != запрос также работает быстро?

Ведь ему всё равно надо так же проверять все записи, которые он достал по индексу.

Limit (cost=0.57..585.61 rows=50 width=8) (actual time=0.017..0.316 rows=50 loops=1) Output: created_at Buffers: shared hit=132 -> Index Scan using package_created_at_desc_index on public.packages (cost=0.57..593110.08 rows=50690 width=8) (actual time=0.015..0.290 rows=50 loops=1) Output: created_at Index Cond: (packages.created_at >= '2018-08-29 17:00:00+00'::timestamp with time zone) Filter: ((packages.status)::text <> 'domestic_delivered'::text) Buffers: shared hit=132 Planning time: 0.234 ms Execution time: 0.344 ms

там же index scan и index only scan воюют

в индекс надо поле status добавить, будет быстрее

Ох ты ж ёлки.

Хотя нет. При != тоже просто index scan, но работает быстро.

Ох ты ж ёлки.

А зачем у Вас такой индекс "using package_created_at_desc_index", кстати?

Limit (cost=0.57..585.61 rows=50 width=8) (actual time=0.017..0.316 rows=50 loops=1) Output: created_at Buffers: shared hit=132 -> Index Scan using package_created_at_desc_index on public.packages (cost=0.57..593110.08 rows=50690 width=8) (actual time=0.015..0.290 rows=50 loops=1) Output: created_at Index Cond: (packages.created_at >= '2018-08-29 17:00:00+00'::timestamp with time zone) Filter: ((packages.status)::text <> 'domestic_delivered'::text) Buffers: shared hit=132 Planning time: 0.234 ms Execution time: 0.344 ms

а где в этом плане Rows removed by filter?

Нету. Видимо, потому что 0?

А зачем у Вас такой индекс "using package_created_at_desc_index", кстати?

Такой создали. Думаете, тут имеет значение в какую сторону отсортирован индекс? P.S. Если в запросе сортировать ASC, он тоже будет быстро работать.

Buffers: shared hit=49277 read=179172 dirtied=58358

Нету. Видимо, потому что 0?

Да. Поэтому производительность и отличается. Сравните с: Rows Removed by Filter: 129574 у медленного запроса.

Buffers: shared hit=49277 read=179172 dirtied=58358

Да. В этом проблема. Но почему он читает только для проверки условия на =?

тут написано, что 58358 было испачкано

тут написано, что 58358 было испачкано

Но как?

попробуйте ещё раз этот запрос выполнить

просто при условии <> он быстро находит 50 записей себе и успокаивается

Такой создали. Думаете, тут имеет значение в какую сторону отсортирован индекс? P.S. Если в запросе сортировать ASC, он тоже будет быстро работать.

Нет, не имеет, в том-то и дело. Я просто удивился, ожидая увидеть "Index Scan Backward" в плане, а потом обратил внимание на название. :)

Но как?

может быть, никто никогда не читал эту таблицу и в ней хинты пришлось расставлять

Нет, не имеет, в том-то и дело. Я просто удивился, ожидая увидеть "Index Scan Backward" в плане, а потом обратил внимание на название. :)

С Index Scan Backward почему-то тоже всё летает.

может быть, никто никогда не читал эту таблицу и в ней хинты пришлось расставлять

Да, думаю что dirtied из-за hint bits. Но таблицу читают постоянно.

С Index Scan Backward почему-то тоже всё летает.

Ещё раз: это не имеет значения. ;) Просто создавать одноколоночные индексы с DESC необычно (ненужно).

а второй раз этот запрос столько же исполняется?

Да, думаю что dirtied из-за hint bits. Но таблицу читают постоянно.

А между тем Denis Girko уже дал правильный ответ. :)

а второй раз этот запрос столько же исполняется?

Да.

просто при условии <> он быстро находит 50 записей себе и успокаивается

Так наоборот если 90% записей имеют этот статус он должен дольше искать записи с отличным статусом.

Так наоборот если 90% записей имеют этот статус он должен дольше искать записи с отличным статусом.

На самом деле — нет, не должен. Вы, кстати, сейчас демонстрируете ту же "наивную" логику, что и планировщик PostgreSQL. ;)

Ну, хорошо. Не "должен", а "с большей вероятностью" )

Жаль, что нельзя trace включить как в Oracle.

кстати, а у вас уменьшилось число строк в таблице по сравнению с предыдущими запросами? или ANALYZE выполнился? там было rows=402913, а в последнем запросе в 8 раз меньше

Ну, хорошо. Не "должен", а "с большей вероятностью" )

Ну... как сказать. Вы (как и планировщик) считате, что в реальной жизни значения полей распределены равномерно и независимо. Но почему это наиболее вероятно-то? ;) Планировщик так считает, кстати, просто потому, что при отсутсвии дополнительной информации... попробуйте-ка придумать существенно лучшее предположение (а это самое простое). :(

кстати, а у вас уменьшилось число строк в таблице по сравнению с предыдущими запросами? или ANALYZE выполнился? там было rows=402913, а в последнем запросе в 8 раз меньше

Нет.

Здравствуйте! 15 сентября в московском офисе Авито пройдёт большой митап сообщества #RuPostgres. Регистрируйтесь и приходите: https://avitotech.timepad.ru/event/798808/?utm_refcode=fe260431fcd15def2d9306530800bee345d15775 Спасибо!

Нет.

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

Господа, а не подскажите, можно ли результат оконной функции использовать в групировке GROUP BY?

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

У обычных индексов нет никакой полезной статистики (и даже та мелочь, что есть (для частичных индексов), практически игнорируется планировщиком). Полезная, используемая планировщиком статистика есть только у expressional index.

А как объясните, что планировщик пишет разное прогнозируемое чтсло строк? (Смотрели эксплейны по ссылкам?)

А как объясните, что планировщик пишет разное прогнозируемое чтсло строк? (Смотрели эксплейны по ссылкам?)

Эээ... запросы-то все разные, вообще-то. Т.е. всё считается, исходя из: 1. Обычных статистик (MCV и гистограмм), для каждого поля. 2. Предположения независимости распределения.

А, ок. 1-й пункт не был для меня очевиден. Я думал, что статистика собирается только по индексируемым полям.

А, ок. 1-й пункт не был для меня очевиден. Я думал, что статистика собирается только по индексируемым полям.

Нет, ровно наоборот — по всем (по умолчанию). :)

не подскажите, можно ли как-то без подзапроса сделать ? select group_id, min(id) as id FROM (select id, group_id, row_number() over (ORDER BY ID)- row_number() over (partition by group_id order by id) as gr from users) as tb group by group_id, gr order by id

https://www.db-fiddle.com/f/byav3cdfcuc2vG2gDFnGwP/0

https://www.db-fiddle.com/f/byav3cdfcuc2vG2gDFnGwP/0

(Для верности) А какой Вы хотите получить результат (в чём цель запроса)?

(Для верности) А какой Вы хотите получить результат (в чём цель запроса)?

вычислить минимальный ID записи в группе

вычислить минимальный ID записи в группе

А получаете минимальные id "островов" последовательных id в группах, кажется... Так что Вам всё-таки нужно? ;)

А получаете минимальные id "островов" последовательных id в группах, кажется... Так что Вам всё-таки нужно? ;)

Ну Вы все правильно написали получить минимальное id, то это можно как-то сделать без подзапроса, или без вариантов?

Ребят, вопрос. Допустим, есть автоинкремент SERIAL, делается невалидный из-за NOT NULL инсерт, но автоинкремент все равно добавляет значение, и при следующем валидном инсерте первый кортеж уже идет с id 2. Как этого избежать?

Ребят, вопрос. Допустим, есть автоинкремент SERIAL, делается невалидный из-за NOT NULL инсерт, но автоинкремент все равно добавляет значение, и при следующем валидном инсерте первый кортеж уже идет с id 2. Как этого избежать?

Сначала проверять на валидность а инсертить потом

спасибо. какой для этого оператор? если ответ пару слов напишите плиз чтоб не искать в документации. первыф день прост БД учу

Ну Вы все правильно написали получить минимальное id, то это можно как-то сделать без подзапроса, или без вариантов?

А мне вот, например, кажется, что я неправильно написал. ;) (Пример: insert into public.users(id, group_id) VALUES (1, 1), (2, 1), (3, 2), (3, 3), (4, 3), (6, 3), (7, 3); ) По тому, как я понял, я ожидал другого результата. А пока непонятно, что запрос делает, подсказывать трудно. :( Кстати, почему Вам важно, есть ли вложенный запрос или нет?

Так это сложный философский вопрос кому доверить проверять валидность. Может клиент на фронте, может средний слой, но может и бд в конце концов

Ребят, вопрос. Допустим, есть автоинкремент SERIAL, делается невалидный из-за NOT NULL инсерт, но автоинкремент все равно добавляет значение, и при следующем валидном инсерте первый кортеж уже идет с id 2. Как этого избежать?

Не вставлять в него "вручную", всего лишь.

А мне вот, например, кажется, что я неправильно написал. ;) (Пример: insert into public.users(id, group_id) VALUES (1, 1), (2, 1), (3, 2), (3, 3), (4, 3), (6, 3), (7, 3); ) По тому, как я понял, я ожидал другого результата. А пока непонятно, что запрос делает, подсказывать трудно. :( Кстати, почему Вам важно, есть ли вложенный запрос или нет?

вот все условие задачи: дана таблица users вида - id, group_id create temp table users(id bigserial, group_id bigint); insert into users(group_id) values (1), (1), (1), (2), (1), (3); 1 В этой таблице, упорядоченной по ID необходимо: 2 выделить непрерывные группы по group_id с учетом указанного порядка записей (их 4) 3 подсчитать количество записей в каждой группе 4 вычислить минимальный ID записи в группе Для таблицы: id | group_id ----+---------- 1 | 1 2 | 1 3 | 1 4 | 2 5 | 1 6 | 3 Непрерывными группами можно считать group_id ---------- 1 2 1 3

Все, понял, спасибо!

Все, понял, спасибо!

правильный совет - не рассчитывайте на последовательные значения

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

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

Да не придётся... Суть тут не в этом, а в том, что у поля, которое генерируется sequence (или identity), есть чёткий смысл — это генератор суррогатных ключей, и всё, что он гарантирует, это то, что они будут уникальными.

вот все условие задачи: дана таблица users вида - id, group_id create temp table users(id bigserial, group_id bigint); insert into users(group_id) values (1), (1), (1), (2), (1), (3); 1 В этой таблице, упорядоченной по ID необходимо: 2 выделить непрерывные группы по group_id с учетом указанного порядка записей (их 4) 3 подсчитать количество записей в каждой группе 4 вычислить минимальный ID записи в группе Для таблицы: id | group_id ----+---------- 1 | 1 2 | 1 3 | 1 4 | 2 5 | 1 6 | 3 Непрерывными группами можно считать group_id ---------- 1 2 1 3

Так почему Вам важно, есть ли вложенный запрос или нет?

Так почему Вам важно, есть ли вложенный запрос или нет?

потому что я минималист, люблю что бы поменьше кода, ищу наиболее короткий вариант )))

потому что я минималист, люблю что бы поменьше кода, ищу наиболее короткий вариант )))

Самый короткий — не значит, что самый лучший. Вы бы лучше понятный другим искали. ;)

потому что я минималист, люблю что бы поменьше кода, ищу наиболее короткий вариант )))

Вот я наоборот сегодня матрешку из трех cte наваял и наехал на тимлида что он комментов не пишет

Самый короткий — не значит, что самый лучший. Вы бы лучше понятный другим искали. ;)

Ладно другие, их не жалко. Сам же через пару месяцев не врубаешься что имел ввиду ?

Ладно другие, их не жалко. Сам же через пару месяцев не врубаешься что имел ввиду ?

зато мозги всегда в тонусе )))

А какой стандартный пароль от юзера постгрес? Хочу подключатся в своей программе к бд.

А какой стандартный пароль от юзера постгрес? Хочу подключатся в своей программе к бд.

postgres

А какой стандартный пароль от юзера постгрес? Хочу подключатся в своей программе к бд.

без пароля

@archi_sova будет жить. Поприветствуем!

Здравствуйте. Скажите есть ли возможность задать пароль для postgres при initdb в переменной окружения или в параметре?

а всё нашёл в документации.

а всё нашёл в документации.

и как же ?

--pwfile=имя_файла Принуждает initdb читать пароль суперпользователя базы данных из файла, первая строка которого используется в качестве пароля.

-W --pwprompt Указывает initdb запросить пароль, который будет назначен суперпользователю базы данных. Это не важно, если не планируется использовать аутентификацию по паролю. В ином случае этот режим аутентификации оказывается неприменимым, пока пароль не задан.

ааа

ну можно было еще такой командой: yes mypassword | initdb -W -D /path/to/db

Это не наши методы (с)