Created
March 12, 2015 17:06
-
-
Save jelder/a25ba892f269f324a535 to your computer and use it in GitHub Desktop.
Filtering emails by domain in PostgreSQL
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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