pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
LIKE and INDEX
3+ messages / 3 participants
[nested] [flat]

* LIKE and INDEX
@ 2004-04-28 22:02  Jie Liang <jie@stbernard.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Jie Liang @ 2004-04-28 22:02 UTC (permalink / raw)
  To: pgsql@postgresql.org; +Cc: pgsql-performance

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)



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: LIKE and INDEX
@ 2004-05-05 17:31  Rod Taylor <pg@rbt.ca>
  parent: Jie Liang <jie@stbernard.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Rod Taylor @ 2004-05-05 17:31 UTC (permalink / raw)
  To: Jie Liang <jie@stbernard.com>; +Cc: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: LIKE and INDEX
@ 2004-05-05 18:12  Richard Huxton <dev@archonet.com>
  parent: Jie Liang <jie@stbernard.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Richard Huxton @ 2004-05-05 18:12 UTC (permalink / raw)
  To: Jie Liang <jie@stbernard.com>; +Cc: pgsql@postgresql.org; pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2004-05-05 18:12 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-28 22:02 LIKE and INDEX Jie Liang <jie@stbernard.com>
2004-05-05 17:31 ` Rod Taylor <pg@rbt.ca>
2004-05-05 18:12 ` Richard Huxton <dev@archonet.com>

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