SELECT table1.*, jsonb_each_text() FROM ...

изначально всё работало без jsonb_each_text(table1.json_column), но сейчас он мне нужен, но из-за него он выдает ошибку неверная ссылка на table1

А вот поэтому надо всё явными JOIN-ами писать! ;) По идее, ваш запрос parse-ится вот так: SELECT table1.* FROM table1, ( jsonb_each_text(table1.json_column) LEFT JOIN table2 ON table1.t2_id = table2.id ) А вот было бы: SELECT table1.* FROM table1 CROSS JOIN LATERAL jsonb_each_text(table1.json_column) AS something LEFT JOIN table2 ON table1.t2_id = table2.id И проблемы бы не было.

SELECT table1.*, jsonb_each_text() FROM ...

мой вариант работает, если join убрать

Добрый день, подскажите пожалуйста, буферный кэш и кэш операционной системы в итоге кэшируют одни и те же страницы 2 раза?

Как посмотреть default text search config?

SHOW default_text_search_config;

Ищу по gin индексу SELECT "item"."params", to_tsvector(COALESCE(("item"."params" ->> 'street'))) AS "search" FROM "item" WHERE to_tsvector(COALESCE(("item"."params" ->> 'street'))) @@ (to_tsquery('Кутузова')); получается регистрозависимый поиск, как сделать регистронезависимый?

Ищу по gin индексу SELECT "item"."params", to_tsvector(COALESCE(("item"."params" ->> 'street'))) AS "search" FROM "item" WHERE to_tsvector(COALESCE(("item"."params" ->> 'street'))) @@ (to_tsquery('Кутузова')); получается регистрозависимый поиск, как сделать регистронезависимый?

Юзать to_tsquery('Кутузова', 'russian')

в to_tsvector тоже не забывать

Юзать to_tsquery('Кутузова', 'russian')

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

Ну и убедиться что LC_LOCALE не C

C вроде

ENCODING = 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C' TABLESPACE = pg_default

Ну и убедиться что LC_LOCALE не C

Это переменная окружения?

Крепись бро. Придется пересоздавать базу с lc_collate=utf-8

Крепись бро. Придется пересоздавать базу с lc_collate=utf-8

Спасибо бро, а остальное правильно?

Lc_ctype тоже

Утф

Добрый день, подскажите пожалуйста, буферный кэш и кэш операционной системы в итоге кэшируют одни и те же страницы 2 раза?

Не обязательно, но зачастую да, в типичных случаях.

люди, есть очень странное поведение. если кто-то что-то такое видел, то скажуте куда копать. дано: если модуль на го, работающий с postgres 9.5. работа происходил в 99% случаев в фоновом процессе, который запускается раз в минуту. время от времени ( от 5 минут до часа) процесс соединения модуля и базы (на сервере, где бежит postgres) вырастает ровно на 264 байта. в pg_stat_activity idle последний запрос INSERT, который вызывает ФП. В 95% случаев он откатывается из-за uniq index. кто-то что-то подобное видел? куда копать?

люди, есть очень странное поведение. если кто-то что-то такое видел, то скажуте куда копать. дано: если модуль на го, работающий с postgres 9.5. работа происходил в 99% случаев в фоновом процессе, который запускается раз в минуту. время от времени ( от 5 минут до часа) процесс соединения модуля и базы (на сервере, где бежит postgres) вырастает ровно на 264 байта. в pg_stat_activity idle последний запрос INSERT, который вызывает ФП. В 95% случаев он откатывается из-за uniq index. кто-то что-то подобное видел? куда копать?

> если модуль на го, работающий с postgres 9.5. работа происходил в 99% случаев в фоновом процессе, который запускается раз в минуту. А что именно имеется в виду? Background worker (их вообще воможно на Go писать? Я просто не в курсе...)? Или обычный клиент? > процесс соединения модуля и базы (на сервере, где бежит postgres) вырастает ровно на 264 байта. Какой именно процесс? Backend (postgres)? Или какой-то другой?

postgres 26815 0.0 0.2 369152 8860 ? Ss 10:25 0:00 postgres: postgres ...

вот, там где 8860 10 минут назад было 8596

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

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

То есть, обычный клиент. А если трассировать запросы от него (в логах PostgreSQL, log_statement=all)? log_statement, кстати, можно задавать для сессии (явно, после подключения), или для пользователя (ALTER ROLE).

спасибо, попробую

подскажите, это как то можно поднастроить: если запрос завершается с ошибкой например ERROR: canceling statement due to user request или FATAL: connection to client lost то в логах нет записи с какими параметрами этот запрос был запущен в нормальном же случае если запрос был исполнен корректно то в логах появляется такая строка следом за запросом 2017-12-22 10:55:54 BLABLABLA [17121]: [156-1] db=bla,appname=192.168.4.71,user=bla_slave,client=192.168.5.111 DETAIL: parameters: $1 = '87833', $2 = '139', $3 = '87833', можно сделать так что бы в случае ERROR или FATAL входящие параметры записались в лог?

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

Добрый день всем, помогите кто не занят: имеется таблица в которой есть колонка c типом jsonb, в ней хранится Jsonb объект. Нужно отфильтровывать запросы по всем значениям (value) внутри этого jsonb объекта, при этом у него могут быть разные ключи. Вот например как работает сейчас: SELECT * FROM table1 WHERE table1.jsonb_column::text LIKE '%filter%'; Однако теперь нужно игнорировать совпадения по ключам и работать только со значениями JSONB объектов .

при этом ключи могут быть разными

при этом ключи могут быть разными

отсутствие ключа в jsonb равносильно его присутствию только со значением NULL ) это вам для справки

ключ то есть, но он разный кажрый раз

Добрый день всем, помогите кто не занят: имеется таблица в которой есть колонка c типом jsonb, в ней хранится Jsonb объект. Нужно отфильтровывать запросы по всем значениям (value) внутри этого jsonb объекта, при этом у него могут быть разные ключи. Вот например как работает сейчас: SELECT * FROM table1 WHERE table1.jsonb_column::text LIKE '%filter%'; Однако теперь нужно игнорировать совпадения по ключам и работать только со значениями JSONB объектов .

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

select * from table1 where 'filter' in json_each(jsonb_column)

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

по мне так лучше всего было использовать jsquery

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

честно говоря я не знаю по какой причине у нас именно так в бд всё и записывается

просто попросили добавить фильтр на сайт по этим вот значениям

https://postgrespro.ru/docs/postgrespro/9.5/jsquery

честно говоря я не знаю по какой причине у нас именно так в бд всё и записывается

Как "так"? Т.е. у вас есть, к примеру {weight = 5 (килограмм), height = 5 (метров)}, и вы ищете 5-ку? Я не понимаю, какой вообще в этом смысл... :(

нет, я думаю там как раз Davron не в курсе какие параметры могут пользователи добавить к своим сущностям но раз они добавляют тот же вес или длину или цвет то хотят иметь фильтры по ним)

нет, я думаю там как раз Davron не в курсе какие параметры могут пользователи добавить к своим сущностям но раз они добавляют тот же вес или длину или цвет то хотят иметь фильтры по ним)

да, что-то в этом роде

ну все просто, вам же приходит наименование ключа, и значение, можете любой способ применить для фильтрации, но лучше делайте так что точно по индексам работает + сами индексы)

по сути вы делаете фильтр item @@ 'id.%($ > 100)'::jsquery внутри в jsquery вы можете сгененировать прикладом любую логику)

нет, я думаю там как раз Davron не в курсе какие параметры могут пользователи добавить к своим сущностям но раз они добавляют тот же вес или длину или цвет то хотят иметь фильтры по ним)

Ну это же не "фильтры по ним" (это я бы понял — например, фильтр по весу 5 килограмм), а фильтры как-то "между" ними (т.е. где название, или вес, или размер, и т.п. — 5... ну, просто 5). :(

Добрый день всем, помогите кто не занят: имеется таблица в которой есть колонка c типом jsonb, в ней хранится Jsonb объект. Нужно отфильтровывать запросы по всем значениям (value) внутри этого jsonb объекта, при этом у него могут быть разные ключи. Вот например как работает сейчас: SELECT * FROM table1 WHERE table1.jsonb_column::text LIKE '%filter%'; Однако теперь нужно игнорировать совпадения по ключам и работать только со значениями JSONB объектов .

Ну ладно, а чисто технически — это одноуровнеый JSON, или там могут быть вложенные?

Ну ладно, а чисто технически — это одноуровнеый JSON, или там могут быть вложенные?

по идее одноуровневый

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

по идее одноуровневый

Ну, например: WITH x(n, js) AS ( VALUES (1, '{"a":"foo", "b":"bar"}'::jsonb), (2, '{"a":"bax", "b":"baz"}'::jsonb), (3, '{"a":"baz", "b":"foo"}'::jsonb)) SELECT * FROM x WHERE EXISTS (SELECT 1 FROM jsonb_each_text(x.js) AS t(k, v) WHERE t.v = 'foo') Хотя, по-хорошему, бросали бы вы это дело (в смысле, подумали о нормализации схемы). Чем дальше, тем страшнее будет, я подозреваю. :(

вообще решение я вчера нашел, но оно немного корявое: SELECT table1.*, table2.name FROM table1 CROSS JOIN LATERAL jsonb_each_text(table1.jsonb_column) AS jsonb_column LEFT JOIN table2 ON table1.t2_id = table2.id WHERE value LIKE '%filter%';

но пробелема возникает когда в jsonb_column лежит одноуровневый объект с множественными парами ключи-значение

он просто дублирует строки меняя столбец value

он просто дублирует строки меняя столбец value

Вы мой запрос попробовали? Если да, какая с ним проблема?

Ну, например: WITH x(n, js) AS ( VALUES (1, '{"a":"foo", "b":"bar"}'::jsonb), (2, '{"a":"bax", "b":"baz"}'::jsonb), (3, '{"a":"baz", "b":"foo"}'::jsonb)) SELECT * FROM x WHERE EXISTS (SELECT 1 FROM jsonb_each_text(x.js) AS t(k, v) WHERE t.v = 'foo') Хотя, по-хорошему, бросали бы вы это дело (в смысле, подумали о нормализации схемы). Чем дальше, тем страшнее будет, я подозреваю. :(

я его испугался)

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

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

Эээ.. что? Какое расширение?

https://postgrespro.ru/docs/postgrespro/9.5/jsquery

это разве не расширение?

это разве не расширение?

А я разве @amir_i_cano? ;)

а ой

Привет. В одном из столбцов таблицы есть массив (BIGINT[]). Как эффективно получить строки, у которых содержится конкретное значение в этом массиве?

ERROR: S client not available

> лучше бы вы нормализовали схему...

Привет. В одном из столбцов таблицы есть массив (BIGINT[]). Как эффективно получить строки, у которых содержится конкретное значение в этом массиве?

WHERE "bigintfield" @> "field" ну и там навесить индекс на "bigintfield"

Привет всем Подскажите пожалуйста, время от времени сервер выдает такую ошибку: org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections После того как начинает её выдавать ни один сервлет не может подключиться к БД постоянно вылетает, минут через 10 ошибка пропадает, либо после перезагрузки всех сервлетов

я не большой спец, но тут надо в postgres.conf увеличивать параметр max_connections

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

WHERE "bigintfield" @> "field" ну и там навесить индекс на "bigintfield"

спасибо. получилось вот как: CREATE INDEX ON table1 USING GIN (parent_id); SELECT * FROM table1 WHERE parent_id @> ARRAY[1::BIGINT] AND latest_action_type != 'DELETE';

спасибо. получилось вот как: CREATE INDEX ON table1 USING GIN (parent_id); SELECT * FROM table1 WHERE parent_id @> ARRAY[1::BIGINT] AND latest_action_type != 'DELETE';

да, это и имел ввиду

обидно только, что GIN-индекс не может быть на нескольких столбцах

обидно только, что GIN-индекс не может быть на нескольких столбцах

Лучше бы вы нормализовали схему...

Всем привет! Подскажите, пожалуйста, как в PostgreSQL реализован сбор статистики? Допустим, стоит дефолтное значение собирать по 100 самплов с каждого поля. Это значит, что при каждом ANALYZE будут рандомно выдернуты 100 самплов? Или что каждый раз будут выдернуты одни и те же самплы? Если распределение неравномерное, может быть такое, что выполненный несколько раз подряд ANALYZE в итоге приведет к разным планам выполнения запросов? И еще вопрос в продолжение темы – куда смотреть если даже после повышения сбора самплов до 10к и выполнения VACUUM ANALYZE по таблице, планер сильно врет при предсказании ожидаемого числа строк и фактическом результате?

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

Всем привет! Подскажите, пожалуйста, как в PostgreSQL реализован сбор статистики? Допустим, стоит дефолтное значение собирать по 100 самплов с каждого поля. Это значит, что при каждом ANALYZE будут рандомно выдернуты 100 самплов? Или что каждый раз будут выдернуты одни и те же самплы? Если распределение неравномерное, может быть такое, что выполненный несколько раз подряд ANALYZE в итоге приведет к разным планам выполнения запросов? И еще вопрос в продолжение темы – куда смотреть если даже после повышения сбора самплов до 10к и выполнения VACUUM ANALYZE по таблице, планер сильно врет при предсказании ожидаемого числа строк и фактическом результате?

> Допустим, стоит дефолтное значение собирать по 100 самплов с каждого поля. Это не 100 samples. Это размер MCV и гистограмм. > Это значит, что при каждом ANALYZE будут рандомно выдернуты 100 самплов? Но да, выбираются они (более-менее) случайно. > Или что каждый раз будут выдернуты одни и те же самплы? Нет, если выбирается не вся таблица, то разные. > Если распределение неравномерное, может быть такое, что выполненный несколько раз подряд ANALYZE в итоге приведет к разным планам выполнения запросов? Да, может. > И еще вопрос в продолжение темы – куда смотреть если даже после повышения сбора самплов до 10к и выполнения VACUUM ANALYZE по таблице, планер сильно врет при предсказании ожидаемого числа строк и фактическом результате? В план конкретного запроса — это всё не так просто.

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

Причины разные, самые "пакостные" — это оценка JOINs, корреляции и n_distinct, наверное...

join’ов нет в запросе. Есть выборка по паре полей с плавающей селективностью (зависит от передаваемого в предикат значения) и составной индекс по этим полям

обидно только, что GIN-индекс не может быть на нескольких столбцах

а что мешает создать несколько индексов?

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

условно, иногда есть кейсы, когда постгрес ожидает 20к+ строк, когда по факту запрос возвращает одну строку

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

А вы бы смогли (серьёзно)? Вот у вас, например, есть гистограммы распределения для каждого из полей, допустим, age и married. Как бы вы посчитали ожидаемое количество записей в запросе: "SELECT * FROM people WHERE married AND age < 12;" ?

про то и речь. чтобы понять, смог бы я или нет, пытаюсь разобраться в алгоритме работы PostgreSQL при сборе статистики

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

и вот если бы не он, помогла бы фича с create statistics, вероятно а так постгрес не может связать поля из индекса и таблицы

и сэмплов 10к явно мало для большой таблицы, а поднять выше не выходит

про то и речь. чтобы понять, смог бы я или нет, пытаюсь разобраться в алгоритме работы PostgreSQL при сборе статистики

Ну вот попробуйте на этом примере. Понятно, что использовать предположение независимости переменных неправильно (я даже где-то видел исследование, где указывались цифры вроде "в 70% случаев для реальных данных")... но а какое правильно? :(

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

и сэмплов 10к явно мало для большой таблицы, а поднять выше не выходит

Ещё раз, это не 10к samples.

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

И показали бы вы индексы, запрос и план... Какая версия PostgreSQL, кстати?

Ещё раз, это не 10к samples.

сорри, пропустил одно сообщение

MCV = most common values?

а планы разве не на основе гистограмм строятся?