Обсуждение: связать таблицы по наибольшему совпадению строки
Здравствуйте, уважаемые. Подскажите, как производительнее организовать такую связку. 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