Postgresql query optimisation with left join

Question

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')) 
LIMIT 30

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 ?

• Gerard H. Pille: 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?
• Phill W.: 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.
• Sebastian Dusza: Is it possible to update the index to handle is null predicate ?
• Vérace: 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?
• jjanes: 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.

Answer 1

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);

Answer 2

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.