Re: multiple UNIQUE indices for FK

On Fri, Mar 4, 2016 at 6:30 AM, Rafal Pietrak <rafal(at)ztk-rp(dot)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.

David J.