Всем привет, вопрос по view-ам. Есть запрос который с помощью

CROSSTAB переворачивает данные из длинного в широкий формат.
Таблица к которой обращается запрос имеет более чем 500кк записей.
Пример запроса:
SELECT * FROM CROSSTAB(
'SELECT dt, name, value
FROM some_huge_table
WHERE dt >= ''2023-05-19 00:00:00'' AND dt < ''2023-05-20 00:00:00''
ORDER BY 1,2'
)
AS summary(
dt TIMESTAMP,
"1" FLOAT,
"2" FLOAT,
"3" FLOAT,
...
"1000" FLOAT

При выполнении этого запроса с фильтром по времени все работает хорошо и быстро.
В случае помещения этого запроса во view, но уже без фильтра, а фильтрацию осуществлять на view-хе, запрос зависает.
SELECT *
FROM some_view
WHERE dt >= '2023-05-19 00:00:00' AND dt <= '2023-05-20 00:00:00'

Я почему-то всегда думал, что postgres при фильтрации по вьюхе будет выгружать только диапозон строк, а не всю вьюху.
А в итоге получается что он сначала все выгружает, а уже потом фильтрует?
Работает как HAVING?

так у вас там crosstab - в него нельзя услловие прокинуть, это ж функция со строкой на входе

Хм, а я даже не подумал об этом. Понял, спасибо

Для начала разговора про скорость запросов представьте информацыю из закрепа: https://t.me/pgsql/303899 Без этого такие разговоры будет совершэнно беспредметными. PS А, и да, откажытесь от crosstab. В нём нет никакого смысла в RDBMS.

С CROSSTAB просто переворачивать таблицу из узкого формата в широкий. Почему вы не советуете его использовать?

Потому, что это противоречит типизацыи в RDBMS. Мы работаем с множэствами (часто -- очень большыми) строго, и часто статически типизированных данных. crosstab переворачивает это до небольшого количества данных неизвестных типов -- и под это не оптимизированно примерно ничего. Начиная с того, что оно сломается на примерно полутора тысячах строк результата. Плюс, там полное фиаско с типом колонок. Притом, в этом нет никакого смысла. Никакой интересной информацыи из crosstab вы не получите. Если нужно "чисто глазами посмотреть" -- переворачивайте в вашэй смотрилке, presentation -- это не работа RDBMS.

ну от PIVOT в субд, которые его поддерживают, еще нико не умер:))))

Ну если так рассуждать то и функции нельзя использовать в СУБД. Мы пробовали переворачивать через pandas, но postgres отрабатывает на порядок быстрее

В большынстве случаев можно и вообще СУБД выкинуть -- и никто не умрёт. А так, людей, которые встряли на 1600 или страдали из-за нюансов типизацыи строки с названиями -- я встречал.

ну понятно, религия не будем спорить

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

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

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

в SQL оно нормально - PIVOT, UNPIVOT (не сильно нужно, но вполне нормально и полезно) 1 берем оттчеты мменеджеров не думая грузим copy 2 unpivot 3 agragate 4 pivot 5 copy все конечно можно написать в приложении не вопрос

У нас в базе храниться огромное количество показателей снимаемых с сотен датчиков в узком формате (dt, name, value). По этим показателям саентисты строят свои модели, но их модели принимают данные только в широком виде (dt, name1, name2, ... name1000). Поэтому есть задача переворота данных из узкого формата в широкий. Выгрузку необходимо делать например за 90 дней, за это время в таблице накопиться порядка 200кк строк, пандас справляется, но делает это гораздо дольше, условно 5 минут, когда постгрес за 1 минуту. Для того и используем crosstab.

это гораздо быстрее чем загружать этим клиентское приложение, и позволяет заметно уменьшить объем данных, передаваемых клиенту. А еще в какой-то степени позволяет эмулировать запросы в колоночное хранилище (для клиента)

ну объемы pivot не сократит, но удобно

речь о crosstab

так это убогий PIVOT )

Может быть, лучше примерно так агрегировать в массивы/json с помощью обычных SQL-запросов, чтобы условия пробрасывались внутрь VIEW ? (Не уверен, что правильно понял смысл этой тысячи столбцов в crosstab) create table test_pivot as select id, data from generate_series(1, 10000) id, enerate_series(1, 1000) data; create view test_pivot_view as select id, array_agg(data) data from ( select id, data, row_number() over (partition by id order by data) n from test_pivot ) foo group by id, (n - 1) / 100; select * from test_pivot_view; 11233.465 ms select * from test_pivot_view where id > 1234 and id < 1237; 235 ms 1234 | {1,2,3,...98,99,100} 1234 | {101,102,103,...198,199,200} ...

Постгрес в принцыпе неспособен сделать crosstab на 200 милионов строк...

почемму?)))))

Потому, что в нём лимит 1600 столбцов, а crosstab делает строки — стобцами.

да нет же ) ттам хреновая документация на расширениее

этот не транспониирование всео запроса

А что б и модели-то сразу в PostgreSQL не строить, зачем данные куда-то таскать? ;) В общем, посыл подобных советов, во-первых, в том, что подобные преобразования — это "оформительство" (так можно дойти и до того, чтобы в отчётах считать итоги по группам на сервере... а потом возвращать какие-то поля, которые будут указывать клиенту, в какой цвет красить данные ячейки в отчёте... а затем уже вообще генерировать отчёт целиком (в html или т.п.)... ну и почему бы сразу уж не отсылать его на e-mail / не выкладывать куда-то, зачем вообще клиенту что-то возвращать?!). Т.е. неплохо было бы остановиться сразу, не скатываясь в вышеописанное безумие нецелевое использование СУБД. Во-вторых, большинство RDBMS работают как статически типизированные системы, причём это относится не только к типам полей, но и к типам обрабатываемых и возвращаемых отношений (точнее, таблиц, если речь об SQL). Поэтому нормального пути вернуть из запроса или функции отношение динамического типа (к примеру, либо (a int, b int, c int), либо (d numeric, e text) ) исключительно в зависимости от того, какие данные "попались" этому запросу, просто нет ("ненормальные", вроде композитных типов и т.д. и т.п., конечно, существуют). Т.е. это "религия" не в большей степени, чем невозможность (например, в C ) вернуть double из функции, которая объявлена, как возвращающая int.

Мы что-то на подобие в начале проектирования думали создать, только использовали jsonb, но потом при раскладке в датафреймы пандаса сильно страдала скорость из-за того что надо было сливать между собой эти json-ы. Хотя пандас вроде как специальный инструментарий имеет под эту задачу. В итоге был выбор либо хранить уже в широком виде в самих таблицах, но тогда мы бы померли от апдейтов и блокировок, либо переворачивать через crosstab.

считать итоги по группам плохо на сервере?)))))

и pivot не возвращщаеет динамичесие типы)

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

Нет, не плохо — это иллюстрация "благими намерениями вымощена дорога в ад". Т.е. начинается-то это всё невинно, а вот далее... см. выше.

Естественно, не возвращает. Я же не просто так написал, что "нормального пути вернуть ... отношение динамического типа ... просто нет".

200кк строк это не значит что в широком виде будет 200кк показателей, показателей как раз таки до 1600. 139088688 перевернул изи за 2.5мин

А, действительно, есть тут такой вариант.

Вместо использования jsonb-объектов можно попробовать jsonb-массивы (или обычные), с которыми должно быть дешевле работать из Питона. Для эффективного создания массивов нужна специальная агрегатная функция, умеющая писать нужный элемент по индексу, чтобы каждое свойство всегда лежало на одном и том же месте (для свойств нужны будут их числовые id). Такую функцию можно даже на SQL написать, но она, конечно, очень медленно будет работать (еще в нее приходится передавать максимальное кол-во элементов, потому что jsonb_set не умееет произвольно расширять массив): CREATE FUNCTION jsonb_agg_idx_trans( arr jsonb, elem anyelement, idx int, max_elems int ) RETURNS jsonb AS $$ SELECT jsonb_set(COALESCE(arr, to_jsonb(array_fill(NULL::int, ARRAY[max_elems]))), ARRAY[idx::text], to_jsonb(elem)) $$ LANGUAGE SQL STABLE; CREATE AGGREGATE jsonb_agg_idx( elem anyelement, idx int, max_elems int ) ( SFUNC = jsonb_agg_idx_trans, STYPE = jsonb ); CREATE TABLE test AS SELECT id, 'prop' || i AS name, id * 1000 + i - 1 AS val FROM generate_series(1, 1000) id, generate_series(1, 100) i WHERE (id + i) % 3 = 0; CREATE TABLE properties AS SELECT i - 1 AS id, 'prop' || i AS name FROM generate_series(1, 100) i; SELECT t.id, jsonb_agg_idx(t.val, p.id, 100) FROM test t JOIN properties p USING (name) GROUP BY 1; 1 | [null, 1001, null, null, 1004, ... 1097, null, null] 2 | [2000, null, null, 2003, null, ... null, null, 2099] ...

Вот кстати типичный пример когда pivot jsonb проще ))))

Потестю Ваш способ в пн, спасибо)

Надо на C написать эту функцию и от джойна по имени свойства избавиться, без этого все очень медленно работает -- порядка 100 000 строк в секунду.

Ну да, медленно

Реальная скорость на C должна получиться такой же, как и у остальных агрегатных функций. На предыдущем примере с 10М строк у меня такие числа получились: array_agg() -- 2.9 Mrow/s json_agg() -- 2.3 Mrow/s jsonb_agg() -- 1.2 Mrow/s jsonb_agg() очень неэффективно написана, надо бы ее переделать.

А jsonb_object_agg тоже?

jsonb_object_agg(), конечно, намного удобнее на стороне сервера, но на стороне клиента парсинг объектов и извлечение значений из них, возможно, будет заметно медленнее, чем у массивов. json[b]_object_agg() раза в полтора медленнее, чем json[b]_agg(). И использовать jsonb в данном случае, когда результат передается клиенту в виде текста, смысла нет, так как мы получим лишний оверхед промежуточного кодирования в jsonb. В примере выше я использовал jsonb, потому что, во-первых, у нас нет array_set() и запись элемента массива по индексу возможна только в UPDATE tab SET arr[idx] = val (что очень плохо), и, во-вторых, у нас нет json_set().

Ну [b] я условно. Но 1,5 раза принятого

А про парсинг кстати не факт, но питон не знаю. А так то осталось jsonb_populate_record with sub query и pivot в кармане 🤦‍♀️