I'm trying to optimize query run on Postgresql 12.

There are 2 tables

  • users (5 mln rows)
  • grants (8 mln rows)

I'm running a following query

SELECT * FROM users u 
LEFT JOIN grants g ON u.id = g.user_id 
WHERE g.id IS NULL AND ((u.last_activity < '2021-05-27 05:30:26.158896+00') OR 
(u.last_activity IS NULL AND u.created < '2021-05-27 05:30:26.158896+00')) 

Execution plan looks like this:

enter image description here

Following indexes exist:

CREATE UNIQUE INDEX pk ON users USING btree (id);
CREATE INDEX idx ON users USING btree (last_activity, created, id);

CREATE UNIQUE INDEX pk  ON grants USING btree (id);
CREATE UNIQUE INDEX idx ON grants USING btree (user_id, application_id);

Currently this query takes about 15s to run. It seems that the (last_activity, created, id) index on users table is not being used.

Any ideas how to optimize this ?

  • 2
    I wonder if a "not exists" on the grants wouldn't be more effective than joining, since it looks that users without grants are rather rare. About unused indexes: when was the table last analyzed, is the "u.last_activity < '2021-05-27 05:30:26.158896+00'" selective enough? Nov 30 '21 at 9:34
  • 1
    Null values may not be included in Indexes, so (u.last_activity is null) is likely causing a Table Scan (or, at the very least, it's preventing the database doing anything cleverer). I'd suggest pulling this into two queries (one for each half of the "where" clause, split at the "or"), optimise each query separately and 'union' the results back together.
    – Phill W.
    Nov 30 '21 at 9:59
  • Is it possible to update the index to handle is null predicate ? Nov 30 '21 at 10:29
  • 1
    Hi, and welcome to dba.se! Have you considered partial indexes where you have a very low number of records with the values you select/filter on? Nov 30 '21 at 10:36
  • Please show us an EXPLAIN (ANALYZE, BUFFERS) as text, not as an image of text. From you image, we can see how many rows it though would pass the filter, but we also need to know how many actually did pass it.
    – jjanes
    Dec 1 '21 at 3:34

I would try the following:

select * 
from users u 
where not exists (select * 
                  from grants g 
                  where u.id = g.user_id) 
  and coalesce(u.last_activity, u.created) < '2021-05-27 05:30:26.158896+00';

Together with this index:

create index on users ( coalesce(last_activity, created), id);

With your query, it doesn't realize it is doing an antijoin, because g.id is NULL confused the planner. If you rewrite that to g.user_id is null, then it knows it is doing an antijoin, and will likely come up with a much better plan.

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.