посмотрите, вот эта штука вам не поможет?

Сейчас почитаю, спасибо!

Тут явно видна проблема, что ищутся 10 ближайших. А мне строго нужно 10 выше значения и 10 ниже

я боюсь, что для реализации именно 10 сверху и 10 снизу, вам придется немного погрузиться в си и поправить функцию distance в коде btree_gist

https://postgrespro.ru/docs/postgrespro/9.5/gist-extensibility.html

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

вряд ли, если таблица реально большая, то получать ее через sequence scan - боль и деградация системы. ну и вертеть в памяти приложения - тоже боль

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

а как раз по 20 значений

ну так у вас же в таблице строки лежат как бог на душу положит

вам придется сканировать почти все

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

проще тогда уж выбрать из гист индекса ближайшую 1000 строк и их уже сравнивать

Почему проще?

Я буду просто последовательно вытаскивать строки и анализировать их

Можно ламерское предложение? Разбить задачу на две - поиск 10 сверху, и поиск 10 снизу.

что мне даст GiST индекс по 1000 строк?

что мне даст GiST индекс по 1000 строк?

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

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

Ребят, всем привет. Где можно найти стажера со знанием SQL в Московский офис?

Посоветуйте плиз

Нужно два чела - один опытный воторой стажёр

Можно ламерское предложение? Разбить задачу на две - поиск 10 сверху, и поиск 10 снизу.

++

Можно ламерское предложение? Разбить задачу на две - поиск 10 сверху, и поиск 10 снизу.

И правда гениально))

Тупо два запроса и отсутствие лишнего геморроя

Тупо два запроса и отсутствие лишнего геморроя

Даже один, но с union

Тупо два запроса и отсутствие лишнего геморроя

Да нет, я еще раз перечитал btree_gist, он все равно дает данные по определенному столбцу. У меня же ситуация, нужно найти строку по userid, а потом уже ближайшие к ней 10 выше и 10 ниже строк

Да нет, я еще раз перечитал btree_gist, он все равно дает данные по определенному столбцу. У меня же ситуация, нужно найти строку по userid, а потом уже ближайшие к ней 10 выше и 10 ниже строк

select userid from test_table where userid <-> (select userid from test_table where amount = 100) and userid <= (select userid from test_table where amount = 100) limit 10;

так это с подзапросами

к тому же я знаю userId, и не знаю amount

ааа

Вам нужно ближайшие amount?

кажется я начинаю догонять

Вам нужно ближайшие amount?

ага

Ну тогда все наоборот)

select userid, amount from test_table where amount <= (select amount from test_table where userId = myId) and userId = myId limit 10;

но это все равно с подзапросом

мне кажется с номерами строк и подзапросом даже быстрее будет

там вообще никаких индексов не нужно

Вы все не правильно понимаете, правда((

Вы все не правильно понимаете, правда((

Да, точно, мой запрос не правельный

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

select userid from test_table where userid <-> (select userid from test_table where amount = 100) and userid <= (select userid from test_table where amount = 100) limit 10;

select userid from test_table where amount <-> (select amount from test_table where userId = myId) and amount <= (select amount from test_table where userId = myId) limit 10;

Но тут будут проблемы, если несколько юзеров имеют одинаковый amount, т.к. amount не уникален

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

Проблема в первом шаге, как его сделать без подзапросов :)

Но тут будут проблемы, если несколько юзеров имеют одинаковый amount, т.к. amount не уникален

Добавьте условие в конец типа and amount <-> (select amount from test_table where userid = myid) <> 0, это отсечёт тех, у кого такой же amount

Добавьте условие в конец типа and amount <-> (select amount from test_table where userid = myid) <> 0, это отсечёт тех, у кого такой же amount

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

я так понимаю нету никаких правил, какую именно строку он вернет при каждом запросе с одинаковым amount

Я запутался)) вам нужно 10 меньше включая тако же количество, или без них?

Я запутался)) вам нужно 10 меньше включая тако же количество, или без них?

Вот я получил строку, с userId заданным. У меня таблица отсортирована по amount. Теперь мне нужно 10 выше и 10 ниже не включая эту строку

И чем все таки решения с индексом лучше вот этого

create table test(a integer, b text); insert into test (a,b) select generate_series(1,30), ''; update test set b='mark' where a=13; with numbered_test as ( select row_number() over(order by a), a, b from test ) select a,b from numbered_test where row_number between (select row_number from numbered_test where b='mark') - 10 and (select row_number from numbered_test where b='mark') + 10;

Если в обоих случаях будут вложенные селекты

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

А, понял, да

Т.е. получается оконные функции в любом случае зло

Если они нумерются в запросе

и выборка большая

Ну тогда получается остается решить, как точно относительно строки с помощью индексов отсчитать 10 строк сверху и снизу

Ну, они зло в исполнении постгреса. Сколь я знаю, он не умеет для них все ещё конвейерную обработку - то есть вначале он все должен построить, а потом уже фильтровать. Вроде оракл и микрософт в этом плане круче, но я не готов поручиться)

Ну тогда получается остается решить, как точно относительно строки с помощью индексов отсчитать 10 строк сверху и снизу

Да, иначе вы очень просядьте по производительности

Я кажется понял. Можно в union добавить еще когда дистанция равна нулю. В этом случае можем получить чуть больше чем 10 записей сверху и/или снизу

select userid from test_table where amount <-> (select amount from test_table where userId = myId) and amount <= (select amount from test_table where userId = myId) limit 11 union select userid from test_table where amount <-> (select amount from test_table where userId = myId) and amount >= (select amount from test_table where userId = myId) limit 11 union select userid from test_table where amount <-> (select amount from test_table where userId = myId) = 0 limit 21

А лишние строки можно обрезать в приложении уже

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

ERROR: S client not available

Может не войти. Предположим у нас 10 одинаковых значений amount. Пусть первый запрос возвратит все которые выше первой строки, второй все которые ниже 10-ой. Тогда последний добавит все одинаковые строки

Может не войти. Предположим у нас 10 одинаковых значений amount. Пусть первый запрос возвратит все которые выше первой строки, второй все которые ниже 10-ой. Тогда последний добавит все одинаковые строки

Оу! Все, каюсь, я осознал)

Потому на уровне приложения уже находим строку с заданным userId и относительно неё отсчитываем 10 сверху и 10 снизу

А как в этом случае union будет работать? Они реально в один запрос объединит?

Потому на уровне приложения уже находим строку с заданным userId и относительно неё отсчитываем 10 сверху и 10 снизу

Ну, можно и полученные данные положить в подзапрос (например в cte через with) и дальше тем же SQL отсечь;) Ну, как тру dba ;)

А как в этом случае union будет работать? Они реально в один запрос объединит?

Да, он склеивает без повторяющихся строк результаты

Ну, можно и полученные данные положить в подзапрос (например в cte через with) и дальше тем же SQL отсечь;) Ну, как тру dba ;)

Ага, или так :)

А кто-нибудь в курсе, группирование запросов в libpg вошло в итоге или нет в 9.6.0 релизе?

Вот этот патч

https://www.postgresql.org/message-id/flat/CAMsr+YFUjJytRyV4J-16bEoiZyH=4nj+sQ7JP9ajwz=B4dMMZw@mail.gmail.com#CAMsr+YFUjJytRyV4J-16bEoiZyH=4nj+sQ7JP9ajwz=B4dMMZw@mail.gmail.com

Я просто не очень понимаю как в итоге отследить статус этого патча, вошел или нет

Denis Теперь другой вопрос. Допустим та таблица, а которой я говорил - это вьюха. Есть ли смысл заюзать materialized view чтобы можно было заюзать индекс btree_gist учитывая что исходные данные будут обновлятся часто и само представление будет большим? Я думаю делать refresh к это вьюхе перед тем как запросить данные через индекс btree_gist

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

А OFFSET так же работает как и ROW_NUMBER, или ему не требуется перенумеровывать?

можно еще так

select userId from test_table offset (select row_number - 11 from test_table where userId = myId) limit 21

Denis Теперь другой вопрос. Допустим та таблица, а которой я говорил - это вьюха. Есть ли смысл заюзать materialized view чтобы можно было заюзать индекс btree_gist учитывая что исходные данные будут обновлятся часто и само представление будет большим? Я думаю делать refresh к это вьюхе перед тем как запросить данные через индекс btree_gist

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

А OFFSET так же работает как и ROW_NUMBER, или ему не требуется перенумеровывать?

Оффсет - это страшная штука)) Ее можно использовать только при отладке, но не в промышленном коде. Проблема в том, что offset 1000000 на самом деле физически этот миллион строк считает с диска, но просто не покажет в результате запроса. И естественно, это будет медленно. Я помню, Маркус Винланд почти при каждом удобном случае кричит про опасность оффсета - http://use-the-index-luke.com/sql/partial-results/fetch-next-page

В matview могут быть индексы. "CREATE INDEX constructs an index on the specified column(s) of the specified relation, which can be a table or a materialized view." https://www.postgresql.org/docs/current/static/sql-createindex.html

В matview могут быть индексы. "CREATE INDEX constructs an index on the specified column(s) of the specified relation, which can be a table or a materialized view." https://www.postgresql.org/docs/current/static/sql-createindex.html

Серьезно? Тогда я сильно затупил.

Да, могут

В документации есть пример с GiST и file_fdw: https://www.postgresql.org/docs/current/static/rules-materializedviews.html

Матвью это ж обычная таблица с данными

Реальными, не вычисленными

Ну да, это понятно. Просто я думал, это не реализовано

Denis Теперь другой вопрос. Допустим та таблица, а которой я говорил - это вьюха. Есть ли смысл заюзать materialized view чтобы можно было заюзать индекс btree_gist учитывая что исходные данные будут обновлятся часто и само представление будет большим? Я думаю делать refresh к это вьюхе перед тем как запросить данные через индекс btree_gist

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

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

Так, сейчас тогда покажу исходную таблицу и по ней понятно будет

CREATE TABLE deals(id SERIAL PRIMARY KEY, user_id INTEGER, time TIMESTAMP, amount MONEY)

вот изначальная таблица

нужно составить рейтинг юзеров. Юзер обладает наибольшим рейтингом если сумма его amount за последнюю неделю максимально

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

Как сделать без materialized view не использова numrow не очень пока представляю