pjatachokчисло строк в новой таблице будет степенной функцией от числа строк в оригинальнойне будет ни степенной функцией, ни показательной (как поправил Vladimir Sitnikov). кол-во строк в таблице t2 будет в пределах от N*((k+1)/k)^(length(f1)) до N*2^(length(f1)), где k - основание системы счисления в f1. то есть линейная функция от N.

Vladimir Sitnikovt2 +index on (subtext, position):
string_id position subtextпроблема не в медленном поиске по t1, а в медленном аггрегировании полученных строк. продолжая вашу идею, может быть для этого использовать таблицу t2(count,position,subtext)?

Vladimir SitnikovСоответсвенно, запрос t1.text like '_3' заменяется на t2.subtext='3' and t2.position=1 и т.п.на что в вашем варианте заменяется запрос t1.text like '1___2___'? будет ли он быстрым?

Vladimir SitnikovРазве вообще возможна проблема в "медленном агрегировании"?конечно. в оригинале имеем джоин и аггрегирование более 10 тысяч строк. с помощью моего совета аггрегирование делается в триггерах в дополнительной таблице на этапе изменения данных. а выборка получается быстрой.

Vladimir SitnikovНасколько я смог понять, по условию Filter: ((cgssn = 6) AND (cdssn = 7) AND ((cgpa)::text ~~ '_:99______'::text) AND ((cdpa)::text ~~ '1:79______'::text)) из таблицы sccp_addr выбралось 70 строк из 500К.
Слабо верится, что остальные 40 секунд ушли на загрузку/агрегацию полученных 11К строк.seqscan работал секунду (actual time=1030.528), в то время как внутренняя часть nested-loop - 41 секунду (actual time=592.376 loops=70).

Vladimir SitnikovИзвестно ли вам, что линейная функция является степенной?точно! с показателем единица.

Vladimir Sitnikov LeXa NalBatна что в вашем варианте заменяется запрос t1.text like '1___2___'? будет ли он быстрым?Вообще говоря, нужно лишь выбрать наиболее селективную подпоследовательность без подчерков и искать по нейили можно сделать джоин выборок по индексу (position,substring) с условиями like '1%' и like '2%'. сомневаюсь, что это будет быстро.

и сработает это только для таблицы t2(string_id,..), а для таблицы t2(count,..) не получится найти искомый count(*).

Vladimir Sitnikovиз-за того, что в postgres нет index-only доступа к данныма это здесь каким образом влияет? объясните пожалуйста подробнее.

Vladimir Sitnikovt1.text like '1___2___' можно либо оставить "как есть" (создав индекс по колонке text), либо заменить на t2.subtext like '1%' and position=0, либо на t2.subtext like '2%' and position=4. В случае, когда цифры располагаются поодиночке, возможно, такой индекс не даст выигрыша.да, думаю выигрыша не даст. в отличие от предложенного мной способа. это ответ на ваш вопрос "зачем нужны все эти подчерки в таблице t2".

Vladimir SitnikovИ что? Разве тормозила агрегация как таковая?нет, тормозил джоин, необходимый для последующей аггрегации, внутренняя часть nested loop.

Vladimir SitnikovПо плану выполнения, агрегация выполняется на самом последнем шаге (если её можно перенести на более ранние этапы, это, конечно же стоит делать).боюсь, что не получится :-(

Vladimir SitnikovНо, вы, ведь, предлагаете не переносить агрегацию на ранние этапы, а на триггерах предвычислять результаты полного запроса?да, совершенно верно

Vladimir SitnikovА что входит во внутренюю часть nested loop?

Честно говоря, в оригинальном запросе я count(*) не увидел.в этом посте запрос и план. во внутренней части - bitmap heap, index scans. в оригинальном запросе - SUM(i.count).

Vladimir SitnikovОчень слабо верится в то, что на поиск 11К строк в nested-loop'е ушло 40 секунд.мне тоже это кажется медленным. хотя если эти строки равномерно разбросаны по страницам, то в худшем случае получится 11K страниц. :-(

Vladimir SitnikovЗачем сомневаться? Для выборки по индексу like '1%' придётся блоки индекса, удволетворяющие условию (ind1 блоков) и блоки таблицы (tbl1). Для выборки по индексу like '2%' придётся прочитать какие-то другие блоки индекса (ind2) и блоки таблицы (как новые, так и старые, tbl2 штук).
В результате, если мы сделаем 2 обращения к индексам, нам потребуется прочитать ind1+tbl1+ind2+tbl2 блоков.
Другое дело, тот же ответ мы можем получить, просмотрев лишь один раз 1-ый индекc -- ведь достоверно известно, что он не пропустит ни одной строки. В то же время, like '2%' непременно вернёт как нужные строки (которые мы уже видели при первом обращении), так и какие-то лишние. Какой смысл выбирать эти лишние строки и тратить на это дополнительное время?

Возможно, станет понятнее, если сразу записать условие поиска в виде t2.subtext like '1___2%'.считаем

пусть условию like '1%' удовлетворяет N1 строк (в t1 и в t2), like '2%' - N2

план IndexScan(t1,like '1%'),Filter(like '2%') требует просмотра N1 строк ширины w1 в таблице t1. цена ~ N1*w1

план IndexScan(t2,like '1%'),IndexScan(t2,like '2%') имеет цену ~ (N1+N2)*w2, где w2 - ширина строки в t2

и что же? какой план быстрее?

Vladimir SitnikovВообще говоря, нужно лишь выбрать наиболее селективную подпоследовательность без подчерков и искать по ней (из-за того, что в postgres нет index-only доступа к данным).все-таки непонятно. какое отношение отсутствие в постгресе index only scan имеет к данной ситуации? а именно, если бы в постгресе был index only scan, то надо было бы выбрать другой план вместо "выбрать наиболее селективную подпоследовательность без подчерков и искать по ней"?

Vladimir SitnikovНе подскажете, как count упростит оригинальный запрос?вы просите разжевать, какие именно колонки надо сгруппировать при создании t2, и как переписывается оригинальный запрос?