|
|
|
очередной велосипедист
|
|||
|---|---|---|---|
|
#18+
хотя и не пятница http://habrahabr.ru/post/242999/ забавно, пассажир, похоже не понимает, что это решение (как и все подобные) работает только в предположении сплошного (или хотя бы равномерного) заполнения id. К примеру если [равномерное "в большом"] заполнение сделано по шаблону "островки" по m через лакуны по N, где N >>m -- верхние границы островков будут попадать в выборку в ~N раз более часто, чем "сидящие в толще островков". более общо -- любое заполнение id с дырками и островами приводит к неравновероятности выборок по алгоритму по ссылке. т.е. приглашаю желающих предложить сглаживающие островной эффект алгоритмы, не требующие полного перебора мн-ва id. (а например всего-то редкого прощупывания его плотности тем же монтекарлом). Есть ли такие ? К каким прореженным множествам id применимы ? -- для не очень больших островков можно пуститься на неглубокий рендомный оффсет, но кто ж гарантирует отсутствие больших глубин. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 12.11.2014, 15:30:36 |
|
||
|
очередной велосипедист
|
|||
|---|---|---|---|
|
#18+
?Ыхотя и не пятница 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 ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 12.11.2014, 16:00:51 |
|
||
|
очередной велосипедист
|
|||
|---|---|---|---|
|
#18+
Maxim Boguk, "высший разум" в исходном посте попробовал решить проблему разреженной наумерации именно для моего алгоритма... но естественно он получил проблему краев на островах. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 12.11.2014, 16:04:03 |
|
||
|
очередной велосипедист
|
|||
|---|---|---|---|
|
#18+
Maxim Boguk, т.е. если заменить t.id > min + (max - min) * random() на t.id = min + (max - min) * random() и переписать запрос слегка (чтобы он нормально miss обрабатывал) то будет нормальный алгоритм без краевых эффектов. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 12.11.2014, 16:08:17 |
|
||
|
очередной велосипедист
|
|||
|---|---|---|---|
|
#18+
?Ы, можно воспользоваться имеющейся статистикой по полю histogram_bounds из pg_stats , там должны быть границы диапазонов, вероятность попадания в которые примерно одинакова. алгоритм тогда будет примерно такой: сначала случайным образом выбираем диапазон, а потом выбираем значение из диапазона. при правильно подобранном значении statistics_target и актуальной статистике теоретически должно работать. в исходный запрос автора не сильно вникал. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 12.11.2014, 16:16:35 |
|
||
|
очередной велосипедист
|
|||
|---|---|---|---|
|
#18+
Maxim BogukMaxim Boguk, т.е. если заменить t.id > min + (max - min) * random() на t.id = min + (max - min) * random() и переписать запрос слегка (чтобы он нормально miss обрабатывал) то будет нормальный алгоритм без краевых эффектов. т.е. чтобы не прибавлять лакунами веса краевым точкам. Да , это наверное работает. хотя бы на равномерном распределении с равномерными лакунами. надо поразмышлять. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 12.11.2014, 16:24:34 |
|
||
|
очередной велосипедист
|
|||
|---|---|---|---|
|
#18+
?Ы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. PS: кто был на PgMaster 2014 в Питере должны опознать стиль да :). --Maxim Boguk www.postgresql-consulting.ru ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 12.11.2014, 17:32:27 |
|
||
|
очередной велосипедист
|
|||
|---|---|---|---|
|
#18+
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] [т.е. большие сплошные дырки будут быстро выколоты] но это "улучшение" не очень эффективно -- при числе дыр сравнимым с числом точек (добавит изрядную константу--множитель в стоимость, не улучшив производительность) ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 12.11.2014, 17:41:54 |
|
||
|
очередной велосипедист
|
|||
|---|---|---|---|
|
#18+
Maxim Boguk, > Но на сильно разреженных таблицах конечно не быстрая интересно оценить бы точнее. может руки дойдут, попробую. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 13.11.2014, 01:43:39 |
|
||
|
очередной велосипедист
|
|||
|---|---|---|---|
|
#18+
Misha Tyurin, ну да. отношение кол-ва элементов N к длине интервала min-max M -- это основа. тогда на каждом шаге будем попадать с вероятностью P = N/M, а мазать с Q = 1 - P = (M - N)/M. и нам надо набрать k элементов. а дальше придите знатоки и нарисуйте формулу для числа попыток i = I(N, M, k). это какая-то классическая задача. не могу вспомнить/придумать сходу... ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 13.11.2014, 02:04:19 |
|
||
|
очередной велосипедист
|
|||
|---|---|---|---|
|
#18+
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? " вроде даже можно всё найти, если интернет не врёт ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 13.11.2014, 03:31:29 |
|
||
|
очередной велосипедист
|
|||
|---|---|---|---|
|
#18+
Misha Tyurin, если интервал min-max большой и таблица большая, а число сколько надо взять сильно маленькое. и если вероятность попасть при этом 1%, то чтобы набрать 5 элементов, надо щелкнуть 500 раз. как-то очень интуитивно даже ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 13.11.2014, 03:51:22 |
|
||
|
очередной велосипедист
|
|||
|---|---|---|---|
|
#18+
Misha Tyurin, собственно именно такие прикидки у меня и были... в общем то 500 index only probes - оно дешево даже 5000 приемлемо на самом деле по скорости. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 13.11.2014, 07:43:44 |
|
||
|
очередной велосипедист
|
|||
|---|---|---|---|
|
#18+
Misha Tyurin, можно решить такую задачу. пусть p=count/(max-min) - вероятность случайным образом попасть в элемент, q = 1-p, k - количество необходимых элементов (могут повторяться). вопрос: сколько раз нужно выбирать, чтобы с вероятностью 99% выбрать не менее k элементов? если вспомнить теорию вероятностей, в частности схему Бернулли и интегральную теорему Муавра - Лапласа, то можно получить приближенное уравнение, справедливое при достаточно большом n*p*q: Код: sql 1. поиграться с параметрами можно тут . например, если p=0.01 и нужно получить k=100 элементов, то нужно выбирать 12615 раз, что в общем-то близко к очевидной оценке k/p=10000. а вот при p=0.01 и k=5 получается уже 1357 вместо 500, хотя это уже на границе применимости формулы. если кому не лень - проверьте экспериментально. конечно, можно брать доверительный интервал не 99%, а поменьше, тогда и числа будут меньше. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 13.11.2014, 09:08:48 |
|
||
|
очередной велосипедист
|
|||
|---|---|---|---|
|
#18+
?Ыхотя и не пятница http://habrahabr.ru/post/242999/ забавно, пассажир, похоже не понимает, что это решение (как и все подобные) работает только в предположении сплошного (или хотя бы равномерного) заполнения id. К примеру если [равномерное "в большом"] заполнение сделано по шаблону "островки" по m через лакуны по N, где N >>m -- верхние границы островков будут попадать в выборку в ~N раз более часто, чем "сидящие в толще островков". более общо -- любое заполнение id с дырками и островами приводит к неравновероятности выборок по алгоритму по ссылке. т.е. приглашаю желающих предложить сглаживающие островной эффект алгоритмы, не требующие полного перебора мн-ва id. (а например всего-то редкого прощупывания его плотности тем же монтекарлом). Есть ли такие ? К каким прореженным множествам id применимы ? -- для не очень больших островков можно пуститься на неглубокий рендомный оффсет, но кто ж гарантирует отсутствие больших глубин. Задача редкая, и на ум приходит только одна цель, по которой рандомно что-то нужно выдернуть из БД - показать случайный набор вопросов испытуемому. Следовательно такой набор вопросов практически не изменяем и для ликвидации "окон" стоит завести отдельное "непрерывное" поле, по которому и вести случайный поиск. Периодически, по необходимости это поле обновлять. Дёшево и сердито. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 13.11.2014, 12:39:35 |
|
||
|
очередной велосипедист
|
|||
|---|---|---|---|
|
#18+
zeon11 <> Задача редкая, и на ум приходит только одна цель, по которой рандомно что-то нужно выдернуть из БД - показать случайный набор вопросов испытуемому. Следовательно такой набор вопросов практически не изменяем и для ликвидации "окон" стоит завести отдельное "непрерывное" поле, по которому и вести случайный поиск. Периодически, по необходимости это поле обновлять. Дёшево и сердито. кхм. как бы это помяхше. на табличке в 10^8 (даже партицированной) это уже и не дёшево и не сердито. а как джопп авовакуума-то будет рад работе. да и редискенс -- не подарок , простите, reindex . а про редкость задачи -- дык лотерейщики жеж (те же розыгрыши лохов у операторов и прочие завлекалки - несть им числа). (или вот тот же монтекарло попытаться натянуть в каком- нить алгоритме -- она и встанет). ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 13.11.2014, 12:57:53 |
|
||
|
очередной велосипедист
|
|||
|---|---|---|---|
|
#18+
Alexius, > p=0.01 и k=5 получается уже 1357 вместо 500 интегралы эти, про Муавра - Лапласа, замечал ночью, но далее не стал углубляться в школьные годы чудесные) угу, но в два раза промазать это еще боле менее) так что на 5 попаданий надо будет прыгнуть в индекс (возможно индекс_онли) 1000 раз грубо. ок ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 13.11.2014, 13:46:56 |
|
||
|
очередной велосипедист
|
|||
|---|---|---|---|
|
#18+
Вот последняя самая быстрая версия: Код: plsql 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. время получения 10 записей из таблицы с 1M записей с разрежением 100 (т.е. 1миллион id в диапазоне до 100M) - 3-5ms (смотря как random попадает). (~1000 итераций в среднем) время получения 1000 записей из той же таблицы (100.000 итераций в среднем) 450ms (весьма стабильное но нужен большой work_mem). изменения: 1)выяснено что JOIN c Код: plsql 1. 2. 3. в цикле занимает заметное время и сильно быстрее пробрасывать 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 ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 13.11.2014, 19:39:13 |
|
||
|
очередной велосипедист
|
|||
|---|---|---|---|
|
#18+
Maxim Boguk, Нет мне всетаки не спится... В общем вот версия которая на коротких списках работает приблизительно так же как и предыдущая а вот на списках в 1000 случайных работает в 3 раза быстрее и главное требует на 3 порядка меньше памяти для работы (предыдущая версия требовала на 1000 случайных значений при 0.01 заполнении больше 200MB текущая в 1MB влезает и работает за 150ms): Код: plsql 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. Идея в том чтобы на каждой итерации пробовать получить не по одной случайной записи а сразу по N. Крайне выгодно для разреженных таблиц (так как резко сокращает число итераций). Для плотных таблиц в общем баш на баш будет. Заодно получилась сильно укороченная и более понятная версия неоптимизированного (с скоростью работы как в предыдущем посте) запроса: Код: plsql 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. И кто после этого скажет что на SQL неитересно программировать :). --Maxim Boguk www.postgresql-consulting.ru ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 13.11.2014, 21:42:15 |
|
||
|
очередной велосипедист
|
|||
|---|---|---|---|
|
#18+
Maxim Boguk, > Идея в том чтобы на каждой итерации пробовать получить не по одной случайной записи а сразу по N. ) мощь и сила! ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 14.11.2014, 14:39:20 |
|
||
|
очередной велосипедист
|
|||
|---|---|---|---|
|
#18+
Забавно, что пассажыр на хабре упоротствует. ему показали, что его "решение" НЕ решает поставленную им же задачу. от слова вообще. так божья росца смешные они там. PS тут тоже, помнится, кто-то про карму заикался, не будем тыкать пальцем. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 14.11.2014, 17:57:33 |
|
||
|
очередной велосипедист
|
|||
|---|---|---|---|
|
#18+
PS тут тоже, помнится, кто-то про карму заикался, не будем тыкать пальцем. это жесткий оффтоп, вот по этому поводу, в том числе, чтобы такого не было, я и предлагал вводить доп требования. можно найти по моему профилю, моим темам. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 14.11.2014, 18:27:20 |
|
||
|
очередной велосипедист
|
|||
|---|---|---|---|
|
#18+
Maxim Boguk, А что даст запрос если в таблице будут 5 записей с ID = {1, 2, 2000000000, 2000000001, 2000000002} P.S. попал в топик с Хабра ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 15.11.2014, 00:50:49 |
|
||
|
очередной велосипедист
|
|||
|---|---|---|---|
|
#18+
Шавлюк ЕвгенийMaxim Boguk, А что даст запрос если в таблице будут 5 записей с ID = {1, 2, 2000000000, 2000000001, 2000000002} P.S. попал в топик с Хабра будет очень долго думать... про разреженные таблицы что алгоритм работает с ними не очень хорошо. если разрежение 1:100 - скорость 5ms на выбор 10 записей из 10M строк разрежение 1:1000 - 50ms на выбор 10 записей из 10M строк дальше алгоритм уже неприменим скорее. Алгоритм с хабра никогда вообще число 2 не этом наборе не выдаст (точнее выдаст с вероятностью 1/2000000000). Что показывает его некорректность. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 15.11.2014, 09:52:05 |
|
||
|
очередной велосипедист
|
|||
|---|---|---|---|
|
#18+
Maxim BogukMaxim Boguk, <> Код: plsql 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. красиво тоько я всё время сам забываю про краевые эффекты, когда краям дается вес по 0.5, а в середке -- по 1. кажется с поправкой оно вернее (могу врать). ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 15.11.2014, 15:13:15 |
|
||
|
|

start [/forum/topic.php?fid=53&tid=1998334]: |
0ms |
get settings: |
6ms |
get forum list: |
15ms |
check forum access: |
3ms |
check topic access: |
3ms |
track hit: |
446ms |
get topic data: |
7ms |
get forum data: |
2ms |
get page messages: |
41ms |
get tp. blocked users: |
1ms |
| others: | 231ms |
| total: | 755ms |

| 0 / 0 |
