powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / очередной велосипедист
25 сообщений из 48, страница 1 из 2
очередной велосипедист
    #38803682
?Ы
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
хотя и не пятница

http://habrahabr.ru/post/242999/

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

К примеру если [равномерное "в большом"] заполнение сделано по шаблону "островки" по m через лакуны по N, где N >>m -- верхние границы островков будут попадать в выборку в ~N раз более часто, чем "сидящие в толще островков".

более общо -- любое заполнение id с дырками и островами приводит к неравновероятности выборок по алгоритму по ссылке.


т.е. приглашаю желающих предложить сглаживающие островной эффект алгоритмы, не требующие полного перебора мн-ва id. (а например всего-то редкого прощупывания его плотности тем же монтекарлом).
Есть ли такие ?
К каким прореженным множествам id применимы ?

-- для не очень больших островков можно пуститься на неглубокий рендомный оффсет, но кто ж гарантирует отсутствие больших глубин.
...
Рейтинг: 0 / 0
очередной велосипедист
    #38803746
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
?Ыхотя и не пятница

http://habrahabr.ru/post/242999/

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

К примеру если [равномерное "в большом"] заполнение сделано по шаблону "островки" по m через лакуны по N, где N >>m -- верхние границы островков будут попадать в выборку в ~N раз более часто, чем "сидящие в толще островков".

более общо -- любое заполнение id с дырками и островами приводит к неравновероятности выборок по алгоритму по ссылке.


т.е. приглашаю желающих предложить сглаживающие островной эффект алгоритмы, не требующие полного перебора мн-ва id. (а например всего-то редкого прощупывания его плотности тем же монтекарлом).
Есть ли такие ?
К каким прореженным множествам id применимы ?

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

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

Если предполагать что множество ID не слишком сильно разрежено (ну скажем не более чем в 100 раз разница между количества записей и разности между min/max id).
В таком случае можно решить следующим образом (я так делал) - просто в цикле запросом или хранимкой выбираются записи по случайному id из диапазона min/max пока не набератся нужные N. Подход работает и обеспевивает честный random(). Но становится неэффективен если множество id сильно разрежено.

--Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
очередной велосипедист
    #38803754
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Maxim Boguk,

"высший разум" в исходном посте попробовал решить проблему разреженной наумерации именно для моего алгоритма... но естественно он получил проблему краев на островах.
...
Рейтинг: 0 / 0
очередной велосипедист
    #38803760
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Maxim Boguk,

т.е. если заменить
t.id > min + (max - min) * random()
на
t.id = min + (max - min) * random()

и переписать запрос слегка (чтобы он нормально miss обрабатывал) то будет нормальный алгоритм без краевых эффектов.
...
Рейтинг: 0 / 0
очередной велосипедист
    #38803780
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
?Ы,

можно воспользоваться имеющейся статистикой по полю histogram_bounds из pg_stats , там должны быть границы диапазонов, вероятность попадания в которые примерно одинакова.

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

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

в исходный запрос автора не сильно вникал.
...
Рейтинг: 0 / 0
очередной велосипедист
    #38803791
?Ы
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Maxim BogukMaxim Boguk,

т.е. если заменить
t.id > min + (max - min) * random()
на
t.id = min + (max - min) * random()

и переписать запрос слегка (чтобы он нормально miss обрабатывал) то будет нормальный алгоритм без краевых эффектов.

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

Да , это наверное работает.
хотя бы на равномерном распределении с равномерными лакунами.
надо поразмышлять.
...
Рейтинг: 0 / 0
очередной велосипедист
    #38803901
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
?ЫMaxim BogukMaxim Boguk,

т.е. если заменить
t.id > min + (max - min) * random()
на
t.id = min + (max - min) * random()

и переписать запрос слегка (чтобы он нормально miss обрабатывал) то будет нормальный алгоритм без краевых эффектов.

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

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

Вот эта версия работает на совершенно любом распределении и работает корректно.
Но на сильно разреженных таблицах конечно не быстрая:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
WITH RECURSIVE
range AS (
  SELECT min(id) AS min, max(id) AS max FROM table1
), 
r AS (
    SELECT array[]::integer[] AS res
  UNION ALL
    SELECT CASE WHEN (id IS NULL) THEN res ELSE res||id END 
    FROM (SELECT (min + (max - min) * random())::int AS rnd,res FROM r JOIN range ON true) AS _r
    LEFT JOIN table1 ON id=rnd AND id <> all(res) 
    WHERE 
      id IS NULL OR coalesce(array_length(res,1),0)<=10 
) 
SELECT unnest(res) FROM (
  SELECT res FROM r ORDER BY array_length(res,1) DESC NULLS LAST LIMIT 1
) AS t;



PS: кто был на PgMaster 2014 в Питере должны опознать стиль да :).

--Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
очередной велосипедист
    #38803916
?Ы
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Maxim BogukЕсли предполагать что множество ID не слишком сильно разрежено (ну скажем не более чем в 100 раз разница между количества записей и разности между min/max id).
В таком случае можно решить следующим образом (я так делал) - просто в цикле запросом или хранимкой выбираются записи по случайному id из диапазона min/max пока не набератся нужные N. Подход работает и обеспевивает честный random(). Но становится неэффективен если множество id сильно разрежено .


PS думаю можно обобщить {min max} на range[], min-max -->length(range[]) (т.е. SUM(length (range[i]))


при miss пополнять наш массив range[] (информцию о дырках) ну и т.д. При малом количестве очень больших дыр -- будет работать сносно. [1..10 , 1E8::bigint] [т.е. большие сплошные дырки будут быстро выколоты]

но это "улучшение" не очень эффективно -- при числе дыр сравнимым с числом точек (добавит изрядную константу--множитель в стоимость, не улучшив производительность)
...
Рейтинг: 0 / 0
очередной велосипедист
    #38804183
Фотография Misha Tyurin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Maxim Boguk,

> Но на сильно разреженных таблицах конечно не быстрая

интересно оценить бы точнее. может руки дойдут, попробую.
...
Рейтинг: 0 / 0
очередной велосипедист
    #38804187
Фотография Misha Tyurin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Misha Tyurin,

ну да. отношение кол-ва элементов N к длине интервала min-max M -- это основа.
тогда на каждом шаге будем попадать с вероятностью P = N/M, а мазать с Q = 1 - P = (M - N)/M.

и нам надо набрать k элементов.

а дальше придите знатоки и нарисуйте формулу для числа попыток i = I(N, M, k).
это какая-то классическая задача. не могу вспомнить/придумать сходу...
...
Рейтинг: 0 / 0
очередной велосипедист
    #38804203
Фотография Misha Tyurin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Misha Tyurin,

https://ru.wikipedia.org/wiki/Формула_Бернулли

это всё про "Формула_Бернулли" и с этим связанным, вроде оно.

http://www.math.com.ua/mathdir/formula_bernoulli.html

http://www.matburo.ru/ex_tv.php?p2=bernul3

"
Решение задачи на формулу наивероятнейшего числа успехов
Задача 3: Сколько следует сыграть партий в шахматы с вероятностью победы в одной партии, равной 1/3, чтобы наивероятнейшее число побед было равно 5?
"

вроде даже можно всё найти, если интернет не врёт
...
Рейтинг: 0 / 0
очередной велосипедист
    #38804208
Фотография Misha Tyurin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Misha Tyurin,

если интервал min-max большой и таблица большая, а число сколько надо взять сильно маленькое.
и если вероятность попасть при этом 1%,
то чтобы набрать 5 элементов, надо щелкнуть 500 раз.

как-то очень интуитивно даже
...
Рейтинг: 0 / 0
очередной велосипедист
    #38804252
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Misha Tyurin,

собственно именно такие прикидки у меня и были...
в общем то 500 index only probes - оно дешево
даже 5000 приемлемо на самом деле по скорости.
...
Рейтинг: 0 / 0
очередной велосипедист
    #38804302
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Misha Tyurin,

можно решить такую задачу. пусть p=count/(max-min) - вероятность случайным образом попасть в элемент, q = 1-p, k - количество необходимых элементов (могут повторяться).
вопрос: сколько раз нужно выбирать, чтобы с вероятностью 99% выбрать не менее k элементов?

если вспомнить теорию вероятностей, в частности схему Бернулли и интегральную теорему Муавра - Лапласа, то можно получить приближенное уравнение, справедливое при достаточно большом n*p*q:
Код: sql
1.
(k-n*p)/sqrt(n*p*q)=-2.34



поиграться с параметрами можно тут .

например, если p=0.01 и нужно получить k=100 элементов, то нужно выбирать 12615 раз, что в общем-то близко к очевидной оценке k/p=10000.
а вот при p=0.01 и k=5 получается уже 1357 вместо 500, хотя это уже на границе применимости формулы.

если кому не лень - проверьте экспериментально. конечно, можно брать доверительный интервал не 99%, а поменьше, тогда и числа будут меньше.
...
Рейтинг: 0 / 0
очередной велосипедист
    #38804715
zeon11
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
?Ыхотя и не пятница

http://habrahabr.ru/post/242999/

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

К примеру если [равномерное "в большом"] заполнение сделано по шаблону "островки" по m через лакуны по N, где N >>m -- верхние границы островков будут попадать в выборку в ~N раз более часто, чем "сидящие в толще островков".

более общо -- любое заполнение id с дырками и островами приводит к неравновероятности выборок по алгоритму по ссылке.


т.е. приглашаю желающих предложить сглаживающие островной эффект алгоритмы, не требующие полного перебора мн-ва id. (а например всего-то редкого прощупывания его плотности тем же монтекарлом).
Есть ли такие ?
К каким прореженным множествам id применимы ?

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

Задача редкая, и на ум приходит только одна цель, по которой рандомно что-то нужно выдернуть из БД - показать случайный набор вопросов испытуемому. Следовательно такой набор вопросов практически не изменяем и для ликвидации "окон" стоит завести отдельное "непрерывное" поле, по которому и вести случайный поиск. Периодически, по необходимости это поле обновлять. Дёшево и сердито.
...
Рейтинг: 0 / 0
очередной велосипедист
    #38804745
?Ы
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
zeon11 <>
Задача редкая, и на ум приходит только одна цель, по которой рандомно что-то нужно выдернуть из БД - показать случайный набор вопросов испытуемому. Следовательно такой набор вопросов практически не изменяем и для ликвидации "окон" стоит завести отдельное "непрерывное" поле, по которому и вести случайный поиск. Периодически, по необходимости это поле обновлять. Дёшево и сердито.
кхм. как бы это помяхше. на табличке в 10^8 (даже партицированной) это уже и не дёшево и не сердито.

а как джопп авовакуума-то будет рад работе.
да и редискенс -- не подарок , простите, reindex .


а про редкость задачи -- дык лотерейщики жеж (те же розыгрыши лохов у операторов и прочие завлекалки - несть им числа).
(или вот тот же монтекарло попытаться натянуть в каком- нить алгоритме -- она и встанет).
...
Рейтинг: 0 / 0
очередной велосипедист
    #38804820
Фотография Misha Tyurin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Alexius,

> p=0.01 и k=5 получается уже 1357 вместо 500

интегралы эти, про Муавра - Лапласа, замечал ночью, но далее не стал углубляться в школьные годы чудесные)

угу, но в два раза промазать это еще боле менее)
так что на 5 попаданий надо будет прыгнуть в индекс (возможно индекс_онли) 1000 раз грубо. ок
...
Рейтинг: 0 / 0
очередной велосипедист
    #38805449
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Вот последняя самая быстрая версия:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
WITH RECURSIVE
r AS (
    SELECT array[]::integer[] AS res,min(id) AS min, max(id)-min(id) AS range, 0 as cnt FROM table1
  UNION ALL
    SELECT CASE WHEN id IS NULL THEN res ELSE res||id END, min, range, CASE WHEN id IS NULL THEN cnt ELSE cnt+1 END
    FROM (SELECT (min + range * random())::int AS _id, * FROM r) AS tmp
    LEFT JOIN table1 ON id=_id AND NOT id=ANY(res)
    WHERE
      cnt<10
)
SELECT unnest(res) FROM (
  SELECT res FROM r ORDER BY cnt DESC NULLS LAST LIMIT 1
) AS t;



время получения 10 записей из таблицы с 1M записей с разрежением 100 (т.е. 1миллион id в диапазоне до 100M) - 3-5ms (смотря как random попадает). (~1000 итераций в среднем)
время получения 1000 записей из той же таблицы (100.000 итераций в среднем) 450ms (весьма стабильное но нужен большой work_mem).

изменения:
1)выяснено что JOIN c
Код: plsql
1.
2.
3.
range AS (
  SELECT min(id) AS min, max(id) AS max FROM table1
), 


в цикле занимает заметное время и сильно быстрее пробрасывать min/max через r на каждой итерации.

2)убрано вычисление (max - min) из цикла

3)версия с ручным cnt вместо coalesce(array_length(res,1),0) быстрее на 5-10%
заодно и последний unnest блок стал побыстрее изза сортировки по константе а не по функции от массива.

4)странная конструкция
id IS NULL OR ...<=10
которая могла приводить к получению N+1 и более вариантов при редком стечении обстоятельств
заменена на простую и понятную cnt<10 (заодно стало понятнее что творится).

Вообще оказалось что пробрасывать дополнительные колонки через рекурсивный WITH очень дешево чем надо пользоваться.

70% времени теперь занимают Index Only Scan using table1_pkey on table1 так что дальше ускорять смысла нет. Это предел для этого алгоритма.

--Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
очередной велосипедист
    #38805517
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Maxim Boguk,

Нет мне всетаки не спится...
В общем вот версия которая на коротких списках работает приблизительно так же как и предыдущая а вот на списках в 1000 случайных работает в 3 раза быстрее и главное требует на 3 порядка меньше памяти для работы (предыдущая версия требовала на 1000 случайных значений при 0.01 заполнении больше 200MB текущая в 1MB влезает и работает за 150ms):

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
WITH RECURSIVE
r AS (
    SELECT array[]::integer[] AS res,min(id) AS min, max(id)-min(id) AS range FROM table1
  UNION ALL
    SELECT res||ARRAY(SELECT id FROM table1 WHERE id IN (SELECT (min+range*random())::int FROM generate_series(1,1000)) AND NOT id=ANY(res)), min, range
    FROM r
    WHERE
      coalesce(array_length(res,1),0)<1000
)
SELECT unnest(res) FROM (
  SELECT res FROM r ORDER BY array_length(res,1) DESC NULLS LAST LIMIT 1
) AS t LIMIT 1000;


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

Заодно получилась сильно укороченная и более понятная версия неоптимизированного (с скоростью работы как в предыдущем посте) запроса:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
WITH RECURSIVE
r AS (
    SELECT array[]::integer[] AS res,min(id) AS min, max(id)-min(id) AS range FROM table1
  UNION ALL
    SELECT res||ARRAY(SELECT id FROM table1 WHERE id=(SELECT (min+range*random())::int) AND NOT id=ANY(res)), min, range
    FROM r WHERE coalesce(array_length(res,1),0)<10
)
SELECT unnest(res) FROM (
  SELECT res FROM r ORDER BY array_length(res,1) DESC NULLS LAST LIMIT 1
) AS t;



И кто после этого скажет что на SQL неитересно программировать :).

--Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
очередной велосипедист
    #38806401
Фотография Misha Tyurin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Maxim Boguk,

> Идея в том чтобы на каждой итерации пробовать получить не по одной случайной записи а сразу по N.

) мощь и сила!
...
Рейтинг: 0 / 0
очередной велосипедист
    #38806763
?Ы
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Забавно, что пассажыр на хабре упоротствует.

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

смешные они там.

PS тут тоже, помнится, кто-то про карму заикался, не будем тыкать пальцем.
...
Рейтинг: 0 / 0
очередной велосипедист
    #38806787
Фотография Misha Tyurin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
PS тут тоже, помнится, кто-то про карму заикался, не будем тыкать пальцем.
это жесткий оффтоп, вот по этому поводу, в том числе, чтобы такого не было, я и предлагал вводить доп требования. можно найти по моему профилю, моим темам.
...
Рейтинг: 0 / 0
очередной велосипедист
    #38806925
Шавлюк Евгений
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Maxim Boguk,

А что даст запрос если в таблице будут 5 записей с ID = {1, 2, 2000000000, 2000000001, 2000000002}

P.S. попал в топик с Хабра
...
Рейтинг: 0 / 0
очередной велосипедист
    #38806965
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Шавлюк ЕвгенийMaxim Boguk,

А что даст запрос если в таблице будут 5 записей с ID = {1, 2, 2000000000, 2000000001, 2000000002}

P.S. попал в топик с Хабра

будет очень долго думать...
про разреженные таблицы что алгоритм работает с ними не очень хорошо.

если разрежение 1:100 - скорость 5ms на выбор 10 записей из 10M строк
разрежение 1:1000 - 50ms на выбор 10 записей из 10M строк
дальше алгоритм уже неприменим скорее.

Алгоритм с хабра никогда вообще число 2 не этом наборе не выдаст (точнее выдаст с вероятностью 1/2000000000). Что показывает его некорректность.
...
Рейтинг: 0 / 0
очередной велосипедист
    #38807061
?Ы
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Maxim BogukMaxim Boguk,
<>
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
WITH RECURSIVE
r AS (
    SELECT array[]::integer[] AS res,min(id) AS min, max(id)-min(id) AS range FROM table1
  UNION ALL
    SELECT res||ARRAY(SELECT id FROM table1 WHERE id=(SELECT (-0.5 + min+(1+range)*random())::int) AND NOT id=ANY(res)), min, range
    FROM r WHERE coalesce(array_length(res,1),0)<10
)
SELECT unnest(res) FROM (
  SELECT res FROM r ORDER BY array_length(res,1) DESC NULLS LAST LIMIT 1
) AS t;




красиво

тоько я всё время сам забываю про краевые эффекты, когда краям дается вес по 0.5, а в середке -- по 1.

кажется с поправкой оно вернее (могу врать).
...
Рейтинг: 0 / 0
25 сообщений из 48, страница 1 из 2
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / очередной велосипедист
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]