"In general, these operator classes will not outperform the equivalent standard B-tree index methods, and they lack one major feature of the standard B-tree code: the ability to enforce uniqueness."
index which truly ought to be its primary key. as_of_date is of range date type:
EXCLUDE USING gist (id WITH =, as_of_date WITH &&)
Any direction here would be much appreciated.
Right now, I am forced to create a redundant btree index UNIQUE, btree (id, lower(as_of_date)) in order to have a primary key on the table.
Thank you for the ref. But I don't understand how an exclusion constraint does not have "the ability to enforce uniqueness" unless they just mean that is the case "under the covers of postgres". That is exactly what it does, right? By the definition of the exclusion index I have above, there cannot be more than one row with the same id and as_of_date values.