Re: [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