Имеется табличка:

create table sq_www_urls (
id serial primary key,
c_id int not null,
c_action char not null default 'd',
c_url varchar(128) unique not null,
);

Имеется хитрый запрос к ней:

select * from sq_www_urls where position (c_url in 'www.bis.net/bs/') =1 and char_length('www.bis.net/bs/') >= char_length(c_url);

В базе сейчас порядка 90.000 записей и запрос выполняется около 1,5 секунд. Это время хорошо бы уменьшить вдвое, в идеале - втрое. Можно ли как-то оптимизировать запрос?

Нет. В базе хранятся запрещенные к просмотру инет-адреса. Выборка производится по критерию редиректоров - все, что начинается с содержащегося в базе И больше по длине - блокировать, выдавая запись из базы.
Сумбурно, но как-то не формулируется конкретнее... Одним словом, простое равенство для моих целей ЗДЕСЬ не подходит.

LeXa NalBatwhere 'www.bis.net/bs/' like c_url||'%'

Выглядит, конечно, красивше моего монстроидного where... но результатов не дает.
Наковырял тут еще кое-что, сделал еще вот такой индекс

create index idx_urls on sq_www_urls (lower(c_url) varchar_pattern_ops);

но все равно выполняется seq scan

explain select * from sq_www_urls where 'www.data.ru/pupkin/images/' like lower(c_url)||'%';
QUERY PLAN
---------------------------------------------------------------------------------------
Seq Scan on sq_www_urls (cost=100000000.00..100001354.94 rows=255 width=37)
Filter: ('www.data.ru/pupkin/images/'::text ~~ (lower((c_url)::text) || '%'::text))
(2 rows)

LeXa NalBatТакой запрос не может использовать индекс. Я его привел только потому, что "красивше". :-)
:) Спасибо. Но мне бы "пошустрее" :)
LeXa NalBatПопробуйте через where c_url in.
Спасибо за совет, сейчас помудрю... но не думаю, что поможет :(...

JelisМожно разбить урл на две части - имя хоста и путь.
А это интересная идея. Спасибо, попробую.

Andrey DaeronВ теории можно посмотреть в сторону GIST индексов.
Уже смотрел. Получается то же самое, только в профиль :)
Хочу еще посмотреть в сторону полнотекстового поиска, только это будет как из пушки по воробьям для данной задачи.

Jelis shrdlu JelisМожно разбить урл на две части - имя хоста и путь.
А это интересная идея. Спасибо, попробую.
"Прграммирование есть алгоритмы + структуры данных." Д.Кнут. Про второе зачастую забывают, а зря :-)
:) Да уж точно, что-нибудь да накосячишь. Кстати, совет реально помог, на тестовых таблицах со 150.000 записей запрос стал выполняться втрое быстрее, так что еще раз большое спасибо!

Jelis LeXa NalBat2 Jelis: Есть сомнения, что у автора, в реализации предложенного вами алгоритма, постгрес использует index scan.
Да уж... в 3 раза... сомнения вполне обоснованные!
Так что, shrdlu, в самом деле, покажите что у вас за табличка получилась и explain запроса - может его еще "ускорить" можно!:-)

С удовольствием приму любые рекомендации, но мне кажется, что своей цели я уже достиг. Впрочем, возможно я и неправ? Вот что имею на текущий момент:

squid=# \d sq_www_url_domain
Таблица "public.sq_www_url_domain"
Колонка | Тип | Модификаторы
---------+-----------------------+----------------------------------------------------------------
id | bigint | not null default nextval('sq_www_url_domain_id_seq'::regclass)
domain | character varying(80) | not null
Индексы:
"sq_www_url_domain_pkey" PRIMARY KEY, btree (id)
"sq_www_url_domain_domain_key" UNIQUE, btree ("domain")

squid=# \d sq_www_url_path
Таблица "public.sq_www_url_path"
Колонка | Тип | Модификаторы
----------+------------------------+--------------------------------------------------------------
id | bigint | not null default nextval('sq_www_url_path_id_seq'::regclass)
d_id | bigint | not null
c_id | integer | not null
c_action | character(1) | not null default 'd'::bpchar
c_url | character varying(128) | not null
Индексы:
"sq_www_url_path_pkey" PRIMARY KEY, btree (id)
"sq_www_url_path_c_url_key" UNIQUE, btree (c_url)
"idx_d_id" btree (d_id)
Ограничения по внешнему ключу:
"category_link" FOREIGN KEY (c_id) REFERENCES sq_category_dict(c_id)
"domain_link" FOREIGN KEY (d_id) REFERENCES sq_www_url_domain(id)

squid=# select count(*) from sq_www_url_domain; count
---------
1000000
(1 запись)

squid=# select count(*) from sq_www_url_path; count
---------
1000000
(1 запись)


squid=# explain analyze select c_action from sq_www_url_path where c_id='3' and 'pupkin/images/' like c_url||'%' and
d_id=(select id from sq_www_url_domain where domain = 'data.ru');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_d_id on sq_www_url_path (cost=8.59..16.97 rows=1 width=5) (actual time=0.078..0.080 rows=1 loops=1)
Index Cond: (d_id = $0)
Filter: ((c_id = 3) AND ('pupkin/images/'::text ~~ ((c_url)::text || '%'::text)))
InitPlan
-> Index Scan using sq_www_url_domain_domain_key on sq_www_url_domain (cost=0.00..8.59 rows=1 width=8) (actual time=0.036..0.038 rows=1 loops=1)
Index Cond: (("domain")::text = 'data.ru'::text)
Total runtime: 0.152 ms
(7 rows)

По-моему, индексы исправно используются...

squid=#