Re: multiple UNIQUE indices for FK - Mailing list pgsql-general
| From | David G. Johnston |
|---|---|
| Subject | Re: multiple UNIQUE indices for FK |
| Date | |
| Msg-id | CAKFQuwbADVdSGVaGzh8W4ebN9hm1rmLWqcjRp5cWZFXE=Q5xWg@mail.gmail.com Whole thread Raw |
| In response to | Re: multiple UNIQUE indices for FK (Rafal Pietrak <rafal@ztk-rp.eu>) |
| Responses |
Re: multiple UNIQUE indices for FK
(Rafal Pietrak <rafal@ztk-rp.eu>)
|
| List | pgsql-general |
>
> 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.
pgsql-general by date: