Skip to content

Instantly share code, notes, and snippets.

@jelder
Created March 12, 2015 17:06
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save jelder/a25ba892f269f324a535 to your computer and use it in GitHub Desktop.
Save jelder/a25ba892f269f324a535 to your computer and use it in GitHub Desktop.
Filtering emails by domain in PostgreSQL
BEGIN;
CREATE FUNCTION email_domain(email text) RETURNS text
LANGUAGE sql IMMUTABLE
AS $$
SELECT reverse(split_part(lower(email), '@', 2))
$$;
CREATE INDEX idx_users_email_domain ON users (email_domain(email));
EXPLAIN ANALYZE SELECT "users".* FROM "users" WHERE email_domain(email) = 'moc.sseldnuob' ORDER BY "users"."proposed_changes_count" DESC;
ROLLBACK;
Timing is on.
BEGIN
Time: 0.253 ms
CREATE FUNCTION
Time: 1.516 ms
CREATE INDEX
Time: 10614.353 ms
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=6525.17..6529.08 rows=1564 width=1994) (actual time=11.447..12.107 rows=1108 loops=1)
Sort Key: proposed_changes_count
Sort Method: external merge Disk: 688kB
-> Bitmap Heap Scan on users (cost=36.55..5087.69 rows=1564 width=1994) (actual time=0.907..8.351 rows=1108 loops=1)
Recheck Cond: (reverse(split_part(lower((email)::text), '@'::text, 2)) = 'moc.sseldnuob'::text)
-> Bitmap Index Scan on idx_users_email_domain (cost=0.00..36.16 rows=1564 width=0) (actual time=0.670..0.670 rows=1108 loops=1)
Index Cond: (reverse(split_part(lower((email)::text), '@'::text, 2)) = 'moc.sseldnuob'::text)
Total runtime: 14.187 ms
(8 rows)
Time: 17.440 ms
ROLLBACK
Time: 1.439 ms
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment