W dniu 05.07.2018 o 23:04, David G. Johnston pisze:
> On Thu, Jul 5, 2018 at 1:45 PM, Rafal Pietrak <rafal@xxxxxxxxx
> <mailto:rafal@xxxxxxxxx>>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.
