Skip site navigation (1) Skip section navigation (2)

связать таблицы по наибольшему совпадению строки

From: "Anton Maksimenkov" <anton200(at)gmail(dot)com>
To: pgsql-ru-general(at)postgresql(dot)org
Subject: связать таблицы по наибольшему совпадению строки
Date: 2006-05-30 13:02:59
Message-ID: 8cac8dd0605300602v18f0b4abn7a7e94189aa4c4bb@mail.gmail.com (view raw or flat)
Thread:
Lists: 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

Responses

pgsql-ru-general by date

Next:From: Alexander M. PravkingDate: 2006-05-30 13:34:27
Subject: Re: связат
Previous:From: Андрей ЗевакинDate: 2006-04-19 09:48:49
Subject: Re: как преобр

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group