ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Индекс для B в A LIKE B || '%'
3 сообщений из 3, страница 1 из 1
Индекс для B в A LIKE B || '%'
    #33021471
Funny_Falcon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Funny_Falcon
Участник
Люди, кто-нибудь знает, есть ли реализованные индексы для строк для
оператора "начинается с" (обозначим @> ), такого, что A @> B эквивалентно A LIKE B||'%' или A ~ '^' || B. Смысл - проиндексировать B в таблице направлений в биллинге.
Пока делаю индекс ( (B||chr(255)) , B) и проверяю A< B||chr(255) and A>= B.
Реализовать можно,т.к. есть более сложный для ltree.
Самому силенок не хватает пока понять работу GIST. Если есть учебник, что б более менне по полочкам, то сам сделаю и выложу.
Кстати, а для отрезка времени (timestamp,timestamp) кто-нибудь rtree через gist сделал?
...
Рейтинг: 0 / 0
18.04.2005, 15:47
| Ответить | Цитировать | Написать  
Индекс для B в A LIKE B || '%'
    #33023496
Funny_Falcon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Funny_Falcon
Участник
Написал. Вернее внес изменения в contrib btree_gist. Основные изменения в btree_text.c, btree_utils_var.c. btree_utils_var.h ввел две дополнительные ссылки и в остальных файлах на их место поставил NULL. в btree_gist.sql.in
ввел определения функций, операторов и добавил в операторный класс пару строк.
Поправлюсь: A LIKE B||'%' эквивалентно A <@ B, и A @> эквивалентно A=substr(B , 1 , length (A)), т.к. это соответствует семантике ltree.

Испытания:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
CREATE TABLE voicetarhnear
(
  code int4 NOT NULL,
  date timestamp( 0 ) NOT NULL,
  portfrom varchar( 20 ) NOT NULL,
  portto char( 6 ) NOT NULL,
  callingid varchar( 20 ) NOT NULL,
  calledid char( 16 ) NOT NULL,
  aon char( 16 ) NOT NULL,
  startcall timestamp( 0 ) NOT NULL,
  stopcall timestamp( 0 ) NOT NULL,
  timecall timestamp( 0 ) NOT NULL,
  calc int4 NOT NULL,
  us float4 NOT NULL,
  ru float4 NOT NULL,
  direction varchar( 30 ) NOT NULL,
  CONSTRAINT "PK__VoiceTarhNear" PRIMARY KEY (date, startcall, portfrom)
) 
WITHOUT OIDS;
Содержит ~85000 строк.

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
CREATE TABLE portphone
(
  code int4 NOT NULL,
  port varchar( 12 ) NOT NULL,
  phone varchar( 20 ) NOT NULL,
  idcg int4,
  date_from timestamp NOT NULL DEFAULT '1900-01-01 00:00:00'::timestamp without time zone,
  date_to timestamp NOT NULL DEFAULT '3000-01-01 00:00:00'::timestamp without time zone,
  idportphone serial NOT NULL,
  type_phone int2 NOT NULL DEFAULT  1 ,
  CONSTRAINT portphone_pkey PRIMARY KEY (idportphone)
) 
WITHOUT OIDS;
2247 строк.
В большинстве port - пустая строка. Есть несколько с phone - пустым, и port - нет. есть пару строк где оба не пусты.

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
CREATE INDEX ix_portphone_phonez_portz_2
  ON portphone
  USING btree
  ((phone::text || 'z'::text), phone, (port::text || 'z'::text), port, date_from, date_to);

CREATE INDEX ix_portphone_phone_port_g
  ON portphone
  USING gist
  ((phone::text), (port::text), date_from, date_to);

В Btree_gist не определен класс операторов для varchar, поэтому text.

Используя BTree:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
select distinct on (date,startcall,portfrom) 
	vt.date,vt.startcall,vt.portfrom,vt.callingid,pp.code,pp.port,pp.phone
from 
(select * from 
voicetarhnear
where date between '2004.11.11'::timestamp and '2004.11.15'::timestamp 
)
 vt inner join 
portphone
 pp 
on  
(pp.phone||'z') >= vt.callingid   and
pp.phone <= vt.callingid and 
(pp.port||'z') >= vt.portfrom   and
pp.port <= vt.portfrom and
pp.date_from <= vt.date and
pp.date_to >= vt.date
order by date,startcall,portfrom,pp.port desc,pp.phone desc

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
Unique  (cost= 7333 . 59 .. 7344 . 48  rows= 36  width= 108 ) (actual time= 14360 . 465 .. 14834 . 482  rows= 33261  loops= 1 )
  ->  Sort  (cost= 7333 . 59 .. 7336 . 31  rows= 1089  width= 108 ) (actual time= 14347 . 305 .. 14507 . 690  rows= 33353  loops= 1 )
        Sort Key: voicetarhnear.date, voicetarhnear.startcall, voicetarhnear.portfrom, pp.port, pp.phone
        ->  Nested Loop  (cost= 0 . 01 .. 7278 . 66  rows= 1089  width= 108 ) (actual time= 0 . 538 .. 13681 . 588  rows= 33353  loops= 1 )
              ->  Index Scan using "PK__VoiceTarhNear" on voicetarhnear  (cost= 0 . 00 .. 1300 . 84  rows= 353  width= 64 ) (actual time= 0 . 164 .. 582 . 992  rows= 34367  loops= 1 )
                    Index Cond: ((date >= '2004-11-11 00:00:00'::timestamp without time zone) AND (date <= '2004-11-15 00:00:00'::timestamp without time zone))
              ->  Index Scan using ix_portphone_phonez_portz_2 on portphone pp  (cost= 0 . 01 .. 16 . 81  rows= 4  width= 60 ) (actual time= 0 . 147 .. 0 . 334  rows= 1  loops= 34367 )
                    Index Cond: ((((pp.phone)::text || 'z'::text) >= ("outer".callingid)::text) AND ((pp.phone)::text <= ("outer".callingid)::text) AND (((pp.port)::text || 'z'::text) >= ("outer".portfrom)::text) AND ((pp.port)::text <= ("outer".portfrom)::text) AND (pp.date_from <= "outer".date) AND (pp.date_to >= "outer".date))
Total runtime:  14991 . 873  ms

Используя Gist:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
select distinct on (date,startcall,portfrom) 
	vt.date,vt.startcall,vt.portfrom,vt.callingid,pp.code,pp.port,pp.phone
from 
(select * from 
voicetarhnear
where date between '2004.11.11'::timestamp and '2004.11.15'::timestamp 
)
 vt inner join 
portphone
 pp 
on  
pp.phone @> vt.callingid and 
pp.port @> vt.portfrom and
pp.date_from <= vt.date and
pp.date_to >= vt.date
order by date,startcall,portfrom,pp.port desc,pp.phone desc

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
Unique  (cost= 3403 . 29 .. 3403 . 30  rows= 1  width= 108 ) (actual time= 8868 . 508 .. 9316 . 857  rows= 33261  loops= 1 )
  ->  Sort  (cost= 3403 . 29 .. 3403 . 29  rows= 1  width= 108 ) (actual time= 8868 . 495 .. 9048 . 693  rows= 33353  loops= 1 )
        Sort Key: voicetarhnear.date, voicetarhnear.startcall, voicetarhnear.portfrom, pp.port, pp.phone
        ->  Nested Loop  (cost= 0 . 00 .. 3403 . 28  rows= 1  width= 108 ) (actual time= 1 . 365 .. 8033 . 640  rows= 33353  loops= 1 )
              ->  Index Scan using "PK__VoiceTarhNear" on voicetarhnear  (cost= 0 . 00 .. 1300 . 84  rows= 353  width= 64 ) (actual time= 0 . 925 .. 629 . 156  rows= 34367  loops= 1 )
                    Index Cond: ((date >= '2004-11-11 00:00:00'::timestamp without time zone) AND (date <= '2004-11-15 00:00:00'::timestamp without time zone))
              ->  Index Scan using ix_portphone_phone_port_g on portphone pp  (cost= 0 . 00 .. 5 . 94  rows= 1  width= 60 ) (actual time= 0 . 102 .. 0 . 166  rows= 1  loops= 34367 )
                    Index Cond: (((pp.phone)::text @> ("outer".callingid)::text) AND ((pp.port)::text @> ("outer".portfrom)::text) AND (pp.date_from <= "outer".date) AND (pp.date_to >= "outer".date))
Total runtime:  9463 . 387  ms

Реальное время (не Explain Analyze): ~13000 ms и ~7400 ms.
Результаты запросов идентичны.

Если убрать из подзапроса по voicetarh самый распространенный случай, где port <@ 'e0_' and phone<@'' (в результате) (и тогда лишь 19976 строк результата), то ~10100ms и ~4800ms.

Платформа:
Celeron 2,4Ghz, 512 RAM(dual), WD80JB.
Под виндой - VmWare 4
Виртуальная машина:192Mb RAM, 2G HDD, Slackware 10, gcc 3.3.2, RaiserFS.

Пожалуйста, пришлите замечания, исправления, предложения. Буду очень рад, ибо новичок :-)
...
Рейтинг: 0 / 0
19.04.2005, 15:36
| Ответить | Цитировать | Написать  
Индекс для B в A LIKE B || '%'
    #33023522
Funny_Falcon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Funny_Falcon
Участник
Извините, фаил потерял :-)
...
Рейтинг: 0 / 0
19.04.2005, 15:46
| Ответить | Цитировать | Написать  
3 сообщений из 3, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Индекс для B в A LIKE B || '%'
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]