Re: Functional Indexes

"David Olbersen" <DOlbersen(at)stbernard(dot)com> writes:
> I have a function (urlhost) which finds the 'host' portion of a URL. In the case of http://www.foobar.com/really/long/path/to/a/file it returns "www.foobar.com".
> I also have a function (urltld) which returns the TLD of a URL. In the case of http://www.foobar.com/really/long/path/to/a/file it returns ".com" (the leading dot is OK).
> urltld uses urlhost to do it's job (how should be apparent).

> Now the question: is there a single index I can create that will be
> used when my WHERE clause contains either urlhost or urltld?

I do not see any way with functions declared like that. Quite aside
from implementation limitations, the portion of the 'host' string that
urltld is interested in would be the low-order part of the indexed
strings, and you can't usefully use an index to search for low-order
digits of the key.

Could you instead define an index over the reversed host name (eg,
com.foobar.www)? This would seem to provide about the same
functionality for searches on urlhost, and you could exploit the index
for TLD searching via prefixes. For example:

regression=# create table t1 (f1 text);
CREATE TABLE
regression=# create index t1i on t1 (lower(f1));
CREATE INDEX
regression=# explain select * from t1 where lower(f1) like 'com.%';
QUERY PLAN
----------------------------------------------------------------------------
Index Scan using t1i on t1 (cost=0.00..17.08 rows=5 width=32)
Index Cond: ((lower(f1) >= 'com.'::text) AND (lower(f1) < 'com/'::text))
Filter: (lower(f1) ~~ 'com.%'::text)
(3 rows)

regards, tom lane