pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedLIKE and INDEX
3+ messages / 3 participants
[nested] [flat]
* LIKE and INDEX
@ 2004-04-28 22:02 Jie Liang <jie@stbernard.com>
2004-05-05 17:31 ` Re: LIKE and INDEX Rod Taylor <pg@rbt.ca>
2004-05-05 18:12 ` Re: LIKE and INDEX Richard Huxton <dev@archonet.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-04-28 22:02 LIKE and INDEX Jie Liang <jie@stbernard.com>
@ 2004-05-05 17:31 ` Rod Taylor <pg@rbt.ca>
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-04-28 22:02 LIKE and INDEX Jie Liang <jie@stbernard.com>
@ 2004-05-05 18:12 ` Richard Huxton <dev@archonet.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