0

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.

  • @JitendraSoftgrid the problem here is that the condition will be just on if there is a value=on but as we can have multiple same user_id i have to exclude where there is a value='on' and value='off' for the same user_id – Medo Apr 30 '18 at 10:46
  • 1
    Given the data you provided user_id, name can't be primary key – Lennart Apr 30 '18 at 10:51
  • 1
    Your example data does not match with your DDL. That's what Lennart meant. – DEarTh Apr 30 '18 at 11:03
  • 1
    But there are 4 entries for same name and id for mike.. ? and 3 entries for joe..?? – DEarTh Apr 30 '18 at 11:15
  • 1
    You said primary_key(user_id,name) but there a multiple rows for e.g. 1 and mike in your sample data. That is what doesn't add up. – sticky bit Apr 30 '18 at 11:17
0

In addition to using an EXISTS as Lennart proposed, try the following indexes:

CREATE INDEX users_timestamp_value
             ON users
                (timestamp,
                 value);

CREATE INDEX users_user_id_value
             ON users
                (user_id,
                 value);

They should support both WHEREs.

See if the indexes are accepted by the optimizer by looking at the plan. And check whether they do any good.

1

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.

  • thanks for the collaboration , for my case is to make an optimizer selection , not a table creating , the query proposed is working but just i 'am looking for another insights if there any to optimize it :-) – Medo Apr 30 '18 at 10:03
  • probably, but it is hard to tell without knowing any details of your table – Lennart Apr 30 '18 at 10:04
  • i updated my question hope that is more clear thank you for your time – Medo Apr 30 '18 at 10:16
  • Not really, what indexes are there? what type do the columns have, are they nullable or not, what does the current execution plan look like? Trying to answer your question without such details is a shot in the dark. Thats why I asked for CREATE TABLE statements including keys and indexes. – Lennart Apr 30 '18 at 10:34
  • i added the indexes , and the table description ,thanks indeed – Medo Apr 30 '18 at 10:44
0

If user_id and name in a 1:1 relationship, you should consider normalizing it out of this table. In any case, rethink the choice of PRIMARY KEY(user_id, name).

SELECT user_id
    FROM (
        SELECT user_id, MAX(timestamp) AS ts
               FROM users
               WHERE value = 'ON'
               GROUP BY user_id ) AS a
    LEFT JOIN (
        SELECT user_id, MAX(timestamp) AS ts
               FROM users
               WHERE value = 'OFF'
               GROUP BY user_id ) AS b
        ON b.user_id = a.user_id
       AND b.ts > a.ts
    WHERE b.user_id IS NULL;

With

INDEX(value, user_id, timestamp)  -- probably in this order

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.