From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: extracting the domain from an email address
Date: Wed, 16 Jan 2019 15:47:12 +0200
Message-ID: <0729dd40-9448-7529-155e-458676681b5e@matrix.gatewaynet.com> (raw)
In-Reply-To: <CADyzmyx0hx6W3WL9ew7D2i404M=R0DDOLgdv7MhdMVPB-0-OgA@mail.gmail.com>
References: <CADyzmyx0hx6W3WL9ew7D2i404M=R0DDOLgdv7MhdMVPB-0-OgA@mail.gmail.com>
On 16/1/19 3:20 μ.μ., Mark Steben wrote:
> Good morning,
> I am searching for a better more efficient way to extract the domain portion of an email address. The two I have been using are very expensive.
> The one extract I mostly use: substring(email from '@(.*)$')
> also has an index on the email column which is the full email address:
> btree (email DESC) WHERE email::text = "substring"(email::text, '@(.*)$'::text) AND length(email::text) > 0,
> The other extract:
> substr(e.email,(strpos(e.email, '@') + 1))
> Currently has no index.
Why don't you create an index on the extracted part including the where clause?
create index your_table_email_domain ON your_table(substring(email from '@(.*)$')) WHERElength(email::text) > 0 ;
>
> The referenced table has 72 million rows. The email column can be empty (ergo the length = 0 check. I am running postgresql 9.4.
>
> Any insights/observations welcome.
>
> --
> *Mark Steben*
> Database Administrator
> @utoRevenue <http://www.autorevenue.com/; | Autobase <http://www.autobase.net/;
> CRM division of Dominion Dealer Solutions
> 95D Ashley Ave.
> West Springfield, MA 01089
> t: 413.327-3045
> f: 413.383-9567
>
> www.fb.com/DominionDealerSolutions <http://www.fb.com/DominionDealerSolutions;
> www.twitter.com/DominionDealer <http://www.twitter.com/DominionDealer;
> www.drivedominion.com <http://www.autorevenue.com/;
>
>
>
>
>
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
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-admin@postgresql.org
Cc: achill@matrix.gatewaynet.com, pgsql-admin@lists.postgresql.org
Subject: Re: extracting the domain from an email address
In-Reply-To: <0729dd40-9448-7529-155e-458676681b5e@matrix.gatewaynet.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