Thread

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

Adam Brusselback <adambrusselback@gmail.com> — 2018-06-13T15:59:48Z

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