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