Re: extracting the domain from an email address

[Achilleas Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx>]

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 '@(.*)$')) WHERE length(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 | Autobase 
  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

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt