Re: index scan with functional indexes -- solved

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 kevin(at)sysexperts(dot)com