Re: [pgsql-ru-general] связать таблицы по наибольшему совпадению строки - Mailing list pgsql-ru-general
From | Anton Maksimenkov |
---|---|
Subject | Re: [pgsql-ru-general] связать таблицы по наибольшему совпадению строки |
Date | |
Msg-id | 8cac8dd0605300927oda3df45w6d59b60a1761508b@mail.gmail.com Whole thread Raw |
In response to | Re: связат ("Alexander M. Pravking" <fduch@antar.bryansk.ru>) |
List | pgsql-ru-general |
> SELECT dir, price > FROM voip_tariffs > WHERE trid = <группа тарифов> > AND dir <= <вызываемый номер> > AND <вызываемый номер> ~ ('^' || dir) > ORDER BY trid DESC, dir DESC > LIMIT 1; > Возможно, не самый оптимальный вариант, однако меня вполне устраивает: ОГРОМНОЕ спасибо, этот вариант гораздо быстрее!!! billing=# explain analyze SELECT *, (SELECT code FROM a_voip_codes WHERE code <= v.called_station_id AND v.called_station_id ~('^' || code) order by code desc limit 1) AS code FROM a_voip AS v WHERE user_name = 'dixi' and tm between '2006-04-10' and '2006-04-20'; ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Bitmap Heap Scan on a_voip v (cost=553.06..3410.96 rows=257 width=166) (actual time=35.572..54.364 rows=371 loops=1) Recheck Cond: (((user_name)::text = 'dixi'::text) AND (tm >= '2006-04-10 00:00:00'::timestamp without time zone) AND (tm <= '2006-04-20 00:00:00'::timestamp without time zone)) -> BitmapAnd (cost=553.06..553.06 rows=257 width=0) (actual time=34.989..34.989 rows=0 loops=1) -> Bitmap Index Scan on a_voip_user_name (cost=0.00..23.67 rows=1905 width=0) (actual time=2.278..2.278 rows=1894 loops=1) Index Cond: ((user_name)::text = 'dixi'::text) -> Bitmap Index Scan on a_voip_tm (cost=0.00..529.15 rows=41191 width=0) (actual time=31.572..31.572 rows=41280 loops=1) Index Cond: ((tm >= '2006-04-10 00:00:00'::timestamp without time zone) AND (tm <= '2006-04-20 00:00:00'::timestamp without time zone)) SubPlan -> Limit (cost=0.00..9.30 rows=1 width=8) (actual time=0.034..0.035 rows=1 loops=371) -> Index Scan Backward using a_voip_codes_pkey on a_voip_codes (cost=0.00..27.91 rows=3 width=8) (actual time=0.028..0.028 rows=1 loops=371) Index Cond: ((code)::text <= ($0)::text) Filter: (($0)::text ~ ('^'::text || (code)::text)) Total runtime: 55.841 ms (13 rows) То есть почти в 16 раз быстрее, чем LIKE. > К тому же, при этом не требуется построение индекса с > varchar_pattern_ops. А это что такое, поясни? > Я вообще рекомендовал бы такие вычисления делать не при выборке > статистики, а при складывании в базу, скажем, из BEFORE INSERT триггера. Да, я тоже сообразил, что можно поле а-ля matched_code завести, в которое триггером вставлять значения. Однако имеется ситуация когда таблица кодов изменяется Поэтому сообразил триггер для INSERT/UPDATE/DELETE таблицы кодов. Понятно зачем - может появиться более длинный код ( то есть скажем так более детально описывающий "куда звоним"), таким образом ранее совпавшие более короткие коды, которые совпадают с начальными цифрами нового длинного кода, нужно "перепроверить" на предмет совпадения с новым более длинным кодом. А вообще, теперь даже обновление ВСЕЙ таблицы звонков (она пока небольшая, порядка 300 000 строк) происходит довольно быстро : billing=# explain analyze UPDATE a_voip SET matched_code = (SELECT code FROM a_voip_codes WHERE code <= a_voip.called_station_id AND a_voip.called_station_id ~('^' || code) order by code desc limit 1); ------------------------------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on a_voip (cost=0.00..2953677.82 rows=316130 width=168) (actual time=0.335..58485.877 rows=305364 loops=1) SubPlan -> Limit (cost=0.00..9.30 rows=1 width=8) (actual time=0.168..0.170 rows=1 loops=305364) -> Index Scan Backward using a_voip_codes_pkey on a_voip_codes (cost=0.00..27.91 rows=3 width=8) (actual time=0.161..0.161 rows=1 loops=305364) Index Cond: ((code)::text <= ($0)::text) Filter: (($0)::text ~ ('^'::text || (code)::text)) Total runtime: 170037.350 ms (7 rows) Выполнения варианта с LIKE я не долждался, отменил, итак понятно, что регексп здесь рулит. > И, кстати, нынешняя реализация регулярных выражений гораздо быстрее > LIKE, так что тоже советую попробовать поиграться. -- engineer
pgsql-ru-general by date: