Thread: [PERFORM] Dissuade the use of exclusion constraint index

[PERFORM] Dissuade the use of exclusion constraint index

From
Adam Brusselback
Date:
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.


Re: [PERFORM] Dissuade the use of exclusion constraint index

From
Adam Brusselback
Date:
Just wondering if anyone has any thoughts on what I can do to alleviate this issue?

I'll kinda at a loss as to what to try to tweak for this.

Re: [PERFORM] Dissuade the use of exclusion constraint index

From
Dave Cramer
Date:
Adam,

I think the first thing to do is to make hackers aware of the specifics of which indexes are being used etc so that the planner could be taught how to use better ones.

Self contained examples do wonders


On 11 April 2018 at 01:59, Adam Brusselback <adambrusselback@gmail.com> wrote:
Just wondering if anyone has any thoughts on what I can do to alleviate this issue?

I'll kinda at a loss as to what to try to tweak for this.

Re: [PERFORM] Dissuade the use of exclusion constraint index

From
Adam Brusselback
Date:
> Self contained examples do wonders
Good point, will work on that and post once I have something usable.


Re: [PERFORM] Dissuade the use of exclusion constraint index

From
Adam Brusselback
Date:
Alright, the first two attempts to reply to this thread I don't believe worked, likely due to the attachment size. Hoping this time it does...

> > Self contained examples do wonders
> Good point, will work on that and post once I have something usable.

Finally got around to making a self contained example... busy few months.

Attached is a pg_dump file which will create a schema called test, and
load up some real-world data for the specified tables. Extract it,
then load.
> psql -f excl_test.sql

Then you can run the following which should hit the condition outlined above:

ANALYZE test.claim;
ANALYZE test.claim_amounts;
ANALYZE test.deduction;
ANALYZE test.deduction_claim;

SELECT *
FROM test.claim
INNER JOIN test.claim_amounts
ON claim.claim_id = claim_amounts.claim_id
LEFT JOIN test.deduction_claim
ON deduction_claim.claim_id = claim.claim_id
AND upper_inf(deduction_claim.active_range)
WHERE claim.claim_id = ANY (
'{79d037ea-4c56-419b-92c4-c2fd6dab9a28
,d3d5d2ef-fb23-451a-bd06-9a976600492e
,dff9bbf9-0816-46b0-baac-f3875ddf6624
,1ac5dc75-3cce-448a-8e37-ba1f5c2f271a
,b7b6af7e-22d2-412c-b56e-f2a589da63de
,fa29d4c9-d820-4852-a39b-5e5a822d6fe5
,9d8ae491-c4a2-44ce-bf1e-0edad8456b5a
,1796635d-1b87-4315-b6eb-d45eec7dfa98
,d7e8a26a-a00a-4216-ae53-15fba2045adb
,391f0bb7-853a-47b4-b4aa-bc9094a2a0b9}'::uuid[]
);


Here is the schema / data for the test case: https://drive.google.com/open?id=1LcEv56GkH19AgEfhRB85SCPnou43jYur

Re: [PERFORM] Dissuade the use of exclusion constraint index

From
Adam Brusselback
Date:
Hello all,
Just wondering if there is anything else I can provide to help figure this out.

One thing I did notice, is there is a discussion about "invisible indexes" going on, which seems that if it was implemented, would be one way to "fix" my problem: https://www.postgresql.org/message-id/flat/ed8c9ed7-bb5d-aaec-065b-ad4893645deb%402ndQuadrant.com#ed8c9ed7-bb5d-aaec-065b-ad4893645deb@2ndQuadrant.com

The down side to that approach, is even when that index really is the best option for a query, it cannot utilize it.

Let me know if I can provide anything else.
-Adam