X-Original-To: pgsql-performance@postgresql.org Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id 2192F475D91 for ; Thu, 20 Mar 2003 20:27:29 -0500 (EST) Received: from [66.219.92.2] (HELO temoku) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 2913050; Thu, 20 Mar 2003 17:27:15 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: "David Olbersen" , Subject: Re: Help with LIKE Date: Thu, 20 Mar 2003 17:27:21 -0800 User-Agent: KMail/1.4.3 References: In-Reply-To: MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200303201727.21401.josh@agliodbs.com> X-Archive-Number: 200303/146 X-Sequence-Number: 1444 David, > My mistake, things don't get much better. >=20 > I'm selecting URLs out of a database like this: >=20 > SELECT * FROM table WHERE url ~ '^http://.*something.*$'; That search still requires a seq scan, since it has "gaps" in the seqence o= f=20 characters. That is, url ~ '^http://www.something.*' could use an index, but your search above= =20 cannot. You may be right that the standard OpenFTS indexing won't help you in this= =20 case, since you're really searching for fragments of a continuous text=20 string. One thing I might suggest is that you look for ways that you might be able = to=20 break out the text you're searching for with a function. For example, if y= ou=20 were searching strictly on the domain SLD name, then you could create an=20 "immutable" function called if_split_sld(TEXT) and index on that. If you are really searching for "floating" text within the string, I believ= e=20 that there are some options in tseach to help you, but they may not end up= =20 improving performance much. --=20 -Josh Berkus Aglio Database Solutions San Francisco