X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.225.181]) by svr1.postgresql.org (Postfix) with ESMTP id 81882CC444 for ; Tue, 15 Jul 2003 18:12:11 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr5.postgresql.org [64.117.225.181]) (amavisd-new, port 10024) with ESMTP id 64525-04 for ; Tue, 15 Jul 2003 18:12:29 +0000 (GMT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 0EEB1CC3C3 for ; Tue, 15 Jul 2003 15:12:00 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h6FICTQ3004246; Tue, 15 Jul 2003 14:12:29 -0400 (EDT) To: "David Olbersen" Cc: pgsql-sql@postgresql.org Subject: Re: Functional Indexes In-reply-to: References: Comments: In-reply-to "David Olbersen" message dated "Tue, 15 Jul 2003 10:36:42 -0700" Date: Tue, 15 Jul 2003 14:12:29 -0400 Message-ID: <4245.1058292749@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/192 X-Sequence-Number: 14306 "David Olbersen" 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