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.
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt