Re: multiple UNIQUE indices for FK - Mailing list pgsql-general

W dniu 04.03.2016 o 19:33, David G. Johnston pisze:
> On Fri, Mar 4, 2016 at 6:30 AM, Rafal Pietrak <rafal@ztk-rp.eu
> <mailto: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.

Hmm. IMHO quite the contrary. as FK does not have WHERE declaration, the
planner seeing relevant index (e.i index covering the columns of
interest) should use it irrespectively. And whatever index hits,
data/row is hit; whatever isn't (hit through such partial index), target
data/row is just missed.

And if documented, such behavior becomes feature. I personally would be
quite happy with such feature.


>
> 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.

There are three cases:
1. FK is defined without unique index (I think Oracle allows for that),
so every IPDATE/INSERT need a full scan of the target ... but as you've
said: for small tables that might be OK.
2. FK is defined with unique index over target column - posgresql
requires that. such unique index guarantees a single target row for FK
to point to.
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).

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.

>
> 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.

But I understand that there may be more implementation details then my
unacquainted eye can see.

Thenx for the info,

-R