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.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