Переменные в postgres как в mysql

pg_variables можно использовать https://github.com/postgrespro/pg_variables

pg_variables это отдельный модуль, это не совсем то, потому что оказывается PG может хранить переменные к конфиге, они могут быть внутри сессии и внутри транзакции, но это простые переменные. Ещё он может временные VIEW, но они живут до конца сессии. И ещё он может временные таблицы! И их можно дропать в конце транзакции, и это здорово. Вот такая вот прелесть получилась в ходе размышлений: (posts принадлежит authors'у, аналог authors left outer join posts)

BEGIN;
  CREATE TEMP TABLE t ON COMMIT DROP AS SELECT * FROM authors;
  SELECT name FROM t;
  SELECT title FROM posts WHERE author_id IN (SELECT id FROM t);
END;

SELECT id, name FROM authors;
-- на клиенте собрать все id
SELECT title FROM posts WHERE author_id IN (1, 2, 3);

SELECT name FROM authors;
SELECT title FROM posts WHERE author_id IN (SELECT id FROM authors);

Померял, думаю, всем интересно: задача - выбрать всех авторов вместе с их постами. Способа 4: 1) С помощью временной таблицы в транзакции; 2) С помощью временной VIEW, которая в конце запроса удаляется; 3) Сначала получаем авторов, потом посты по собранным id; 4) id авторов собираются подзапросом, как выше показал Таблицы маленькие, 5 авторов, 8 постов: (в секундах, 500 итераций)

temp table: 6.0405511856079

temp view: 6.0984668731689

resend ids: 0.27397990226746

repeat first: 0.20581197738647

Рандомными значениями заполнил 10000 авторов и 10000 постов, у авторов 5 колонок, у постов 10, что получилось: (100 итераций)

temp table: 10.707113027573

temp view: 10.277657985687

resend ids: 66.552784919739

repeat first: 8.9030148983002

Выводы: нет, то как делает rails самый медленный способ (я про сам подход, тестировал на lua), временная таблица надежнее, чем view: она точно удалится, если вдруг postgres упадет среди запроса, но она чуть медленнее VIEW, если только один раз спросить, а не 100 - медленнее почти в 2 раза, и на простых запросах, вроде моего - повторять первую часть во второй для id - самый верный способ! Если запрос будет таблиц из 5, этот способ вероятно сдуется. В итоге все равно не знаю, какой выбрать.

postgres
выбрать всех авторов вместе с их постами

array_agg?

И зачем он тут?

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

Все ормы которые делают как делает ORM Ruby on rails это плохие ормы, как ты понял они выполняют свою работу очень не эффективно, в большинстве случаев перетягивание айдишников на клиент сделано не с целью «отимизации», а потому что код который так делает простой и обезьянки которые это писали всё таки смогли выполнить задачу таким путём. Ещё хуже дело обстоит с пагинацией она вообще гипер тормознутая в таких ормах, а вот если фильтрация производиться на клиенте(привет django orm) то это вообще п*здец.

Реляционные базы на то и реляционные, ты всегда работаешь со строками и кортежами, эту двумерный массив, и если тебе нужно выбрать древовидную структуру или граф то у тебя всегда будет двумерный массив с множеством пустых значений. Пытаться факать эту парадигму и «оптимизировать» пересылая айдишиники это плохая идея, как ты понял, лишние сетевые накладные расходы на пересыл пустых значений нивелируется очень хорошей производительностью одного запроса с джойнами по сравнению с целой кучей селектов по id. Да и производительность базы никчёрту другие параллельные запросы от такого доса будут очень хорошо висеть (посмотри загрузку cpu, диска и памяти при всех вариантах бенчмарка).

Если тебе нужно по хипстерски эффективно читать древовидные данные, то тебе нужен NoSQL вроде mongodb, но там куча своих мест где нога простреливается на раз два.

В общем используй 4 вариант т.е. джойны, это самый правильный путь. А ещё учи матчасть чтобы ты сам себя не тролил прилюдно.

Да какая разница кто кого и даже если сам себя тролю, глупости все это, интересно до истины докапаться. Да, воистину джоины самые быстрые, хотя и могут выдавать непредсказуемо великое количество дублей. Вот мне это и не нравится, значит можно и нужно придумать четкий способ! Для этого и создал тред, не боясь обзывающихся проходимцев. У NoSQL проблем намного больше, чем решений, но это тема бессмысленного холивора. А я знал, что ЛОР то самое место, где люди намного опытнее, знающие и всегда или почти всегда помогают найти решение, до которого самому идти-не дойти. Вот аноним сверху - спасибо аноним! Я только сейчас вдуплил какая замечательная функция array_agg, и она решает эти join'ы просто гениально! Аноним написал название и знак вопроса, как мастер саспенса, держа нас в напряжении до тех пор, пока мысль что ж это таки значило не дошла. Она, мать её, функция эта делает из postgres'а монгу без json'а!!1! Получаем массив authors, и в каждом авторе уже есть массив постов! А в постах будут комменты, допустим! Аноним, спасибо ещё раз, ты сделал мой день!

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

можно и нужно придумать четкий способ

Не стоит боятся не чётких способов, инженерное решение это всегда некий компромисс.

Протестируй производительность реализации array_agg против джойнов

Вот пример:

SELECT a.id, name, array_agg(ROW(p.title, p.comments_count)) FROM authors a LEFT OUTER JOIN posts p ON a.id = p.author_id GROUP BY a.id;

Не стоит боятся не чётких способов, инженерное решение это всегда некий компромисс.

У идеала не бывает компромиссов :)

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

У идеала не бывает компромиссов :)

Именно по тому он не достижим

Можно кстати план запроса?

Возврат всего строку это поправимо! Мой адаптер для pg пока не подозревал, что в массиве могут быть ROW'ы, но научится, и psql видимо тоже, показывает посты в кавычках вот так: {"(EnuL1nDl58EfnEPfuOHeQd9euESayR,24)«} (рандомный title), да и pgadmin точно так же, видимо, agg'ом пользоваться для строк ROW мало кому в голову приходило (и очень зря).

Теперь время посравнивать планы. Да, ещё до сравнения очевидно, что нормальный join будет может в 2, может даже в 3 быстрее. И сравнивать их вообще некорректно! Совсем! Я не знаю зачем это делаю, ну прост. Некорректно, потому что join выдает нужные данные в виде, который совсем не катит, но если нужен именно такой вид - вот для этого есть просто join. А agg дает именно то, что надо для орм! Напомню, это Объектно Реляционная Модель, и вот agg все строит по этой модели прям на постгресе.

Запрос: выбираем все посты, в каждом посте массив авторов, в каждом авторе массив ещё и книг, то есть три таблицы, так интереснее.

EXPLAIN ANALYZE
  SELECT p.id, p.title, array_agg(ROW(a.id, a.name, a.books)) authors
  FROM posts p
  LEFT OUTER JOIN (
    SELECT a.id, a.name, array_agg(ROW(b.id, b.published_at)) books
    FROM authors a
    LEFT OUTER JOIN books b ON b.author_id = a.id
    GROUP BY a.id
  ) a ON a.id = p.author_id
  GROUP BY p.id;
                                                                              QUERY PLAN                                                                               
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=3417.34..3660.10 rows=10000 width=67) (actual time=206.651..256.372 rows=10000 loops=1)
   Group Key: p.id
   ->  Sort  (cost=3417.34..3456.59 rows=15701 width=82) (actual time=206.630..220.630 rows=10000 loops=1)
         Sort Key: p.id
         Sort Method: quicksort  Memory: 2132kB
         ->  Hash Right Join  (cost=752.57..2323.09 rows=15701 width=82) (actual time=33.225..186.778 rows=10000 loops=1)
               Hash Cond: (a.id = p.author_id)
               ->  GroupAggregate  (cost=0.57..1189.08 rows=10000 width=47) (actual time=0.081..121.348 rows=10000 loops=1)
                     Group Key: a.id
                     ->  Merge Left Join  (cost=0.57..1014.08 rows=10000 width=27) (actual time=0.050..75.415 rows=13674 loops=1)
                           Merge Cond: (a.id = b.author_id)
                           ->  Index Scan using authors_pkey on authors a  (cost=0.29..347.29 rows=10000 width=15) (actual time=0.017..11.531 rows=10000 loops=1)
                           ->  Index Scan using books_author_id_index on books b  (cost=0.29..517.47 rows=9946 width=16) (actual time=0.024..20.570 rows=9946 loops=1)
               ->  Hash  (cost=627.00..627.00 rows=10000 width=39) (actual time=33.053..33.053 rows=10000 loops=1)
                     Buckets: 16384  Batches: 1  Memory Usage: 832kB
                     ->  Seq Scan on posts p  (cost=0.00..627.00 rows=10000 width=39) (actual time=0.013..15.869 rows=10000 loops=1)
 Planning time: 0.475 ms
 Execution time: 268.161 ms
(18 rows)

И теперь аналогично с просто outer join'ами: (дублей всего 3685 из 10000 всех постов)

EXPLAIN ANALYZE
  SELECT p.id, p.title, a.id, a.name, b.id, b.published_at
  FROM posts p
  LEFT OUTER JOIN authors a ON a.id = p.author_id
  LEFT OUTER JOIN books b ON b.author_id = a.id;
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=714.22..1478.72 rows=10000 width=62) (actual time=80.972..118.973 rows=13685 loops=1)
   Hash Cond: (p.author_id = a.id)
   ->  Seq Scan on posts p  (cost=0.00..627.00 rows=10000 width=39) (actual time=0.008..8.971 rows=10000 loops=1)
   ->  Hash  (cost=589.22..589.22 rows=10000 width=27) (actual time=80.933..80.933 rows=13674 loops=1)
         Buckets: 16384  Batches: 1  Memory Usage: 921kB
         ->  Hash Right Join  (cost=299.00..589.22 rows=10000 width=27) (actual time=26.991..63.688 rows=13674 loops=1)
               Hash Cond: (b.author_id = a.id)
               ->  Seq Scan on books b  (cost=0.00..153.46 rows=9946 width=16) (actual time=0.012..8.359 rows=9946 loops=1)
               ->  Hash  (cost=174.00..174.00 rows=10000 width=15) (actual time=26.949..26.949 rows=10000 loops=1)
                     Buckets: 16384  Batches: 1  Memory Usage: 587kB
                     ->  Seq Scan on authors a  (cost=0.00..174.00 rows=10000 width=15) (actual time=0.009..12.754 rows=10000 loops=1)
 Planning time: 0.521 ms
 Execution time: 133.247 ms
(13 rows)

Кстати, в протоколе у каждого типа приходит код. Массив с ROW'ами - код 239, булеан, например, 16, у меня есть маленький список с этими кодами. Весь интернет перерыл и не нашел официального полного списка, только в исходниках у других адаптеров. Где бы найти таковой?

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

Вообщем рад за тебя что ты нащёл подходящие для тебя решение, но я совсем не понимаю - почему ты недоволен той структурой которую выдаёт обыкновенные джойны?

И зачем он тут?

выбрать всех авторов вместе с их постами

Не?

Да, молодец, супер! Видимо не читал кучу текста после

Ответ с ними такой: Вася - пост 1, Вася - пост 2, Вася - пост 3, Петя - пост 4. Код приложения выглядит примерно так: authors = Author.include('posts'); тут authors массив из двух. authors[0] это Вася, если индекс у языка с нуля. authors[0].posts это его посты. Как можно автоматически строить из таких ответов такие структуры? Можно, но сложно и по производительности не выгодно, а с array_agg'ом они бы строились рекурсивно еще на этапе разбора адаптером ответа. Если ты хочешь такой ответ джоина обработать и джсон составить - как тут быть без всех этих сложностей? Либо носкл со всеми вытекающими, либо вот надо искать пути решения

Сколько я не вчитывался я так и не смог понят что тебе нужно.

1) Почему ты просто не можешь выбрать посты заджойнив к ним авторов? 2) Я предполагаю что ты хочешь выбрать данные потипу как в GraphQL. Те отдельно список постов по данному условию? отедльно список авторов, либо у авторов либо у постов есть список айдишников другой сущности. Почему тогда не сделать это в 2 запроса:

select ... FROM posts WHERE posts.шото = условие_кокоето

select ... FROM authors JOIN posts ON posts.author_id = authors.id WHERE posts.шото = уловие_кокоето 

Можно и в два, этот способ в бенчмарке 4-ый, можно и на каждый пост получать авторов, тогда будет 10тыс запросов. Не важно сколько, важно как данные из

 name | title | шото           
------+--------------------------------
 Вася | пост 1 | шото 1
 Вася | пост 1 | шото 2
 Вася | пост 2 | шото 3
 Петя | пост 3 | шото 4
 Петя | пост 3 | шото 5

Ты видимо не понял меня с первым вариантом.

select ... FROM posts JOIN authors ON posts.author_id = authors.id

Вот в чём проблема так сделать? Да если у тебя есть посты с одинаковыми авторами, то да будет гонятся лишняя информация, её можно исключить но это экономия на спичках.

Можно и в два, этот способ в бенчмарке 4-ый, можно и на каждый пост получать авторов, тогда будет 10тыс запросов

Ты не понял в GrapQL такой подход на клиент приходит что то на вроде такого.

{
  "posts": {
    4565633: {
      "author_id": 599,
      ...
    },
    4565634: {
      "author_id": 599,
      ...
    },
  },
  "autors": {
    599: {
      "posts": [4565633,4565634]
    },
  }
}

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

Идеальный подход! Возьму на вооружение

Теперь буду голову ломать как бы pg графами научить отвечать)

Вот как-то так:

 a.id | a.info... |      a.posts       | p.info... | p.authors
------+-----------+--------------------+-----------+--------
    1 |           | ARRAY[id1, id2...] |           | ARRAY

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

WITH p AS (
	SELECT p.id, p.title, p.author_id FROM posts p
), a AS (
	SELECT a.id, a.name, array_agg(p.id) posts FROM authors a
	LEFT OUTER JOIN p ON p.author_id = a.id
	GROUP BY a.id
)
SELECT a.id, a.name, a.posts, p.id, p.title, p.author_id
FROM a
RIGHT OUTER JOIN p ON p.id = a.id;

Вместо id = id, наверное, лучше row_number() сравнивать в двух таблицах, компактнее будет, если id разные.

Ну рад что тебе так понравилось, я конечно советую изучить GrapQL чтобы не писать свои велосипеды ибо там очень много продумано вроде кешей и языка запросов очень советую, есть скажем Relay это React + GrapQL и он вообще выносит мозг ибо там всё очень очень круто.

Ну вот тут ты идёшь по пути близкому к NoSQL делая формат хранения данных удобных под себя, но это кстати очень хорошо ибо Postgres уже как несколько релизов активно обзаводиться NoSQL возможностями в сочетании с расширяемостью и простым и надёжным реляционным фундаментом.

Да идея с массивом и сам запрос хорошие, можно план?

Сорян что 3 дня прошло, вот такой план GraphQL-подобного запроса из моего коммента чуть выше:

Merge Left Join  (cost=3856.66..11406.66 rows=500000 width=108) (actual time=248.124..300.045 rows=10001 loops=1)
  Merge Cond: (p.id = a.id)
  CTE p
    ->  Seq Scan on posts p_1  (cost=0.00..627.00 rows=10000 width=39) (actual time=0.014..12.094 rows=10001 loops=1)
  CTE a
    ->  GroupAggregate  (cost=1300.89..1500.89 rows=10000 width=47) (actual time=97.709..159.477 rows=10000 loops=1)
          Group Key: a_1.id
          ->  Sort  (cost=1300.89..1325.89 rows=10000 width=19) (actual time=97.679..117.655 rows=17115 loops=1)
                Sort Key: a_1.id
                Sort Method: quicksort  Memory: 1883kB
                ->  Hash Right Join  (cost=299.00..636.50 rows=10000 width=19) (actual time=23.493..70.472 rows=17115 loops=1)
                      Hash Cond: (p_2.author_id = a_1.id)
                      ->  CTE Scan on p p_2  (cost=0.00..200.00 rows=10000 width=8) (actual time=0.004..10.387 rows=10001 loops=1)
                      ->  Hash  (cost=174.00..174.00 rows=10000 width=15) (actual time=23.454..23.454 rows=10000 loops=1)
                            Buckets: 16384  Batches: 1  Memory Usage: 587kB
                            ->  Seq Scan on authors a_1  (cost=0.00..174.00 rows=10000 width=15) (actual time=0.015..11.149 rows=10000 loops=1)
  ->  Sort  (cost=864.39..889.39 rows=10000 width=40) (actual time=47.652..57.131 rows=10001 loops=1)
        Sort Key: p.id
        Sort Method: quicksort  Memory: 1166kB
        ->  CTE Scan on p  (cost=0.00..200.00 rows=10000 width=40) (actual time=0.021..35.556 rows=10001 loops=1)
  ->  Sort  (cost=864.39..889.39 rows=10000 width=68) (actual time=200.462..209.876 rows=10000 loops=1)
        Sort Key: a.id
        Sort Method: quicksort  Memory: 1247kB
        ->  CTE Scan on a  (cost=0.00..200.00 rows=10000 width=68) (actual time=97.714..187.193 rows=10000 loops=1)
Planning time: 0.219 ms
Execution time: 311.592 ms

Вариант с вложенными left join'ами более эффективен, выглядит понятнее, и раскладывает записи так, как нужно моей орме: (этот запрос аналогичен предыдущему):

GroupAggregate  (cost=1727.89..1927.89 rows=10000 width=47) (actual time=95.670..169.677 rows=10000 loops=1)
  Group Key: a.id
  ->  Sort  (cost=1727.89..1752.89 rows=10000 width=50) (actual time=95.639..121.155 rows=17115 loops=1)
        Sort Key: a.id
        Sort Method: quicksort  Memory: 2493kB
        ->  Hash Right Join  (cost=299.00..1063.50 rows=10000 width=50) (actual time=22.601..69.201 rows=17115 loops=1)
              Hash Cond: (p.author_id = a.id)
              ->  Seq Scan on posts p  (cost=0.00..627.00 rows=10000 width=39) (actual time=0.011..10.136 rows=10001 loops=1)
              ->  Hash  (cost=174.00..174.00 rows=10000 width=15) (actual time=22.558..22.558 rows=10000 loops=1)
                    Buckets: 16384  Batches: 1  Memory Usage: 587kB
                    ->  Seq Scan on authors a  (cost=0.00..174.00 rows=10000 width=15) (actual time=0.018..10.721 rows=10000 loops=1)
Planning time: 0.332 ms
Execution time: 182.445 ms

-- авторы с постами, на том и другом можно делать where и прочее
authors = Author\select(?)\include(Post\select(?)\where(?))
authors[1].posts[1] -- и так вот просто можно у первого автора взять первый пост
-- и по такому принципу можно вкладывать инклуды друг в друга, совмещать и творить безумие,
-- умещающееся в 1 запрос к базе, круто же
authors = Author\include posts: {GoodComment --[[модель, кот. связана с постами]], 'ok_comments' --[[имя связи для постов]], BadComment\where too_bad: false --[[подзапрос, где модель связана с постами]]}

Один недостаток подхода - array_agg(ROW(где нет связных строк)) возвращает {()}, а не {}, как хотелось бы, это можно как-то фильтровать в самом запросе, можно и на получателе.