1

I want to run a query like this:

SELECT *
FROM "core_user"
WHERE UPPER("core_user"."email"::text) = UPPER('emailsearch@example.com')
ORDER BY "core_user"."last_login" DESC
LIMIT 1;

I've tried adding two indexes:

CREATE INDEX core_user_upper_idx ON public.core_user USING btree (upper((email)::text));
CREATE INDEX core_user_last_lo_0521b8_idx ON public.core_user USING btree (last_login);

core_user_upper_idx works well if I remove the ordering clause, but in its current state explain gives

 Limit  (cost=0.29..10.33 rows=1 width=431)
   ->  Index Scan Backward using core_user_last_lo_0521b8_idx on core_user  (cost=0.29..5531.53 rows=551 width=431)
         Filter: (upper((email)::text) = 'EMAIL@EXAMPLE.COM'::text)

which doesn't perform very well.

Is there a better index for me to add here? I'm a bit unsure as to whether I should "prioritise" the email or last_login fields.

2

A multicolumn expression index should serve your particular query best:

CREATE INDEX ON public.core_user (upper(email), last_login DESC);

If last_login can be NULL, consider NULLS LAST - in index and query.
The rule of thumb is: equality first, range later. See:

4
  • Amazing, thanks! NULLS LAST works well too
    – MDalt
    Apr 29 at 12:33
  • The DESC is not necessary. Apr 29 at 16:09
  • @LaurenzAlbe: Not necessary, but if last_login can be NULL and the OP meant DESC NULLS LAST to begin with, it's faster that way. Apr 30 at 13:12
  • @LaurenzAlbe: Just stumbled across another fine post of yours, addressing the case exactly: dba.stackexchange.com/a/254761/3684 May 6 at 17:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.