On Thu, Jul 5, 2018 at 2:36 PM, Rafal Pietrak <ra...@ztk-rp.eu> wrote:
> > > W dniu 05.07.2018 o 23:04, David G. Johnston pisze: > > On Thu, Jul 5, 2018 at 1:45 PM, Rafal Pietrak <ra...@ztk-rp.eu > > <mailto:ra...@ztk-rp.eu>>wrote: > > > > I was thinking, that when "add constraint" cannot choose appropriate > > index, may be some explicit help (like ... using <index_name>;) > would be > > due. > > > > > > Basically all the FK trigger does is: > > > > SELECT EXISTS(SELECT 1 FROM pk_table WHERE pk_col1 = val1 AND pk_col2 = > > val2) > > > > And fails if query returns false. The planner is still free to use the > > index or not to execute the query just as if you had written it by > > hand. For a small table fully in memory it likely would prefer a > > sequential scan and it would be perfectly within its rights to do so. > > > > I must assume, that the above "WHERE pk_col1 - val1..." is not the same > place as the one you've mentioned earlier, where "FK don't have where > clausures". > The FK definition doesn't have a WHERE clause so the only (and all) columns used in the trigger are those defined by the constraint itself. So, if a partial index for the above was: UNIQUE INDEX (pk_col1, pk_col2) WHERE pktblcol3 = false; and FK (col1, col2) REFERENCES pk_table (pk_col1, pk_col2) There is no place on the FK to reference "pktblcol3" so that the effective trigger query would become: WHERE pk_col1 = val2 AND pk_col2 = val2 AND pktblcol3 = ??? And without pktblcol3 more than one row could be returned (so, not really EXISTS...) > > Thus, the bulk of code that maintains FK consistency "does not use where > clausures", would break on partial indexes. But in the above SELECT, > with partial indexed, that select will never fail. So it is not the > blocker... on the other hand, I would rather rewrite it along the lines of: > SELECT 1 = (SELECT count(1) FROM pk_table WHERE pk_col1 = val1 AND > pk_col2 = val2) > Yeah, that's closer to reality > > Naturally, if the planner choses to do a seq scan, everything would > break down - inappropriate rows would get hit; but that's a different > story: > 1. one may think, doing a "SELECT * FROM pk_table WHERE... INTO current" > and making all constraint trigger functions use that "current" instead > of making more lookups, could solve the problem. > 2. or with some syntax help during FK creation (aka: ... add constraint > ... using <index_name>) one could force the planner to always use > indicated index. > This comes back to constraints don't directly target indexes even though indexes are used in implementation (it would be violation of scope). They target other constraints at a logical level. Whether the code is written exactly like that without evidence to the contrary it provides a sound mental model to operate from. I think we might get away from this for INSERT ON CONFLICT but I'm not that well versed nor have time to look into it right now. David J.
