индексы разные, да второй индекс вроде даже более селективный судя по плану и идет index only скан, а на деле он просто умирает на запросе

всем привет, что посоветуете почитать для освоения сложных select с join, я сейчас попал на проект с аналитикой metabase + postgres

Селекты с джойнами - это не сложно. Официальной документации вполне себе хватает. Суровая аналитика без CTE - это как-то вот в голове плохо укладывается. А CTE - это искать в гугле всю и всяческую оптимизацию запросов от Максима Богука и Ивана Фролкова.

кроме составных индексов на этой таблице есть еще отдельные индексы на uid, отдельные на bar_number и lower(bar_number)

Селекты с джойнами - это не сложно. Официальной документации вполне себе хватает. Суровая аналитика без CTE - это как-то вот в голове плохо укладывается. А CTE - это искать в гугле всю и всяческую оптимизацию запросов от Максима Богука и Ивана Фролкова.

Понял спасибо

У меня структура: 50 ГБ данных. Хочу разбить их помесячно. Ключ - число (unix_timestamp)

А зачем?

А зачем?

что зачем?

что зачем?

Партиционировать Вам зачем?

на будущее, чтобы шустрее работало

на будущее, чтобы шустрее работало

А с чего Вы взяли, что будет быстрее работать?

А зачем?

У человека 2Гб оперативы на виртуалке. Вот он и изощряется, чтобы такого придумать...

поиск впределах маленькой партиции быстрее работает, чем в толстой таблице

да там вроде бы больше и негде. Ладно спасибо, будем думать.

А почему Вы вообще rule используете?

поиск впределах маленькой партиции быстрее работает, чем в толстой таблице

Если у Вас вообще индексов нет —- да, быстрее. Но почему бы их просто не создать?

дык я там после написал: 16ГБ данных, а индексов на 53ГБ

дык я там после написал: 16ГБ данных, а индексов на 53ГБ

А Вы что думате, после партиционирования меньше будет?

А почему Вы вообще rule используете?

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

@dmitriy_vasilyev приключения - это здорово. Но быстро надоедает. Поэтому рекомендация в вашем случае правильная только одна - нарастить оперативы. Партиционировать 50 гб - это вот как-то не совсем понятно. Ну и когда данных 16 Гб, а индексов - 53 - это вызывает много вопросов.

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

Rule лучше вообще не использовать, никогда. См. http://blog.rhodiumtoad.org.uk/2010/06/21/the-rule-challenge/

@dmitriy_vasilyev приключения - это здорово. Но быстро надоедает. Поэтому рекомендация в вашем случае правильная только одна - нарастить оперативы. Партиционировать 50 гб - это вот как-то не совсем понятно. Ну и когда данных 16 Гб, а индексов - 53 - это вызывает много вопросов.

несколько жирных составных индексов там.

несколько жирных составных индексов там.

Если они были нужны и "исчезнут" после партиционирования, производительность исчезнет вместе с ними, Вы это понимаете? ;)

да

Занчит надо разбираться, что за индексы, и где они используются. Ибо с таким соотношением любой запрос будет тормозить на этапе планирования. А ещё у вас наверняка весёлое веселье при любом изменении данных.

Rule лучше вообще не использовать, никогда. См. http://blog.rhodiumtoad.org.uk/2010/06/21/the-rule-challenge/

спасибо прочел, буду иметь ввиду.

да

Тогда, опять-таки, зачем партиционировать? По сравнению с корректной индексацией никакого выигрыша, скорее всего, не будет. А вот проиграть можно запросто.

Занчит надо разбираться, что за индексы, и где они используются. Ибо с таким соотношением любой запрос будет тормозить на этапе планирования. А ещё у вас наверняка весёлое веселье при любом изменении данных.

Ну собственно эти индексы и сделаны для запросов. Все норм работает. Просто за счет партицирования хочу, что бы искались значения не в толстой таблице, а в маленьком кусочк.

На партиции нормально же индексы вешаются, не?

И в таблицах много insert и редко делаются селекты

иногда delete делается

@dmitriy_vasilyev на Ваших объёмах любое партиционирование будет увеличивать количество проблем.

это данные за пол года.

А копить и хранить нужно около 5-6

Ну собственно эти индексы и сделаны для запросов. Все норм работает. Просто за счет партицирования хочу, что бы искались значения не в толстой таблице, а в маленьком кусочк.

Ещё раз: почему Вы думате, что будет лучше? Вы, кстати, знаете про ограничения, т.е. хотя бы то, что дата/даты должны быть константами в запросе?

На партиции нормально же индексы вешаются, не?

Да. Но "общий" UNIQUE INDEX Вы уже не сделаете, например.

а мне uniq не нужен

16*6 = 96 Не, не надо такие объёмы партиционировать. А вот с индексами разбираться надо. Потому что реальная ситуация, когда размер индекса сопоставим с объёмом данных - это на индексах для полнотекстового поиска. Но чтобы объём индекса более чем в 3+ раза превышал объём данных... Не, в голове укладывается плохо.

Да. Но "общий" UNIQUE INDEX Вы уже не сделаете, например.

Общий индекс вообще не сделаете - глобальный и это самый большой недостаток партиционирования

а мне uniq не нужен

Я просто хотел Вас предупредить, что Вы можете просто потратить много времени (на партиционирование и его тестирование) абсолютно впустую (т.е. в конце убедиться, что стало только хуже).

16*6 = 96 Не, не надо такие объёмы партиционировать. А вот с индексами разбираться надо. Потому что реальная ситуация, когда размер индекса сопоставим с объёмом данных - это на индексах для полнотекстового поиска. Но чтобы объём индекса более чем в 3+ раза превышал объём данных... Не, в голове укладывается плохо.

Просто индексов там много, как я понял.

Просто индексов там много, как я понял.

Их там не много. Их там чудовищно много.

Их там не много. Их там чудовищно много.

Тут надо видеть таблицу - может узкая и с большим числом уникальных значений

а мне uniq не нужен

А можете \d таблицы показать, в самом деле?

Тут надо видеть таблицу - может узкая и с большим числом уникальных значений

Видеть одну таблицу - сильно мало. Надо ещё знать задачи. И товарищ уже говорил, что многоразвесистых составных индексов.

А можете \d таблицы показать, в самом деле?

CREATE TABLE public.flowlog ( timestart integer, timeend integer, insideip inet, outsideip inet, startport integer, endport integer )

CREATE TABLE public.flowlog ( timestart integer, timeend integer, insideip inet, outsideip inet, startport integer, endport integer )

Это не \d. Интересуют именно индексы, ясное дело. ;)

Это не \d. Интересуют именно индексы, ясное дело. ;)

CREATE INDEX stat_inside_ip_idx ON public.flowlog USING btree (insideip, timestart, timeend); — Index: public.stat_out_ip_idx — DROP INDEX public.stat_out_ip_idx; CREATE INDEX stat_out_ip_idx ON public.flowlog USING btree (outsideip, timestart, timeend);

CREATE INDEX active_idx ON public.flowlog USING btree (timeend);

вот 3 индекса

вот 3 индекса

И всё, только 3? Кстати, почему у Вас "timestart integer," а не timestamptz?

исторически сложилось, дату хранить в unix формате

Уникальность значений высокая... в составных индексах я думаю вообще каждая строка уникальна

-+ да, каждая

исторически сложилось, дату хранить в unix формате

Ну ладно, а по какому полю Вы её думали партиционировать? По timeend? Если так, то, судя по другим индексам, у Вас как раз вариант "станет только хуже".

по timestart

Что-то мне подсказывает, что timeend из составных индексов надо попробовать убрать при наличии отдельного индекса по этому полю. Может я и не прав, утверждать не буду. Но я бы попробовал.

в условиях запросов используются все 3 поля

по timestart

На первый взгляд (судя по индексам) станет хуже (или же эти индексы к Вашим запросам не очень подходят).

сейчас все работает нормально, шустро ищет все, что нужно

в условиях запросов используются все 3 поля

Можете примеры запросов показать?

сейчас все работает нормально, шустро ищет все, что нужно

Ну станет менее шустро. ;)

Что-то мне подсказывает, что timeend из составных индексов надо попробовать убрать при наличии отдельного индекса по этому полю. Может я и не прав, утверждать не буду. Но я бы попробовал.

Сейчас у него indexonly scan

select to_timestamp(timestart)::timestamp without time zone as timestart,to_timestamp(max(timeend))::timestamp without time zone as timeend,insideip as internalip,outsideip as externalip,startport,endport from flowlog where (timeend = 0 or timeend >= $tf) and timestart <= $tt and $sideIP = '$ip'::inet group by timestart, insideip, outsideip, startport, endport " order by timestart,insideip,outsideip

Сейчас у него indexonly scan

Как видите, нет.

$ip = это ip $tf = time from (unix) $tt = time to (unix) $side = insideip / outsideip

Как видите, нет.

Да. Вижу. Секционирование при высокой уникальности значений думаю не поможет - у вас и так каждый индекс указывает на 1 строку таблицы - зачем?

Сорри поглядел запрос )

Привет всем, есть такой вопрос как посчитать максимально количество коннектов к pgbouncer, от каких параметров это зависит?

select to_timestamp(timestart)::timestamp without time zone as timestart,to_timestamp(max(timeend))::timestamp without time zone as timeend,insideip as internalip,outsideip as externalip,startport,endport from flowlog where (timeend = 0 or timeend >= $tf) and timestart <= $tt and $sideIP = '$ip'::inet group by timestart, insideip, outsideip, startport, endport " order by timestart,insideip,outsideip

Для выполнения этого запроса придётся проверить все partitions, где timestart меньше заданного, и в каждой произвести поиск по индексу. Если $tt у Вас обычно ближе к "концу", то почти все. Даже теоретически, Вы заменяете одно сканирование индекса (по insideip/outsideip и timestart) на K сканирований (где K —- кол-во partitions). Плюс ещё время планирования. Т.е. запрос станет (значительно?) менее шустрым. ;)

Хорошо, спасибо за разъяснения

пускай будет толстая таблица, жить не мешает

CREATE TABLE public.flowlog ( timestart integer, timeend integer, insideip inet, outsideip inet, startport integer, endport integer )

А какой первичный ключ на таблице?

Если данные более или менее равномерно распределены по времени, можно разбить на партиции по timestart. Но тогда каждый запрос должен содержать условие, чтобы захватить конкретную партицию. При таком подходе я вижу больше плюсов, чем минусов. Во-первых, сами индексы будут меньше - они могут целиком поместиться в кеш. Ну это при услови, что есть данные, попадающие в интервал времени, с котороыми вы чаще работаете, чем с другими. Во-вторых, таблицы будет проще обслуживать - быстрее создавать индексы, делать ALTER и так далее. В-третьих - VACUUM. Он хотя бы сможет отработать на маленьких таблицах.

Добрый день как с эмулировать тип set mysql https://dev.mysql.com/doc/refman/5.7/en/set-statement.html

только ссылка не про тип set

(insideip, timestart, timeend); по-моему, timeend - в этом индексе только место занимает

Если данные более или менее равномерно распределены по времени, можно разбить на партиции по timestart. Но тогда каждый запрос должен содержать условие, чтобы захватить конкретную партицию. При таком подходе я вижу больше плюсов, чем минусов. Во-первых, сами индексы будут меньше - они могут целиком поместиться в кеш. Ну это при услови, что есть данные, попадающие в интервал времени, с котороыми вы чаще работаете, чем с другими. Во-вторых, таблицы будет проще обслуживать - быстрее создавать индексы, делать ALTER и так далее. В-третьих - VACUUM. Он хотя бы сможет отработать на маленьких таблицах.

Вы пример запроса видели? Нет там условия для конкретной partition, он захватывает несколько (а, возмжно, почти все). :(

если вы с timestart не равенством сравниваете, timeend не используется никогда

Если данные более или менее равномерно распределены по времени, можно разбить на партиции по timestart. Но тогда каждый запрос должен содержать условие, чтобы захватить конкретную партицию. При таком подходе я вижу больше плюсов, чем минусов. Во-первых, сами индексы будут меньше - они могут целиком поместиться в кеш. Ну это при услови, что есть данные, попадающие в интервал времени, с котороыми вы чаще работаете, чем с другими. Во-вторых, таблицы будет проще обслуживать - быстрее создавать индексы, делать ALTER и так далее. В-третьих - VACUUM. Он хотя бы сможет отработать на маленьких таблицах.

Я в свое время секционировал таблицы zabbix (по itemid) как раз с целью сокращения времени vacuum (особенно autovacuum), и там в каждом запросе содержится 1 либо несколько конкретных itemid - как раз под ваши рекомендации. Профит есть

Я в свое время секционировал таблицы zabbix (по itemid) как раз с целью сокращения времени vacuum (особенно autovacuum), и там в каждом запросе содержится 1 либо несколько конкретных itemid - как раз под ваши рекомендации. Профит есть

"В своё время" —- это до 9.6? Сейчас это, к счастью, уже не так актуально...

Я делал на 9.6, сейчас там уже 10ка... но по-прежнему pg_pathman

А какой первичный ключ на таблице?

нет его

Если данные более или менее равномерно распределены по времени, можно разбить на партиции по timestart. Но тогда каждый запрос должен содержать условие, чтобы захватить конкретную партицию. При таком подходе я вижу больше плюсов, чем минусов. Во-первых, сами индексы будут меньше - они могут целиком поместиться в кеш. Ну это при услови, что есть данные, попадающие в интервал времени, с котороыми вы чаще работаете, чем с другими. Во-вторых, таблицы будет проще обслуживать - быстрее создавать индексы, делать ALTER и так далее. В-третьих - VACUUM. Он хотя бы сможет отработать на маленьких таблицах.

там запросы максимум за сутки

если вы с timestart не равенством сравниваете, timeend не используется никогда

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

Вы пример запроса видели? Нет там условия для конкретной partition, он захватывает несколько (а, возмжно, почти все). :(

Видел, из него не совсем понятно, какова селективность. Просто я не могу поверить, что где-то может существовать большая таблица которую нужно постоянно целиком сканировать. В таком случае надо пересмотреть немного архикектуру. Может, агрегировать что-то предварительно.

Видел, из него не совсем понятно, какова селективность. Просто я не могу поверить, что где-то может существовать большая таблица которую нужно постоянно целиком сканировать. В таком случае надо пересмотреть немного архикектуру. Может, агрегировать что-то предварительно.

Это же от индексации зависит... И, несмотря на это, запросто может существовать (т.е. случаи, когда индексы не помогают —- не сокращают минимально необходимый объём выборки), и что-то для оптимизации этого сделать довольно нетривиально. :(

Я планировал помесячно разбить. Выборки максмум за сутки, чаще всего в пределах часа-двух

Подскажите, есть у кого словать для Thesaurus чтобы сделать поиск по синонимам?

Я планировал помесячно разбить. Выборки максмум за сутки, чаще всего в пределах часа-двух

В Вашем примере запроса условия-то не такие... Выбираются все записи с timestart <= $tt .

Задача такакя: дается ip и период. Мне нужны все записи, которые попадают в заданный интвервал. timestart - timeend. И при этом timeend может быть = 0, типа сессия активна

Задача такакя: дается ip и период. Мне нужны все записи, которые попадают в заданный интвервал. timestart - timeend. И при этом timeend может быть = 0, типа сессия активна

Поздравляю ). Партиции вам могут помочь. Делаете range partitions по timestart, внутри индекс (timestart, ip) и вперёд.

Поздравляю ). Партиции вам могут помочь. Делаете range partitions по timestart, внутри индекс (timestart, ip) и вперёд.

А по-моему, не могут. Покажете, как?

Задача такакя: дается ip и период. Мне нужны все записи, которые попадают в заданный интвервал. timestart - timeend. И при этом timeend может быть = 0, типа сессия активна

Хмм... я почти уверен, что Ваши запросы на самом деле "спасает" наличие AND $sideIP = '$ip'::inet и соотвествующий индекс по нему (т.е. то, что записей по одному IP в среднем немного), а не что-то другое.

Задача такакя: дается ip и период. Мне нужны все записи, которые попадают в заданный интвервал. timestart - timeend. И при этом timeend может быть = 0, типа сессия активна

Впрочем, если хотите оптимизировать ещё, начинать нужно с планов. ;)

Ну пока работает

я думал что дальше делать, если начнет тупить, еще раз тут спрошу, может пг 11 уже будет))

я думал что дальше делать, если начнет тупить, еще раз тут спрошу, может пг 11 уже будет))

И чем 11 версия Вам поможет? ;)

тут выше уповали на нее

типа pg_pathman не нужен будет

и заживем