Skip site navigation (1) Skip section navigation (2)

Re: index scan with functional indexes -- solved

From: Kevin Brown <kevin(at)sysexperts(dot)com>
To: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: index scan with functional indexes -- solved
Date: 2004-01-31 03:35:52
Message-ID: 20040131033551.GB2608@filer (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
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

In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2004-01-31 04:34:39
Subject: Re: dump + restore didn't include schemas
Previous:From: Tatsuo IshiiDate: 2004-01-31 02:25:22
Subject: Re: [PATCHES] v7.4.1 text_position() patch

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group