бред, у тебя сортировка Sort Key: messages.y_id не по индексу

покажи все индексы на messages

\d+ messages

schemaname | tablename | indexname | tablespace | indexdef ------------+-----------+--------------------------+------------+---------------------------------------------------------------------------------------------- public | messages | messages_pkey | | CREATE UNIQUE INDEX messages_pkey ON messages USING btree (y_id) public | messages | message_date_created_idx | | CREATE INDEX message_date_created_idx ON messages USING btree (date_created DESC NULLS LAST) (2 rows)

там неупорядоченный хеш? отстреливаем дальше set enable_mergejoin to off;

и так пока не получим интересующий нас план или обнаружим, что он невозможен / тормозит

если он не тормозит, рассматриваем на нём косты

там неупорядоченный хеш? отстреливаем дальше set enable_mergejoin to off;

эмн. y_id определна как строка, там хранится md5 хэш, он мне приходит из сторонней апишки. они все упорядочены, т.е. y_id каждой новой записи будет больше чем предыдущий, если странить построково

упорядочены = в смысле я щас заметил, что они по порядку идут, но в базе у меня никаких фишек нет для этого

cluster messages using messages_pkey;

пусть идут по порядку

хм, всё равно 12 секунд..

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

где план без мержжойна? :)

вангую, что если построите индекс по chats (unread DESC, last_msg_id) или хотя бы (unread DESC), то должно все это превратиться в Nested Loop из 20 итераций, что должно быть существенно быстрее, нужно потрогать руками

https://explain.depesz.com/s/HZqD enable_hashjoin = on, enable_mergejoin = off или надо оба off?

оба off

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

хочется посмотреть в секскан по chats и индексскан по messages с nested loop

вангую, что если построите индекс по chats (unread DESC, last_msg_id) или хотя бы (unread DESC), то должно все это превратиться в Nested Loop из 20 итераций, что должно быть существенно быстрее, нужно потрогать руками

CREATE UNIQUE INDEX chats_pkey ON chats USING btree (y_id) CREATE INDEX ix_chats_date_created ON chats USING btree (date_created) CREATE INDEX chat_unread_idx ON chats USING btree (unread DESC NULLS LAST) CREATE INDEX chat_last_msg_idx ON chats USING btree (last_msg_id) вот такие индексы есть у чатов

а сортируешь в запросе desc, а не desc nulls last

оба off

https://explain.depesz.com/s/5Ipk

а сортируешь в запросе desc, а не desc nulls last

кстати, да

а сортируешь в запросе desc, а не desc nulls last

+

ну ок, но всё равно всё по старому - https://explain.depesz.com/s/TBb

меня сейчас на этом сайте забанят )

explain analyze select * from ( select chats.y_id as chats_y_id, chats.unread as chats_unread from chats join messages on messages.y_id = chats.last_msg_id where chats.unread = true order by messages.date limit 20 ) union all select * from ( select chats.y_id as chats_y_id, chats.unread as chats_unread from messages join chats on messages.y_id = chats.last_msg_id where chats.unread = false order by messages.date limit 20 ) limit 20;

чую разные планы на обе части

chats.unread integer если чо

:)

у вас 65536 разных непрочитываний? :)

да не, раньше просто хотел хранить там счетчик, потом забил и стал использовать просто как флаг

https://explain.depesz.com/s/xyw8

поменяй в первой части join messages on messages.y_id = chats.last_msg_id на join lateral (select * from messages where messages.y_id = chats.last_msg_id) on true

и cluster chats using chat_last_msg_idx; analyse chats; перед тем

(больше гвоздей в план!)

сайдноут, лучше переложи md5 из текста в uuid, тогда у него abbreviated keys sort может заработать

чёто туплю explain analyze select * from ( select chats.y_id as chats_y_id, chats.unread as chats_unread from chats join lateral (select * from messages where messages.y_id = chats.last_msg_id) as Z on true where chats.unread = 1 order by messages.date_created limit 20 ) as X union all select * from ( select chats.y_id as chats_y_id, chats.unread as chats_unread from messages join chats on messages.y_id = chats.last_msg_id where chats.unread = 0 order by messages.date_created limit 20 ) as Y limit 20; ERROR: missing FROM-clause entry for table "messages" LINE 9: order by messages.date_created

сайдноут, лучше переложи md5 из текста в uuid, тогда у него abbreviated keys sort может заработать

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

as messages вместо as Z

вообще, я бы прожёг дату последнего сообщения рядом с last_msg_id, тогда можно было бы делать сортировку в одной таблице

https://explain.depesz.com/s/l1Wx

я уж тоже подумал про это.

join lateral (select * from messages where messages.y_id = chats.last_msg_id offset 0) as messages on true

но и так уже в два раза скостили

если это админ-онли, я бы забил уже на его оптимизацию и смотрел, что там у юзеров

блин, там вообще всё весело. у чата есть пост1 и пост2 (разные таблицы). у обоих типов постов есть аккаунт. у поста1 если рассылка1, у поста2 есть рассылка2. у рассылки1, рассылки2 и аккаунта есть юзер. сейчас я всё это джойню и возвращаю все чаты, где (пост1.аккаунт = юзер И пост1.рассылка1 = юзер) ИЛИ (пост2.аккаунт = юзер И пост2.рассылка2 = юзер)

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

ну да, схему под access patterns пилить

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

а что используется вместо него?

есть две групных причины не использовать индекс - он не подходит, или он недостаточно селективный

Имеется 2 столбца по которым просиходит поиск — внешний ключ(int, user_id) и datetime, оператор and, при этом создан индекс по обоим полям, т.е. CREATE INDEX ON table(user_id, datetime)

Используется seq scan

таблица довольно большая по размерам

прошу прощения, heap scan

покажи запрос и эксплейн

покажи запрос и эксплейн

Спасибо, дело было в довольно сильном затупе с моей стороны — для сравнения даты использовал datetime::DATE, в то время как индекс был построен без конвертации типа

Завершена разработка защищенной отечественной СУБД Синергия-БД, основанной на PostgreSQL http://www.opennet.ru/opennews/art.shtml?num=46160 Федеральный ядерный центр в Сарове (РФЯЦ-ВНИИЭФ) совместно с компанией Postgres Professional завершили проект по созданию защищенной системы управления базами данных «Синергия-БД», основанной на кодовой базе СУБД PostgreSQL. «Синергия-БД» предназначена для использования в качестве хранилища данных на предприятиях, предъявляющих повышенные требования к надежности и безопасности информационных систем. В первую очередь, это контур ядерно-оружейного и оборонно-промышленного комплекса страны, а также органы исполнительной власти федерального и регионального уровня. #opennet

@Komzpa @pensnarik @raksita парни, всем спасибо за помощь с анализом планов, в итоге отказался от джойна, добавил избыточность, зато теперь всё летает?

@Komzpa @pensnarik @raksita парни, всем спасибо за помощь с анализом планов, в итоге отказался от джойна, добавил избыточность, зато теперь всё летает?

??

ERROR: S client not available

pglogical можно использовать как замену streaming replication?

pglogical можно использовать как замену streaming replication?

А какую цель преследуете?

А какую цель преследуете?

иметь реплику с данными, с которой можно делать периаодически dump. и на которую можно отправлять r/o запросы pgpool'ом

иметь реплику с данными, с которой можно делать периаодически dump. и на которую можно отправлять r/o запросы pgpool'ом

streaming чем не нравится?

streaming чем не нравится?

dump нельзя сделать. да и вообще весь кластер в режиме r/o

dump нельзя сделать. да и вообще весь кластер в режиме r/o

можно

hot_standby

можно

dump длится более часа

ну вот на hot_snadby dump и вылетает с ошибкой

ну вот на hot_snadby dump и вылетает с ошибкой

какой?

какой?

ERROR: canceling statement due to conflict with recovery

ERROR: canceling statement due to conflict with recovery

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

я ставил. место на мастере заканчивается

а вот нашёл hot_standby_feedback = on

Не, это с репликой прилетают удаление данных, которые потом используются в дампе. Нужна задержка применения логов.

Вот этот параметр крутить надо: max_standby_archive_delay (integer)

я ставил. место на мастере заканчивается

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

Вот этот параметр крутить надо: max_standby_archive_delay (integer)

max_standby_streaming_delay а не этот?

Вот этот параметр крутить надо: max_standby_archive_delay (integer)

в моей случае straeming

@Aytuar ну или этот, да.

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

это не даёт ответа на исходный вопрос. в моём случае дамп с хот стендбая вызывает проблемы

Ну значит с pglogical будет заканчиваться место на slave?

Ну значит с pglogical будет заканчиваться место на slave?

почему?

Открытый pg_dump'ом снапшот не потребует аналогичного количества WAL'ов на slave?

Самое главное. Насколько я помню, pglogical - это надстройка над logical decoding, у которой есть некоторые ограничения по применению, например, только одна база из кластера обслуживается этим безобразием.

Открытый pg_dump'ом снапшот не потребует аналогичного количества WAL'ов на slave?

на проде ещё много кто выполняет транзакции, которые сильно влияют на wal

Самое главное. Насколько я помню, pglogical - это надстройка над logical decoding, у которой есть некоторые ограничения по применению, например, только одна база из кластера обслуживается этим безобразием.

мне как раз 1 и надо

ho all