Обсуждение: связать таблицы по наибольшему совпадению строки
Здравствуйте, уважаемые.
Подскажите, как производительнее организовать такую связку.
1) есть таблица кодов для междугородки, (коды, города, тарифы), скажем
==================================================
billing=# \d a_voip_codes
Table "public.a_voip_codes"
Column | Type | Modifiers
--------+-----------------------+-----------
code | character varying(11) | not null
region | character varying(77) |
tarif | numeric(13,7) |
Indexes:
"a_voip_codes_pkey" PRIMARY KEY, btree (code)
==================================================
2) Есть таблица звонков, основное (юзер, набранный номер, время), скажем
==================================================
billing=# \d a_voip
Table "public.a_voip"
Column | Type |
Modifiers
--------------------+-----------------------------+-----------------------------------------------------
id | integer | not null default
nextval('a_voip_id_seq'::regclass)
tm | timestamp without time zone | not null
user_name | character varying(50) | not null
...
calling_station_id | character varying(20) | not null
...
Indexes:
"a_voip_pkey" PRIMARY KEY, btree (id)
"a_voip_called_station_id" btree (called_station_id)
"a_voip_tm" btree (tm)
"a_voip_user_name" btree (user_name)
==================================================
В набранном номере первые сколько-то цифр - это год междугородки.
Нужно сделать выборку из 2) таблицы, соединяя с 1) - по самому
длинному коду, совпадающему с начальными цифрами набранного номера. Ну
то есть если есть номера
73512808080
73517909090
73515303030
и коды
7351
73512
73517
то должно выбираться соответственно
calling_station_id - code
73512808080 - 73512
73517909090 - 73517
73515303030 - 7351
Я сначала-то сделал примерно так (плохо)
billing=# explain analyze SELECT *,
(SELECT code FROM a_voip_codes AS c where v.called_station_id like
c.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';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on a_voip v (cost=553.44..3006.04 rows=259
width=166) (actual time=39.386..897.065 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.44..553.44 rows=259 width=0) (actual
time=36.794..36.794 rows=0 loops=1)
-> Bitmap Index Scan on a_voip_user_name (cost=0.00..24.74
rows=1926 width=0) (actual time=2.062..2.062 rows=1894 loops=1)
Index Cond: ((user_name)::text = 'dixi'::text)
-> Bitmap Index Scan on a_voip_tm (cost=0.00..528.45
rows=41075 width=0) (actual time=33.655..33.655 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..6.72 rows=1 width=8) (actual
time=2.298..2.300 rows=1 loops=371)
-> Index Scan Backward using a_voip_codes_pkey on
a_voip_codes c (cost=0.00..67.20 rows=10 width=8) (actual
time=2.293..2.293 rows=1 loops=371)
Filter: (($0)::text ~~ ((code)::text || '%'::text))
Total runtime: 898.290 ms
(12 rows)
Это работает ещё как-то, но если брать статистику по всем юзерам
(убрать user_name из WHERE), то это уже ужасно долго:
billing=# explain analyze SELECT *, (SELECT code FROM a_voip_codes AS
c where v.called_station_id like c.code || '%' order by code desc
limit 1) AS code FROM a_voip AS v WHERE tm between '2006-04-10' and
'2006-04-20';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on a_voip v (cost=528.45..294000.19 rows=41075
width=166) (actual time=37.256..101377.640 rows=41280 loops=1)
Recheck Cond: ((tm >= '2006-04-10 00:00:00'::timestamp without time
zone) AND (tm <= '2006-04-20 00:00:00'::timestamp without time zone))
-> Bitmap Index Scan on a_voip_tm (cost=0.00..528.45 rows=41075
width=0) (actual time=33.784..33.784 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..6.72 rows=1 width=8) (actual
time=2.436..2.438 rows=1 loops=41280)
-> Index Scan Backward using a_voip_codes_pkey on
a_voip_codes c (cost=0.00..67.20 rows=10 width=8) (actual
time=2.431..2.431 rows=1 loops=41280)
Filter: (($0)::text ~~ ((code)::text || '%'::text))
Total runtime: 101414.521 ms
(9 rows)
Крутил по всякому, но не пойму как избавиться от вложенного запроса
или от этого обратного LIKE, видимо он портит дело.
Подскажите, как работать с подобными вещами, может структуру базы по
другому делать?
--
engineer
On Tue, 2006-05-30 at 19:02 +0600, Anton Maksimenkov wrote:
> 1) есть таблица кодов для междугородки, (коды, города, тарифы), скажем
> ==================================================
> billing=# \d a_voip_codes
> Table "public.a_voip_codes"
> Column | Type | Modifiers
> --------+-----------------------+-----------
> code | character varying(11) | not null
> region | character varying(77) |
> tarif | numeric(13,7) |
> Indexes:
> "a_voip_codes_pkey" PRIMARY KEY, btree (code)
Вот что у меня:
Table "voip.voip_tariffs"
Column | Type | Modifiers
--------+---------+-----------
trid | integer | not null
dir | text | not null
price | numeric |
descr | text |
Indexes:
"voip_tariffs_pkey" PRIMARY KEY, btree (trid, dir)
trid - группа тарифов, при поиске она уже заранее известна.
Вот запрос, который выполняется при поиске тарифа для каждой записи:
SELECT dir, price
FROM voip_tariffs
WHERE trid = <группа тарифов>
AND dir <= <вызываемый номер>
AND <вызываемый номер> ~ ('^' || dir)
ORDER BY trid DESC, dir DESC
LIMIT 1;
Возможно, не самый оптимальный вариант, однако меня вполне устраивает:
EXPLAIN ANALYZE
SELECT dir, price
FROM voip_tariffs
WHERE trid = 4
AND dir <= '4951234567'
AND '4951234567' ~ ('^' || dir)
ORDER BY trid DESC, dir DESC
LIMIT 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..14.86 rows=1 width=21) (actual time=29.281..29.283 rows=1 loops=1)
-> Index Scan Backward using voip_tariffs_pkey on voip_tariffs (cost=0.00..14.86 rows=1 width=21) (actual
time=29.275..29.275rows=1 loops=1)
Index Cond: ((trid = 4) AND (dir <= '4951234567'::text))
Filter: ('4951234567'::text ~ ('^'::text || dir))
Total runtime: 29.383 ms
К тому же, при этом не требуется построение индекса с
varchar_pattern_ops.
Выбирается именно наибольшее совпадение. Это хорошо видно, если убрать
LIMIT 1 (ну и правильно подобрать вызываемый номер ;).
> 2) Есть таблица звонков, основное (юзер, набранный номер, время), скажем
> ==================================================
> billing=# \d a_voip
> Table "public.a_voip"
> Column | Type |
> Modifiers
> --------------------+-----------------------------+-----------------------------------------------------
> id | integer | not null default
> nextval('a_voip_id_seq'::regclass)
> tm | timestamp without time zone | not null
> user_name | character varying(50) | not null
> ...
> calling_station_id | character varying(20) | not null
> ...
> Indexes:
> "a_voip_pkey" PRIMARY KEY, btree (id)
> "a_voip_called_station_id" btree (called_station_id)
> "a_voip_tm" btree (tm)
> "a_voip_user_name" btree (user_name)
> ==================================================
>
> В набранном номере первые сколько-то цифр - это год междугородки.
> Нужно сделать выборку из 2) таблицы, соединяя с 1) - по самому
> длинному коду, совпадающему с начальными цифрами набранного номера.
Я вообще рекомендовал бы такие вычисления делать не при выборке
статистики, а при складывании в базу, скажем, из BEFORE INSERT триггера.
И, кстати, нынешняя реализация регулярных выражений гораздо быстрее
LIKE, так что тоже советую попробовать поиграться.
--
Fduch M. Pravking
Re: [pgsql-ru-general] связать таблицы по наибольшему совпадению строки
От
"Anton Maksimenkov"
Дата:
> 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