Thread

[PERFORM] Dissuade the use of exclusion constraint index

Adam Brusselback <adambrusselback@gmail.com> — 2018-04-06T16:30:52Z

Hey all,
I'm using Postgres 10.3
6 core VM with 16gb of ram

My database schema requires a good bit of temporal data stored in a
few my tables, and I make use of ranges and exclusion constraints to
keep my data consistent.

I have quite a few queries in my DB which are using a very sub-optimal
index choice compared to others available.  I am just looking for ways
to tune things to make it less likely to use the backing index for an
exclusion constraint for queries where better indexes are available.

Here is an example of a query which exhibits this behavior:
SELECT *
FROM claim
INNER JOIN claim_amounts
ON claim.claim_id = claim_amounts.claim_id
LEFT JOIN deduction_claim
ON deduction_claim.claim_id = claim.claim_id
AND upper_inf(deduction_claim.active_range)
WHERE claim.claim_id = ANY ('{uuids_go_here}'::uuid[]);

Here is the plan which is always chosen: https://explain.depesz.com/s/rCjO

I then dropped the exclusion constraint temporarily to test, and this
was the plan chosen after: https://explain.depesz.com/s/xSm0

The table definition is:
CREATE TABLE deduction_claim
(
  deduction_id uuid NOT NULL,
  claim_id uuid NOT NULL,
  deduction_amount_allotted numeric NOT NULL,
  active_range tstzrange NOT NULL DEFAULT tstzrange(now(),
NULL::timestamp with time zone),
  inoperative boolean DEFAULT false,
  deduction_claim_id uuid NOT NULL DEFAULT gen_random_uuid(),
  CONSTRAINT deduction_claim_pkey PRIMARY KEY (deduction_claim_id),
  CONSTRAINT deduction_claim_claim_id_fkey FOREIGN KEY (claim_id)
      REFERENCES claim (claim_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT deduction_claim_deduction_id_fkey FOREIGN KEY (deduction_id)
      REFERENCES deduction (deduction_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT deduction_claim_active_range_excl EXCLUDE
  USING gist (deduction_id WITH =, claim_id WITH =, active_range WITH &&),
  CONSTRAINT deduction_claim_ar_empty_check CHECK (active_range <>
'empty'::tstzrange)
);

-- Index: idx_deduction_claim_claim_id

-- DROP INDEX idx_deduction_claim_claim_id;

CREATE INDEX idx_deduction_claim_claim_id
  ON deduction_claim
  USING btree
  (claim_id)
  WHERE upper_inf(active_range);

-- Index: idx_deduction_claim_deduction_id

-- DROP INDEX idx_deduction_claim_deduction_id;

CREATE INDEX idx_deduction_claim_deduction_id
  ON deduction_claim
  USING btree
  (deduction_id)
  WHERE upper_inf(active_range);

If there is any more info I can provide, please let me know.

Thanks in advance for any advice you can give.