W dniu 04.03.2016 o 19:33, David G. Johnston pisze: > On Fri, Mar 4, 2016 at 6:30 AM, Rafal Pietrak <rafal@xxxxxxxxx > <mailto:rafal@xxxxxxxxx>>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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
