Re: multiple UNIQUE indices for FK

On Fri, Mar 4, 2016 at 4:17 PM, Rafal Pietrak <rafal(at)ztk-rp(dot)eu> wrote:

> >
> > 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
>
> It wouldn't.
>
> 3. FK is defined with partially-unique indes. This is new and ... would
> it create ambiquity between queries. No. I don't think so (provided that
> FK/partial-index are used consistently).
>
>
​My knowledge in this area is somewhat limited but that is very large
"provided that"​

> The only "ambiquity" arises when one allows for "unindexed" FK, while
> subsequent changes to schema add partially-unique index at target
> columns. But this wouldn't happen in postgresql ... and who cares about
> Oracle :7 And even then. such index may fail to get created of currently
> present FK have records pointing outside that newly created index, Once
> index get created, queries become consistent again. just like creating
> full unique index may fail, and when data is corrected and index get
> created - the queries become consistent (with it).
>
> > 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.
>
> I'd say that if there is an implementation requirement for FK target
> column set to be covered by unique index, then executor should never
> ignore it in favour of any other search plan. If it does, it's a bug.
>

Since the current requirement is that the index and the sequential scan for
a FK lookup would return the same data the most efficient plan is chosen.
Lacking a where clause a join between two tables with a defined PK-FK
relationship can be more quickly fulfilled by simply scanning both tables
completely and then merging them together once the scanned data has been
sorted on the keyed columns. While the index is already sorted the extra
hits to the heap to check visibility are likely going to make working with
the index less performant.​ There is no bug in this case because the exact
same results are returned in either scenario.

I've made my point and am not fluent enough to discuss the issues that
would need to be addressed to implement a FK-to-a-partial-unique-index
feature.

I recall having hit this limitation myself previously so I too wouldn't
mind seeing it implemented - but doing so without degrading the performance
of all FK-related queries is important too - and the increase in risk of
having bugs in the short term is quite high given that the fundamental
operation of a key module needs to be changed. Its not something whose
cost-benefit analysis reports favorably.

David J.