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