1

I want to run a query like this:

SELECT *
FROM "core_user"
WHERE UPPER("core_user"."email"::text) = UPPER('[email protected]')
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 protected]'::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.

1 Answer 1

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
    Commented Apr 29, 2021 at 12:33
  • 1
    The DESC is not necessary. Commented Apr 29, 2021 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. Commented Apr 30, 2021 at 13:12
  • @LaurenzAlbe: Just stumbled across another fine post of yours, addressing the case exactly: dba.stackexchange.com/a/254761/3684 Commented May 6, 2021 at 17:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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