Обсуждение: LIKE and INDEX

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)

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

> 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.