On Sun, Jul 9, 2017 at 2:38 AM, Alexander Korotkov <aekorotkov@gmail.com> wrote:
Could you, please, specify idea of what you're implementing in more detail?
Ultimatley we would like an indexed scan instead of a sequential scan, so I thought we needed to index the FK array columns first.
Indeed, this is right.
But look how that works for regular FK. When you declare a FK, you necessary need unique index on referenced column(s). However, index on referencing columns(s) is not required. Without index on referencing column(s), row delete in referenced table and update of referenced column are expensive because requires sequential scan of referencing table. Users are encouraged to index referencing column(s) to accelerate queries produced by RI triggers. [1]
According to this, it's unclear why array FKs should behave differently. We may document that GIN index is required to accelerate RI queries for array FKs. And users are encouraged to manually define them.
It's also possible to define new option when index on referencing column(s) would be created automatically. But I think this option should work the same way for regular FKs and array FKs.
