From: Anton Maksimenkov <anton200@gmail.com>
To: Alexander M. Pravking <fduch@antar.bryansk.ru>
Cc: pgsql-ru-general@postgresql.org
Subject: Re: [pgsql-ru-general] связать таблицы по наибольшему совпадению строки
Date: Tue, 30 May 2006 22:27:49 +0600
Message-ID: <8cac8dd0605300927oda3df45w6d59b60a1761508b@mail.gmail.com> (raw)
In-Reply-To: <20060530133427.GE10013@dyatel.antar.bryansk.ru>
References: <8cac8dd0605300602v18f0b4abn7a7e94189aa4c4bb@mail.gmail.com>
<20060530133427.GE10013@dyatel.antar.bryansk.ru>
> SELECT dir, price
> FROM voip_tariffs
> WHERE trid = <группа тарифов>
> AND dir <= <вызываемый номер>
> AND <вызываемый номер> ~ ('^' || dir)
> ORDER BY trid DESC, dir DESC
> LIMIT 1;
> Возможно, не самый оптимальный вариант, однако меня вполне устраивает:
ОГРОМНОЕ спасибо, этот вариант гораздо быстрее!!!
billing=# explain analyze SELECT *, (SELECT code FROM a_voip_codes
WHERE code <= v.called_station_id AND v.called_station_id ~('^' ||
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';
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on a_voip v (cost=553.06..3410.96 rows=257
width=166) (actual time=35.572..54.364 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.06..553.06 rows=257 width=0) (actual
time=34.989..34.989 rows=0 loops=1)
-> Bitmap Index Scan on a_voip_user_name (cost=0.00..23.67
rows=1905 width=0) (actual time=2.278..2.278 rows=1894 loops=1)
Index Cond: ((user_name)::text = 'dixi'::text)
-> Bitmap Index Scan on a_voip_tm (cost=0.00..529.15
rows=41191 width=0) (actual time=31.572..31.572 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..9.30 rows=1 width=8) (actual
time=0.034..0.035 rows=1 loops=371)
-> Index Scan Backward using a_voip_codes_pkey on
a_voip_codes (cost=0.00..27.91 rows=3 width=8) (actual
time=0.028..0.028 rows=1 loops=371)
Index Cond: ((code)::text <= ($0)::text)
Filter: (($0)::text ~ ('^'::text || (code)::text))
Total runtime: 55.841 ms
(13 rows)
То есть почти в 16 раз быстрее, чем LIKE.
> К тому же, при этом не требуется построение индекса с
> varchar_pattern_ops.
А это что такое, поясни?
> Я вообще рекомендовал бы такие вычисления делать не при выборке
> статистики, а при складывании в базу, скажем, из BEFORE INSERT триггера.
Да, я тоже сообразил, что можно поле а-ля matched_code завести, в
которое триггером вставлять значения. Однако имеется ситуация когда
таблица кодов изменяется
Поэтому сообразил триггер для INSERT/UPDATE/DELETE таблицы кодов.
Понятно зачем - может появиться более длинный код ( то есть скажем так
более детально описывающий "куда звоним"), таким образом ранее
совпавшие более короткие коды, которые совпадают с начальными цифрами
нового длинного кода, нужно "перепроверить" на предмет совпадения с
новым более длинным кодом.
А вообще, теперь даже обновление ВСЕЙ таблицы звонков (она пока
небольшая, порядка 300 000 строк) происходит довольно быстро :
billing=# explain analyze UPDATE a_voip SET matched_code = (SELECT
code FROM a_voip_codes WHERE code <= a_voip.called_station_id AND
a_voip.called_station_id ~('^' || code) order by code desc limit 1);
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on a_voip (cost=0.00..2953677.82 rows=316130 width=168)
(actual time=0.335..58485.877 rows=305364 loops=1)
SubPlan
-> Limit (cost=0.00..9.30 rows=1 width=8) (actual
time=0.168..0.170 rows=1 loops=305364)
-> Index Scan Backward using a_voip_codes_pkey on
a_voip_codes (cost=0.00..27.91 rows=3 width=8) (actual
time=0.161..0.161 rows=1 loops=305364)
Index Cond: ((code)::text <= ($0)::text)
Filter: (($0)::text ~ ('^'::text || (code)::text))
Total runtime: 170037.350 ms
(7 rows)
Выполнения варианта с LIKE я не долждался, отменил, итак понятно, что
регексп здесь рулит.
> И, кстати, нынешняя реализация регулярных выражений гораздо быстрее
> LIKE, так что тоже советую попробовать поиграться.
--
engineer
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-ru-general@postgresql.org
Cc: anton200@gmail.com, fduch@antar.bryansk.ru
Subject: Re: =?KOI8-R?B?UmU6IFtwZ3NxbC1ydS1nZW5lcmFsXSDT19HawdTYINTBwsw=?= =?KOI8-R?B?ycPZINDPIM7BycLPzNjbxc3VINPP19DBxMXOycAg09TSz8vJ?=
In-Reply-To: <8cac8dd0605300927oda3df45w6d59b60a1761508b@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox