On Fri, Mar 4, 2016 at 6:30 AM, Rafal Pietrak <rafal@ztk-rp.eu> wrote:
The problem is that once one accepts the requirement for a unique index as FK target column "mandatory performance support", then I fail to see real reazon, where *ENY* unique index shouldn't do that role too. They are unique (within domains of their conditions) and by definition yield a single row for FK (or nothing); that should be sufficient for the engine to keep data consistancy as expected, shouldn't it?
A foreign key doesn't get to use a WHERE clause so the planner has no ability to know just by looking at a query that the partial unique index should be used.
In other words the presence of absence of an FK constraint between two tables should not alter the results of any question. But since a partial unique constraint could result in the full table having duplicates on the constrained columns when ignoring the partial's WHERE clause this would not be true.
For the example data you could construct a partial unique index [(a,b) WHERE c = true]
(a,b,c)
(1,1,true),
(1,1,false),
(1,2,true)
This Query:
SELECT a, b, c
FROM src
JOIN abc USING (a,b)
Would return 1 row if the FK restricted the executor to only looking at rows in the partial index but would return 2 rows if it considers (say, because of using a sequential scan) the table as a whole.
This seems simply like an implementation artifact. INDEX is used only upon data entry and for performance gains and never in order to ensure correctness.
I'm wandering into novel territory (for me) in my explanation above but it seems to cover the concept well even if I'm imprecise in some areas.