Re: LIKE and INDEX

Поиск
Список
Период
Сортировка
Искать

Re: LIKE and INDEX

От:
Rod Taylor <pg@rbt.ca>
Дата:
> but if I use:
> select url from urlinfo where url like 'http://%.lycos.de';
> it won't use index at all, NOT good!
> is there any way I can force secon query use index???

create index nowww on urlinfo(replace(replace(url, 'http://', ''),
'www.', '')));

SELECT url
  FROM urlinfo
WHERE replace(replace(url, 'http://', ''), 'www.', '') = 'lycos.de'
   AND url LIKE 'http://%.lycos.de' ;

The replace() will narrow the search down to all records containing
lycos.de. Feel free to write a more complex alternative for replace()
that will deal with more than just optional www.

Once the results have been narrowed down, you may use the original like
expression to confirm it is your record.

Re: LIKE and INDEX

От:
Richard Huxton <dev@archonet.com>
Дата:
Jie Liang wrote:
> All,
> This is old topic, when I use:
> select url from urlinfo where url like 'http://www.lycos.de%';
> it uses the index, good!
> 
> but if I use:
> select url from urlinfo where url like 'http://%.lycos.de';
> it won't use index at all, NOT good!
> is there any way I can force secon query use index???

I've seen people define a reverse(text) function via plperl or similar 
then build a functional index on reverse(url). Of course, that would 
rely on your knowing which end of your search pattern has the % wildcard.

-- 
   Richard Huxton
   Archonet Ltd

LIKE and INDEX

От:
"Jie Liang" <jie@stbernard.com>
Дата:
All,
This is old topic, when I use:
select url from urlinfo where url like 'http://www.lycos.de%';
it uses the index, good!

but if I use:
select url from urlinfo where url like 'http://%.lycos.de';
it won't use index at all, NOT good!
is there any way I can force secon query use index???

Thanks.

Jie Liang

                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Index Scan using urlinfo_ukey on urlinfo  (cost=0.00..6.01 rows=1 width=33)
   Index Cond: ((url >= 'http://www.lycos.de/'::text) AND (url < 'http://www.lycos.de0'::text))
   Filter: (url ~ '^http://www\\.lycos\\.de/.*$'::text)
(3 rows)

                         QUERY PLAN                          
-------------------------------------------------------------
 Seq Scan on urlinfo  (cost=0.00..100440.48 rows=4 width=33)
   Filter: (url ~ '^http://.*\\.lycos\\.de$'::text)
(2 rows)
FAQ