Re-design the table to store opening hours (hours of operation) as a set of
tsrange (range of
timestamp without time zone) values. Requires Postgres 9.2 or later.
Pick a random week to stage your opening hours. I like the week:
1996-01-01 (Monday) to 1996-01-07 (Sunday)
That’s the most recent leap year where Jan 1st conveniently happens to be a Monday. But it can be any random week for this case. Just be consistent.
Install the additional module
CREATE EXTENSION btree_gist;
Then create the table like this:
CREATE TABLE hoo ( hoo_id serial PRIMARY KEY , shop_id int NOT NULL -- REFERENCES shop(shop_id) -- reference to shop , hours tsrange NOT NULL , CONSTRAINT hoo_no_overlap EXCLUDE USING gist (shop_id with =, hours WITH &&) , CONSTRAINT hoo_bounds_inclusive CHECK (lower_inc(hours) AND upper_inc(hours)) , CONSTRAINT hoo_standard_week CHECK (hours <@ tsrange '[1996-01-01 0:0, 1996-01-08 0:0]') );
The one column
hours replaces all of your columns:
opens_on, closes_on, opens_at, closes_at
For instance, hours of operation from Wednesday, 18:30 to Thursday, 05:00 UTC are entered as:
'[1996-01-03 18:30, 1996-01-04 05:00]'
The exclusion constraint
hoo_no_overlap prevents overlapping entries per shop. It is implemented with a GiST index, which also happens to support our queries. Consider the chapter “Index and Performance” below discussing indexing strategies.
The check constraint
hoo_bounds_inclusive enforces inclusive boundaries for your ranges, with two noteworthy consequences:
- A point in time falling on lower or upper boundary exactly is always included.
- Adjacent entries for the same shop are effectively disallowed. With inclusive bounds, those would “overlap” and the exclusion constraint would raise an exception. Adjacent entries must be merged into a single row instead. Except when they wrap around Sunday midnight, in which case they must be split into two rows. The function
f_hoo_hours()below takes care of this.
The check constraint
hoo_standard_week enforces the outer bounds of the staging week using the “range is contained by” operator
With inclusive bounds, you have to observe a corner case where the time wraps around at Sunday midnight:
'1996-01-01 00:00+0' = '1996-01-08 00:00+0' Mon 00:00 = Sun 24:00 (= next Mon 00:00)
You have to search for both timestamps at once. Here is a related case with exclusive upper bound that wouldn’t exhibit this shortcoming:
To “normalize” any given
timestamp with time zone:
CREATE OR REPLACE FUNCTION f_hoo_time(timestamptz) RETURNS timestamp LANGUAGE sql IMMUTABLE PARALLEL SAFE AS $func$ SELECT timestamp '1996-01-01' + ($1 AT TIME ZONE 'UTC' - date_trunc('week', $1 AT TIME ZONE 'UTC')) $func$;
PARALLEL SAFE only for Postgres 9.6 or later.
The function takes
timestamptz and returns
timestamp. It adds the elapsed interval of the respective week
($1 - date_trunc('week', $1) in UTC time to the starting point of our staging week. (
To normalize ranges and split those crossing Mon 00:00. This function takes any interval (as two
timestamptz) and produces one or two normalized
tsrange values. It covers any legal input and disallows the rest:
CREATE OR REPLACE FUNCTION f_hoo_hours(_from timestamptz, _to timestamptz) RETURNS TABLE (hoo_hours tsrange) LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE COST 500 ROWS 1 AS $func$ DECLARE ts_from timestamp := f_hoo_time(_from); ts_to timestamp := f_hoo_time(_to); BEGIN -- sanity checks (optional) IF _to <= _from THEN RAISE EXCEPTION '%', '_to must be later than _from!'; ELSIF _to > _from + interval '1 week' THEN RAISE EXCEPTION '%', 'Interval cannot span more than a week!'; END IF; IF ts_from > ts_to THEN -- split range at Mon 00:00 RETURN QUERY VALUES (tsrange('1996-01-01', ts_to , '')) , (tsrange(ts_from, '1996-01-08', '')); ELSE -- simple case: range in standard week hoo_hours := tsrange(ts_from, ts_to, ''); RETURN NEXT; END IF; RETURN; END $func$;
INSERT a single input row:
INSERT INTO hoo(shop_id, hours) SELECT 123, f_hoo_hours('2016-01-11 00:00+04', '2016-01-11 08:00+04');
For any number of input rows:
INSERT INTO hoo(shop_id, hours) SELECT id, f_hoo_hours(f, t) FROM ( VALUES (7, timestamptz '2016-01-11 00:00+0', timestamptz '2016-01-11 08:00+0') , (8, '2016-01-11 00:00+1', '2016-01-11 08:00+1') ) t(id, f, t);
Each can insert two rows if a range needs splitting at Mon 00:00 UTC.
With the adjusted design, your whole big, complex, expensive query can be replaced with … this:
WHERE hours @> f_hoo_time(now());
For a little suspense I put a spoiler plate over the solution. Move the mouse over it.
The query is backed by said GiST index and fast, even for big tables.
If you want to calculate total opening hours (per shop), here is a recipe:
Index and Performance
Currently, only the B-tree, GiST, GIN, and BRIN index types support multicolumn indexes.
And the order of index columns matters:
A multicolumn GiST index can be used with query conditions that
involve any subset of the index’s columns. Conditions on additional
columns restrict the entries returned by the index, but the condition
on the first column is the most important one for determining how much
of the index needs to be scanned. A GiST index will be relatively
ineffective if its first column has only a few distinct values, even
if there are many distinct values in additional columns.
So we have conflicting interests here. For big tables, there will be many more distinct values for
shop_id than for
- A GiST index with leading
shop_idis faster to write and to enforce the exclusion constraint.
- But we are searching
hoursin our query. Having that column first would be better.
- If we need to look up
shop_idin other queries, a plain btree index is much faster for that.
- To top it off, I found an SP-GiST index on just
hoursto be fastest for the query.
New test with Postgres 12 on an old laptop.
My script to generate dummy data:
INSERT INTO hoo(shop_id, hours) SELECT id , f_hoo_hours(((date '1996-01-01' + d) + interval '4h' + interval '15 min' * trunc(32 * random())) AT TIME ZONE 'UTC' , ((date '1996-01-01' + d) + interval '12h' + interval '15 min' * trunc(64 * random() * random())) AT TIME ZONE 'UTC') FROM generate_series(1, 30000) id JOIN generate_series(0, 6) d ON random() > .33;
Results in ~ 141k randomly generated rows, ~ 30k distinct
shop_id, ~ 12k distinct
hours. Table size 8 MB.
I dropped and recreated the exclusion constraint:
ALTER TABLE hoo DROP CONSTRAINT hoo_no_overlap , ADD CONSTRAINT hoo_no_overlap EXCLUDE USING gist (shop_id WITH =, hours WITH &&); -- 3.5 sec; index 8 MB ALTER TABLE hoo DROP CONSTRAINT hoo_no_overlap , ADD CONSTRAINT hoo_no_overlap EXCLUDE USING gist (hours WITH &&, shop_id WITH =); -- 13.6 sec; index 12 MB
shop_id first is ~ 4x faster for this distribution.
In addition, I tested two more for read performance:
CREATE INDEX hoo_hours_gist_idx on hoo USING gist (hours); CREATE INDEX hoo_hours_spgist_idx on hoo USING spgist (hours); -- !!
VACUUM FULL ANALYZE hoo;, I ran two queries:
- Q1: late night, finding only 35 rows
- Q2: in the afternoon, finding 4547 rows.
Got an index-only scan for each (except for “no index”, of course):
index idx size Q1 Q2 ------------------------------------------------ no index 38.5 ms 38.5 ms gist (shop_id, hours) 8MB 17.5 ms 18.4 ms gist (hours, shop_id) 12MB 0.6 ms 3.4 ms gist (hours) 11MB 0.3 ms 3.1 ms spgist (hours) 9MB 0.7 ms 1.8 ms -- !
- SP-GiST and GiST are on par for queries finding few results (GiST is even faster for very few).
- SP-GiST scales better with a growing number of results, and is smaller, too.
If you read a lot more than you write (typical use case), keep the exclusion constraint as suggested at the outset and create an additional SP-GiST index to optimize read performance.