extracting the domain from an email address

From: Mark Steben <mark(dot)steben(at)drivedominion(dot)com>
To: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: extracting the domain from an email address
Date: 2019-01-16 13:20:17
Message-ID: CADyzmyx0hx6W3WL9ew7D2i404M=R0DDOLgdv7MhdMVPB-0-OgA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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.

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
www.twitter.com/DominionDealer
www.drivedominion.com <http://www.autorevenue.com/>

<http://autobasedigital.net/marketing/DD12_sig.jpg>

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Achilleas Mantzios 2019-01-16 13:47:12 Re: extracting the domain from an email address
Previous Message Morris de Oryx 2019-01-16 11:29:44 Re: How to set default owner of objects in Postgresql