From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Olbersen <DOlbersen@stbernard.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Functional Indexes
Date: Tue, 15 Jul 2003 14:12:29 -0400
Message-ID: <4245.1058292749@sss.pgh.pa.us> (raw)
In-Reply-To: <E7E213858379814A9AE48CA6754F5ECB0D6FC9@mail01.stbernard.com>
References: <E7E213858379814A9AE48CA6754F5ECB0D6FC9@mail01.stbernard.com>
"David Olbersen" <DOlbersen@stbernard.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, DOlbersen@stbernard.com
Subject: Re: Functional Indexes
In-Reply-To: <4245.1058292749@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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