Optimize query to filter active IDs for a given day from big table I have a PostgreSQL table called users with 4 columns:

user_id   integer,       -- we can have a lot of the same user_id
name      text,          -- name of the user
value     text NOT NULL, -- enumeration values: ok, not ok, on,off
timestamp timestamp default current_timestamp NOT NULL, -- date of insertion
primary_key(user_id,name)


I have to select distinct user_id where timestamp='date x' and value='on' not having value='off' after. I made this query:

select distinct user_id,name
from users
where (timestamp='date x' and value='on')
and user_id not in (select distinct user_id from users  where value='off')


But it is very slow because there are more than 300K users to handle. Is there a way to make the query faster?

user_id name    value    timestamp
1       mike    not ok   2018-03-27 10:24
1       mike    on       2018-03-27 11:22
1       mike    ok       2018-03-27 13:33
1       mike    off      2018-03-27 15:33

2       joe     not ok   2018-03-28 10:24
2       joe     on       2018-03-28 11:22
2       joe     ok       2018-03-28 13:33


The result wanted is:

  user_id    name
    2         joe


because user_id 1 has a value off in the table.
You could try not exists:

select distinct user_id 
from users u1 
where u1.timestamp='date x' 
  and u1.value='on' 
  and not exists (
      select 1
      from users u2
      where u1.user_id = u2.user_id
        and u2.value='off'
  ); 


If you want more qualified guesses, edit your question with CREATE TABLE statement including keys and indexes.