Dave Cramer wrote:
Interesting it works now, and the good news is it is *WAY* faster, this
might be able to speed up marc's doc search by orders of magnitude
this is searching 100536 rows
select * from url where fn_strrev(url) like fn_strrev('%beta12.html');
1.57ms
explain select * from url where url like '%beta12.html';
3310.38 ms
The nice thing about this is that you can create your query thusly:
SELECT * from table WHERE column like 'string' AND fn_strrev(column)
LIKE fn_strrev('string')
and, if you have both a standard index on column and a functional index
on fn_strrev(column), the query will be fast (well, as fast as the
pattern in question allows) as long as 'string' is anchored on either end.
I've implemented the 'locate' utility in Perl using a PG backend instead
of the standard locate database. I internally convert globs given as
arguments into LIKE strings, and with a functional index like that the
searches are now blazingly fast -- faster than the original 'locate'
utility. It has the added advantage that you can specify a file type
to further narrow the search (thus 'locate --type file core' will find
all regular files named 'core' in the database).
I'll be happy to share my code with anyone who's interested.
--
Kevin Brown [EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
