1

I have table with jsonb field - timetable which has next structure:

{
1: [{start:0, finish: 12}],
2: [{start:0, finish: 12}, {start:12, finish:17}],
3: [{start:12, finish: 17}],
4: [{start:0, finish: 12}],
5: [{start:12, finish:17}, {start:17, finish: 0}],
6: [{start:0, finish: 12}, {start:12, finish:17}],
7: [{start:17, finish: 0}]
}

1...7 are days of week, which represent when user is available - morning/afternoon/evening. I'm doing filter where user could choose morning/afternoon/evening and days of week and get available users. If user checked all three time periods and all days I will have 21 variants. I wrote next query:

SELECT * FROM users WHERE 
timetable <@ {1: [{start: 0, finish: 12}]} OR timetable <@ {1: [{start: 12, finish: 17}]} OR timetable <@ {1: [{start: 17, finish: 0}]} OR
timetable <@ {2: [{start: 0, finish: 12}]} OR timetable <@ {2: [{start: 12, finish: 17}]} OR timetable <@ {2: [{start: 17, finish: 0}]} OR
......
timetable <@ {7: [{start: 0, finish: 12}]} OR timetable <@ {7: [{start: 12, finish: 17}]} OR timetable <@ {7: [{start: 17, finish: 0}]}

Please advice me how to optimize my query. I know if user checked all possible periods and days I could just query with timetable is not null. But I want to optimize my task in general.

7
  • 7
    Why are you using JSON? Mar 1, 2018 at 14:20
  • 3
    To expand a little on Jack's question, you have this beautiful highly efficient standard-compliant open-source relational DBMS called PostgreSQL. Why are you using JSON for data storage?
    – Andriy M
    Mar 1, 2018 at 14:26
  • because postgresql works with jsonb:) This field is like of cache. User is available or not depends on 3 another entities and this field is result of computing.
    – Dmitro
    Mar 1, 2018 at 15:05
  • 2
    You are not using it like cache though. You are trying to do queries against items/subparts stored inside the json value. Mar 1, 2018 at 15:10
  • 1
    If it is a bunch of data that needs to be retrieved as a unit or document, JSON might be the way forward. If not, the way to "optimize your task" for joins, filtering, etc is not to use JSON. Mar 1, 2018 at 15:19

2 Answers 2

3

Moving away from JSONB

JSONB is not the right tool for every job. I'm going to fix your schema because everyone here is about to cry.

Here we use an ENUM type and timerange

CREATE TYPE dow AS ENUM (
  'Monday', 'Tuesday', 'Wednesday', 'Thursday',
  'Friday', 'Saturday', 'Sunday'
);

CREATE FUNCTION time_subtype_diff(x time, y time)
RETURNS float8 AS $$
  SELECT EXTRACT(EPOCH FROM (x - y))
$$ LANGUAGE sql
STRICT IMMUTABLE;

CREATE TYPE timerange AS RANGE (
    subtype = time,
    subtype_diff = time_subtype_diff
);

CREATE TABLE available (
  user_id  int,  -- REFERENCES users,
  dow      dow,
  trange  timerange,
  PRIMARY KEY (user_id, dow)
);

now you can be sane (and we support minutes!)

CREATE TABLE users(user_id) AS VALUES (1::int);

INSERT INTO available VALUES 
  ( 1, 'Monday', '[13:42,16:12]' );

SELECT *
FROM users AS u
JOIN available AS a USING (user_id)
WHERE dow = 'Monday' AND a.trange @> '[14:00,15:00]';
2
  • What kind of index strategy its the best for this kind of custom enum type mixed with float, GIST maybe? @Evan Carroll
    – Imanol Y.
    Mar 19, 2018 at 15:54
  • 1
    Yes, CREATE EXTENSION btree_gist; and then create a gist index on (user_id, dow, trange) Mar 19, 2018 at 15:56
1

Not sure if its the best option, but my approach to this is in the past, is to transform the jsonb data into another more convenient data adapted to your query with a function and index it.

   CREATE OR REPLACE FUNCTION weekly_activity(timetable jsonb) RETURNS integer AS $$
    BEGIN
         IF timetable <@ {1: [{start: 0, finish: 12}]} OR timetable <@ {1: [{start: 12, finish: 17}]} THEN
           RETURN 111;
         END IF;
    END;
    $$ LANGUAGE plpgsql IMMUTABLE; 

    CREATE INDEX CONCURRENTLY idx_weekly_activity ON table BTREE( weekly_activity(timetable)) 

    SELECT * FROM table WHERE weekly_activity(timetable) = 111;

Silly example with just one case but you can adapt it to your situation.

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.