On Mon, Dec 2, 2013 at 6:08 PM, Tom Dunstan <pg...@tomd.cc> wrote: > On 3 Dec 2013, at 03:37, Robert Haas <robertmh...@gmail.com> wrote: >> I also like this feature. It would be really neat if a FOREIGN KEY >> constraint with a WHERE clause could use a *partial* index on the >> foreign table provided that the index would be guaranteed to be predOK >> for all versions of the foreign key checking query. That might be >> hard to implement, though. > > Well, with this patch, under the hood the FK query is doing (in the case of > RESTRICT): > > SELECT 1 FROM ONLY "public"."comment" x WHERE (the id) OPERATOR(pg_catalog.=) > "parent_id" AND (parent_entity = 'event') FOR KEY SHARE OF x; > > If we stick a partial index on the column, disable seq scans and run the > query, we get: > > tom=# create index comment_event_id on comment (parent_id) where > parent_entity = 'event'; > CREATE INDEX > tom=# set enable_seqscan = off; > SET > tom=# explain SELECT 1 FROM ONLY "public"."comment" x WHERE 20 > OPERATOR(pg_catalog.=) "parent_id" AND (parent_entity = 'event') FOR KEY > SHARE OF x; > QUERY PLAN > ---------------------------------------------------------------------------------------- > LockRows (cost=0.12..8.15 rows=1 width=6) > -> Index Scan using comment_event_id on comment x (cost=0.12..8.14 > rows=1 width=6) > Index Cond: (20 = parent_id) > Filter: (parent_entity = 'event'::commentable_entity) > (4 rows) > > Is that what you had in mind?
Yeah, more or less, but the key is ensuring that it wouldn't let you create the constraint in the first place if the partial index specified *didn't* match the WHERE clause. For example, suppose the partial index says WHERE parent_entity = 'event' but the constraint definition is WHERE parent_event = 'somethingelse'. That ought to fail, just as creating a regular foreign constraint will fail if there's no matching unique index. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
