Postgresql – Include constraint for daterange type

exclusion-constraintpostgresql

Here is the table

CREATE TABLE date_ranges (
  id SERIAL PRIMARY KEY,
  user_id INTEGER,
  start_at DATE,
  end_at DATE,
  is_exception BOOLEAN
);

ALTER TABLE date_ranges ADD CONSTRAINT date_ranges_bounds
EXCLUDE USING gist (user_id WITH =, daterange(start_at, end_at, '[]') WITH &&)
WHERE (NOT is_exception);

I have a table with working ranges and here is a flag is_exception which indicates that this range is an exception from the regular range and I want to make a constraint that all regular ranges do not overlap each other and exception ranges belong to the regular range.

I tried to use <@ operator but have found that it is not commutative.

ALTER TABLE date_ranges ADD CONSTRAINT date_ranges_bounds
EXCLUDE USING gist (user_id WITH =, daterange(start_at, end_at, '[]') WITH <@)
WHERE (is_exception);

Is there any option to do this? Shall I create custom operator for this case?

UPDATE

Test cases:

user_id     start_at        end_at      is_exception    is_day_off
1           2019-03-01      2019-03-05  FALSE           FALSE       // is fine
1           2019-03-01      2019-03-05  FALSE           FALSE       // FAIL. Range intersects with range above
1           2019-03-02      2019-03-03  TRUE            FALSE       // is fine since exception is inside "regular range above"
1           2019-03-04      2019-03-04  TRUE            FALSE       // is fine since exception is inside "regular range above" & does not intersect another exception
1           2019-03-03      2019-03-03  FALSE           TRUE        // is fine since day off is inside "regular range above"
1           2019-03-05      2019-03-05  FALSE           TRUE        // is fine since day off is inside "regular range above" & does not intersect another day off

1           2019-03-02      2019-03-03  TRUE            FALSE       // FAIL. Exception should NOT intersect with another one
1           2019-03-03      2019-03-03  FALSE           TRUE        // FAIL. Day off should NOT intersect with another one

1           2019-03-15      2019-03-15  TRUE            FALSE       // FAIL. Exception is NOT inside "regular range above"
1           2019-03-10      2019-03-10  FALSE           TRUE        // FAIL. Day off is NOT inside "regular range above"

Best Answer

The (not overlapping) constraint between "regular" ranges can be enforced with an EXCLUDE constraint, as you it defined in the question.

The (not overlapping) constraint between "exception" ranges can be enforced in the same manner and so can the constraint between "day off" ranges.

What is not trivial is the constraint between "regular" and "exception" ranges (and between "regular" and "day off" ranges):

For an exception there should be a regular range that the exception range is contained inside the regular one.

This is not commutative, so it cannot be done with EXCLUDE constraints. What you really need is a FOREIGN KEY constraint, a slightly more complicated one than a common foreign key constraint.

As an example, a foreign key constraint defined n table B:

CONSTRAINT a_b_fk
    FOREIGN KEY (aid)
    REFERENCES a (aid)

enforces that:

for any aid value in table B, there exists a row in table A with equal value in column aid

which is quite similar to what you have. The problematic difference is that foreign key constraints can use only the equality (=) and not any other operator. To solve this problem, we'll have to duplicate the columns that need to be checked with the different operator ("is contained by", <@).

As a result, we'll need to split the table into two, "regular" and "exception" ranges (lets forget the "day off" for a bit) and apply our solution:

For the "regular" ranges table, we'll only need an additional UNIQUE constraint, for the foreign key to be defined (and we can skip the is_exception and is_day_off columns):

CREATE TABLE date_regular_ranges (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,
  start_at DATE NOT NULL,
  end_at DATE NOT NULL,
  CONSTRAINT date_regular_ranges_uq
    UNIQUE (user_id, start_at, end_at),
  CONSTRAINT date_regular_ranges_bounds
    EXCLUDE USING gist
      ( user_id WITH =, 
        daterange(start_at, end_at, '[]') WITH &&
      )
);

For the "exception" ranges table, we'll need the FOREIGN KEY constraint and a CHECK constraint for the `contained" operator:

CREATE TABLE date_exception_ranges (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,
  start_at DATE NOT NULL,
  end_at DATE NOT NULL,
  regular_start_at DATE NOT NULL,
  regular_end_at DATE NOT NULL,

  -- we don't need the UNIQUE, as the EXCLUDE covers it
  -- CONSTRAINT date_exception_ranges_uq
  --   UNIQUE (user_id, start_at, end_at),

  CONSTRAINT date_exception_ranges_bounds
    EXCLUDE USING gist
      ( user_id WITH =, 
        daterange(start_at, end_at, '[]') WITH &&
      ),

  CONSTRAINT date_regular_exception_ranges_fk
    FOREIGN KEY
      (user_id, regular_start_at, regular_end_at)
    REFERENCES date_regular_ranges
      (user_id, start_at, end_at),

  CONSTRAINT date_exception_ranges_ck
    CHECK ( daterange(start_at, end_at, '[]')
         <@ daterange(regular_start_at, regular_end_at, '[]')
          )
);

For the "day off", since we identified that are similar to "exception" ranges, we can either add another table (with identical structure as the date_exception_ranges) or keep only that and add a boolean column to identify whether it is an "exception" or a "day off".

(The following assume that "exceptions" can overlap "day off" ranges. If they cannot, it's actually much simpler):

CREATE TABLE date_exception_ranges (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,
  start_at DATE NOT NULL,
  end_at DATE NOT NULL,
  regular_start_at DATE NOT NULL,
  regular_end_at DATE NOT NULL,
  -- TRUE: is_exception, FALSE: is_day_off
  is_exception BOOLEAN NOT NULL,

  CONSTRAINT date_exception_ranges_bounds
    EXCLUDE USING gist
      ( user_id WITH =,
        (is_exception::int) WITH =,
        daterange(start_at, end_at, '[]') WITH &&
      ),

  CONSTRAINT date_regular_exception_ranges_fk
    FOREIGN KEY
      (user_id, regular_start_at, regular_end_at)
    REFERENCES date_regular_ranges
      (user_id, start_at, end_at),

  CONSTRAINT date_exception_ranges_ck
    CHECK ( daterange(start_at, end_at, '[]')
         <@ daterange(regular_start_at, regular_end_at, '[]')
          )
);

And finally, the above tested at dbfiddle.uk

Alternative with COLLATE "C"

Instead of creating two indexes, there is another alternative for xxx_pattern_ops indexes that may be preferable. The documentation:

The difference from the default operator classes is that the values are compared strictly character by character rather than according to the locale-specific collation rules. This makes these operator classes suitable for use by queries involving pattern matching expressions (LIKE or POSIX regular expressions) when the database does not use the standard "C" locale.

And:

The index automatically uses the collation of the underlying column.

You can create the column without collation (using COLLATE "C"). Then the default operator class behaves the same way as text_pattern_ops would - plus the index can be used with all the standard operators.

CREATE TABLE book2 (
   book_id serial PRIMARY KEY,
   book    text NOT NULL COLLATE "C" UNIQUE  -- that's all!
);

Now, LIKE can use the index:

SELECT * FROM book2 WHERE book LIKE 'foo%';

But ILIKE still can't:

SELECT * FROM book2 WHERE book ILIKE 'foo%';

SQL Fiddle.

Consider a trigram index using the additional module pg_trgm for a more general solution.:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Postgresql – Optimizing queries on a range of timestamps (one column)

Two things that are very odd here:

The query select 300k rows from a table with 1M+ rows. For 30 % (or anything over 5 % - depends on row size and other factors) it doesn't typically pay to use an index at all. We should see a sequential scan.

The exception would be index-only scans, which I don't see here. The multicolumn index @Craig suggested would be the best option if you get index-only scans out of it. With lots of updates like you mentioned, this may not work out, in which case you are better off without the additional columns - and just the index you already have. You might be able to make it work for you with more aggressive autovacuum settings for the table. You can adjust parameters for individual tables.
While Postgres is going to use the index I would certainly expect to see a bitmap index scan for that many rows, not a plain index scan, which is typically the better choice for a low percentage of rows. As soon as Postgres expects multiple hits per data page (judging from its statistics on the table) it will typically switch to a bitmap index scan.
- Keep PostgreSQL from sometimes choosing a bad query plan

Judging from that I would suspect that your cost settings are inadequate (and possibly the table statistics, too). You may have set random_page_cost and / or cpu_index_tuple_cost too low, relative to seq_page_cost. Follow the links and read the manual.

Would also fit with the observation that cold cache is large factor, as we worked out in comments. Either you are accessing (parts of) tables that nobody has touched in a long time or you are running on a test system where the cache isn't populated (yet)?
Else you just don't have enough RAM available to cache most of the relevant data in your DB. Consequently, random access is much more expensive than sequential access when data resides in cache. Depending on the actual situation you may have to adjust to get better query plans.

One other factor has to be mentioned for slow response on the first read only: Hint bits. Read details in the Postgres Wiki and this related question:

Why does a SELECT statement dirty cache buffers in Postgres?

Or the table is extremely bloated, in which case an index scan would make sense and I would refer back to CLUSTER / pg_repack in my previous answer that you quoted. (Or just VACUUM FULL) And investigate your VACUUM settings. Those are important with many inserts and updates every day.

Depending on UPDATE patterns also consider a FILLFACTOR below 100. If you mostly update only newly added rows, set the lower FILLFACTER after compacting your table, so that only new pages keep some wiggle room for updates.

Schema

campaign_id is 99%+ NULL and dt_updated is 0% NULL.

Adjust the sequence of columns slightly, to save 8 bytes per row (in the 99% of cases where campaign_id is NULL):

CREATE TABLE traffic (
    uuid_self       uuid not null REFERENCES ... ,
    uuid_partner    uuid not null REFERENCES ... ,
    id              serial PRIMARY KEY,
    impressions     integer NOT NULL DEFAULT 1,
    clicks          integer NOT NULL DEFAULT 0,
    campaign_id     integer,
    dt_created      DATE DEFAULT CURRENT_DATE NOT NULL,
    dt_updated      TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
);

Detailed explanation and links to more:

Configuring PostgreSQL for read performance

To measure:

Measure the size of a PostgreSQL table row

Best Answer

Related Solutions

PostgreSQL – Unique Index vs Unique Constraint with Operator Class

Alternative with COLLATE "C"

Postgresql – Optimizing queries on a range of timestamps (one column)

Schema

Related Question