btree gist indices, null and open-ended tsranges

From: Chris Withers <chris(at)simplistix(dot)co(dot)uk>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: btree gist indices, null and open-ended tsranges
Date: 2016-12-01 11:56:57
Message-ID: 18847817-c439-aacb-cb97-549eef1d1377@simplistix.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi All,

Working with the exclude constraint example from
https://www.postgresql.org/docs/current/static/rangetypes.html:

CREATE EXTENSION btree_gist;
CREATE TABLE room_reservation (
room text,
during tsrange,
EXCLUDE USING GIST (room WITH =, during WITH &&)
);

So, first observation: if I make room nullable, the exclude constraint
does not apply for rows that have a room of null. I guess that's to be
expected, right?

Next question: if lots of rows have open-ended periods
(eg: [, 2010-01-01 15:00) or [2010-01-01 14:00,)), how does that affect
the performance of the btree gist index backing the exclude constraint?

cheers,

Chris

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Francisco Olarte 2016-12-01 12:07:09 Re: Overwrite pg_catalog?
Previous Message Juliano 2016-12-01 11:16:10 Overwrite pg_catalog?