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.

12
  • @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
    Commented Apr 30, 2018 at 10:46
  • 1
    Given the data you provided user_id, name can't be primary key Commented Apr 30, 2018 at 10:51
  • 1
    Your example data does not match with your DDL. That's what Lennart meant.
    – DEarTh
    Commented Apr 30, 2018 at 11:03
  • 1
    But there are 4 entries for same name and id for mike.. ? and 3 entries for joe..??
    – DEarTh
    Commented Apr 30, 2018 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
    Commented Apr 30, 2018 at 11:17

3 Answers 3

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.

5
  • 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
    Commented Apr 30, 2018 at 10:03
  • probably, but it is hard to tell without knowing any details of your table Commented Apr 30, 2018 at 10:04
  • i updated my question hope that is more clear thank you for your time
    – Medo
    Commented Apr 30, 2018 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. Commented Apr 30, 2018 at 10:34
  • i added the indexes , and the table description ,thanks indeed
    – Medo
    Commented Apr 30, 2018 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 and acknowledge you have read our privacy policy.

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