![]() ![]() Partial indexes, briefly, let you leverage a “where” clause in the index itself. This was my first idea for a partial index. Postgres=# explain analyze verbose select * from users where email like '%aol.com' create index idx_user_email on users(email) This index isn’t going to help due to the left-most wildcard, but let’s prove it. update users set email=concat(name, where percentage Parallel Seq Scan on ers (cost=0.25 rows=159 width=35) (actual time=0.478.339.496 rows=614 loops=3)įilter: ((users.email)::text ~~ '%aol.com'::text) update users set email=concat(name, where email not like '%' įinally, a small handful of email addresses from our target domain. update users set email=concat(name, where percentage>.20 Īnd some from another known domain. The percentage column was created with random() so we can use it for domain population. ![]() Our test data is only in the low millions, but it will give us enough to work with. The test data has mostly email addresses from a large known domain. You need to find a small handful of email addresses from a particular domain. The situation: billions and billions of email addresses. That problem didn’t go unsolved, but it certainly wasn’t as easy as I’ll demonstrate below. When I first heard of Postgres partial indexes, I knew immediately that this would have solved a problem I had in MySQL about a decade ago. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |