Re: [pgsql-ru-general] связать таблицы по наибольшему совпадению строки

Поиск
Список
Период
Сортировка
От Anton Maksimenkov
Тема Re: [pgsql-ru-general] связать таблицы по наибольшему совпадению строки
Дата
Msg-id 8cac8dd0605300927oda3df45w6d59b60a1761508b@mail.gmail.com
обсуждение исходный текст
Ответ на Re: связат  ("Alexander M. Pravking" <fduch@antar.bryansk.ru>)
Список 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 по дате отправления:

Предыдущее
От: "Alexander M. Pravking"
Дата:
Сообщение: Re: связат
Следующее
От: "Oleg Golovanov"
Дата:
Сообщение: DocBook 4.2 detecting at configure time