From: Richard Huxton <dev@archonet.com>
To: gnanam@zoniac.com
Cc: pgsql-performance@postgresql.org
Subject: Re: How to boost performance of queries containing pattern matching characters
Date: Mon, 14 Feb 2011 07:56:56 +0000
Message-ID: <4D58E048.9020506@archonet.com> (raw)
In-Reply-To: <004801cbcc1b$3f1d4fe0$bd57efa0$@com>
References: <004601cbcc14$acfe99d0$06fbcd70$@com>
<4D58D752.2000100@archonet.com>
<004701cbcc18$c54c8980$4fe59c80$@com>
<4D58DC35.8010007@archonet.com>
<004801cbcc1b$3f1d4fe0$bd57efa0$@com>
On 14/02/11 07:46, Gnanakumar wrote:
>> If you really need to match all those options, you can't use an index. A
>> substring-matching index would need to have multiple entries per
>> character per value (since it doesn't know what you will search for).
>> The index-size becomes unmanageable very quickly.
>
>> That's why I asked what you really wanted to match.
> To be more specific, in fact, our current application allows to delete
> email(s) with a minimum of 3 characters. There is a note/warning also given
> for application Users' before deleting, explaining the implication of this
> delete action (partial& case-insensitive, and it could be wide-ranging
> too).
>
>> So, I'll ask again: do you really want to match all of those options?
> Yes, as explained above, I want to match all those.
Then you can't use a simple index. If you did use an index it would
probably be much slower for "com" or "yah" or "gma" and so on.
The closest you can do is something like Artur's option (or the pg_trgm
module - handy since you are looking at 3-chars and up) to select likely
matches combined with a separate search on '%domain.com%' to confirm
that fact.
P.S. - I'd be inclined to just match the central domain parts, so for
"user1@europe.megacorp.com" you would index "europe" and "megacorp" and
only allow matching on the start of each string. Of course if your
application spec says you need to match on "p.c" too then that's what
you have to do.
--
Richard Huxton
Archonet Ltd
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-performance@postgresql.org
Cc: dev@archonet.com, gnanam@zoniac.com
Subject: Re: How to boost performance of queries containing pattern matching characters
In-Reply-To: <4D58E048.9020506@archonet.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