Re: связат

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.275 rows=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