DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=I3HhcEtwx+WF/87r8dHZcAkDF32NSExlF0FyKNWc8PXcF2C2ZNNy6bt1xsg6Szgu7dLEXJI4LESiece4Lbz6pjrmHcx28N3ITI4W+s1+B6vLK8XuTPspVz+/bwp9G1eXF3Flo+tvLcBGV2+XJmulL+n59vgRBWaWDZ+bdRGECX8=
Message-ID: <8cac8dd0605290253v75d43b9p249ebf58ee28b63c@mail.gmail.com>
Date: Mon, 29 May 2006 15:53:24 +0600
From: "Anton Maksimenkov" <anton200@gmail.com>
To: pgsql-performance@postgresql.org
Subject: select with "like" from another table
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi.

I have 2 tables - one with calls numbers and another with calls codes.
The structure almost like this:
billing=3D# \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
 called_station_id  | character varying(20)       | not null
Indexes:
   "a_voip_pkey" PRIMARY KEY, btree (id)
   "a_voip_tm" btree (tm)

billing=3D# \d a_voip_codes
       Table "public.a_voip_codes"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 code   | integer               | not null
 region | character varying(77) |
 tarif  | numeric(13,7)         |
Indexes:
   "a_voip_codes_pkey" PRIMARY KEY, btree (code)

I need to select longest codes from a_voip_codes which match with the
the called_station_id. Because codes (very rarely) changes I construct
query

SELECT  user_name, called_station_id,
(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 =3D 'dixi' AND tm between '2006-04-01'
and '2006-05-01' group by user_name, called_station_id;

Analyzed variant
billing=3D# explain analyze SELECT user_name, called_station_id, (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 =3D 'dixi' AND tm between '2006-04-01' and '2006-05-01' group
by user_name, called_station_id;

 QUERY PLAN
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-------
 HashAggregate  (cost=3D11515.93..12106.26 rows=3D69 width=3D22) (actual
time=3D215.719..677.044 rows=3D130 loops=3D1)
  ->  Bitmap Heap Scan on a_voip v  (cost=3D1106.66..11513.16 rows=3D554
width=3D22) (actual time=3D72.336..207.618 rows=3D848 loops=3D1)
        Recheck Cond: ((tm >=3D '2006-04-01 00:00:00'::timestamp
without time zone) AND (tm <=3D '2006-05-01 00:00:00'::timestamp without
time zone))
        Filter: ((user_name)::text =3D 'dixi'::text)
        ->  Bitmap Index Scan on a_voip_tm  (cost=3D0.00..1106.66
rows=3D90943 width=3D0) (actual time=3D69.441..69.441 rows=3D93594 loops=3D=
1)
              Index Cond: ((tm >=3D '2006-04-01 00:00:00'::timestamp
without time zone) AND (tm <=3D '2006-05-01 00:00:00'::timestamp without
time zone))
  SubPlan
    ->  Limit  (cost=3D0.00..8.55 rows=3D1 width=3D4) (actual
time=3D3.565..3.567 rows=3D1 loops=3D130)
          ->  Index Scan Backward using a_voip_codes_pkey on
a_voip_codes c  (cost=3D0.00..85.45 rows=3D10 width=3D4) (actual
time=3D3.560..3.560 rows=3D1 loops=3D130)
                Filter: (($0)::text ~~ ((code)::text || '%'::text))
 Total runtime: 678.186 ms
(11 rows)

It is ugly, however not so long (but only for 69 rows). If I want to
select for ALL users it goes veeeery long:
billing=3D# explain analyze SELECT user_name, called_station_id, (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-01' and '2006-05-01' group by user_name,
called_station_id;

  QUERY PLAN
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------
 HashAggregate  (cost=3D11740.52..107543.85 rows=3D11198 width=3D22) (actua=
l
time=3D779.488..75637.623 rows=3D20564 loops=3D1)
  ->  Bitmap Heap Scan on a_voip v  (cost=3D1106.66..11285.81
rows=3D90943 width=3D22) (actual time=3D72.539..274.850 rows=3D90204 loops=
=3D1)
        Recheck Cond: ((tm >=3D '2006-04-01 00:00:00'::timestamp
without time zone) AND (tm <=3D '2006-05-01 00:00:00'::timestamp without
time zone))
        ->  Bitmap Index Scan on a_voip_tm  (cost=3D0.00..1106.66
rows=3D90943 width=3D0) (actual time=3D69.853..69.853 rows=3D93594 loops=3D=
1)
              Index Cond: ((tm >=3D '2006-04-01 00:00:00'::timestamp
without time zone) AND (tm <=3D '2006-05-01 00:00:00'::timestamp without
time zone))
  SubPlan
    ->  Limit  (cost=3D0.00..8.55 rows=3D1 width=3D4) (actual
time=3D3.631..3.633 rows=3D1 loops=3D20564)
          ->  Index Scan Backward using a_voip_codes_pkey on
a_voip_codes c  (cost=3D0.00..85.45 rows=3D10 width=3D4) (actual
time=3D3.623..3.623 rows=3D1 loops=3D20564)
                Filter: (($0)::text ~~ ((code)::text || '%'::text))
 Total runtime: 75652.199 ms
(10 rows)

So I want to ask, how can I reorganize query/structure for achieve
good performance?

 I experiment with additional column (matched_code) for a_voip table
and think about RULE which will update that column "matched_code"
doing the (SELECT code FROM a_voip_codes AS c where
v.called_station_id like c.code || '%' order by code desc limit 1) job
when a_voip_codes updated. Or about TRIGGER. But this may also takes
long time, especially with short "code" numbers (like 1 digit). Look:

billing=3D# explain analyze UPDATE a_voip SET matched_code =3D (SELECT
code FROM a_voip_codes AS c WHERE a_voip.called_station_id like c.code
|| '%' order by code desc limit 1) WHERE matched_code LIKE '1%';

   QUERY PLAN
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
----------
 Bitmap Heap Scan on a_voip  (cost=3D20.34..20467.27 rows=3D2057
width=3D168) (actual time=3D13.407..22201.369 rows=3D2028 loops=3D1)
  Filter: ((matched_code)::text ~~ '1%'::text)
  ->  Bitmap Index Scan on a_voip_matched_code  (cost=3D0.00..20.34
rows=3D2057 width=3D0) (actual time=3D2.035..2.035 rows=3D2028 loops=3D1)
        Index Cond: (((matched_code)::text >=3D '1'::character varying)
AND ((matched_code)::text < '2'::character varying))
  SubPlan
    ->  Limit  (cost=3D0.00..8.55 rows=3D1 width=3D4) (actual
time=3D10.909..10.911 rows=3D1 loops=3D2028)
          ->  Index Scan Backward using a_voip_codes_pkey on
a_voip_codes c  (cost=3D0.00..85.45 rows=3D10 width=3D4) (actual
time=3D10.923..10.923 rows=3D1 loops=3D2028)
                Filter: (($0)::text ~~ ((code)::text || '%'::text))
 Total runtime: 23216.770 ms
(9 rows)

Is there any other ways to connect longest "code" with "called_station_id"?
--=20
engineer