From: Josh Berkus <josh@agliodbs.com>
To: David Olbersen <DOlbersen@stbernard.com>
To: pgsql-performance@postgresql.org
Subject: Re: Help with LIKE
Date: Thu, 20 Mar 2003 17:27:21 -0800
Message-ID: <200303201727.21401.josh@agliodbs.com> (raw)
In-Reply-To: <E7E213858379814A9AE48CA6754F5ECB0D6C33@mail01.stbernard.com>
References: <E7E213858379814A9AE48CA6754F5ECB0D6C33@mail01.stbernard.com>
David,
> My mistake, things don't get much better.
>
> I'm selecting URLs out of a database like this:
>
> SELECT * FROM table WHERE url ~ '^http://.*something.*$';;
That search still requires a seq scan, since it has "gaps" in the seqence of
characters. That is,
url ~ '^http://www.something.*'; could use an index, but your search above
cannot.
You may be right that the standard OpenFTS indexing won't help you in this
case, since you're really searching for fragments of a continuous text
string.
One thing I might suggest is that you look for ways that you might be able to
break out the text you're searching for with a function. For example, if you
were searching strictly on the domain SLD name, then you could create an
"immutable" function called if_split_sld(TEXT) and index on that.
If you are really searching for "floating" text within the string, I believe
that there are some options in tseach to help you, but they may not end up
improving performance much.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: josh@agliodbs.com, DOlbersen@stbernard.com
Subject: Re: Help with LIKE
In-Reply-To: <200303201727.21401.josh@agliodbs.com>
References: <E7E213858379814A9AE48CA6754F5ECB0D6C33@mail01.stbernard.com> <200303201727.21401.josh@agliodbs.com>
* 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