Re: GSoC 2017: Foreign Key Arrays

On Sun, Jul 9, 2017 at 1:11 PM, Mark Rofail <markm(dot)rofail(at)gmail(dot)com> wrote:

> On Sun, Jul 9, 2017 at 2:38 AM, Alexander Korotkov <aekorotkov(at)gmail(dot)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.

1.
https://www.postgresql.org/docs/current/static/ddl-constraints.html#DDL-CONSTRAINTS-FK

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company