Re: simplifying foreign key/RI checks

út 19. 1. 2021 v 3:08 odesílatel Amit Langote <amitlangote09(at)gmail(dot)com>
napsal:

> On Tue, Jan 19, 2021 at 3:01 AM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> > po 18. 1. 2021 v 13:40 odesílatel Amit Langote <amitlangote09(at)gmail(dot)com>
> napsal:
> >> I started with the check that's performed when inserting into or
> >> updating the referencing table to confirm that the new row points to a
> >> valid row in the referenced relation. The corresponding SQL is this:
> >>
> >> SELECT 1 FROM pk_rel x WHERE x.pkey = $1 FOR KEY SHARE OF x
> >>
> >> $1 is the value of the foreign key of the new row. If the query
> >> returns a row, all good. Thanks to SPI, or its use of plan caching,
> >> the query is re-planned only a handful of times before making a
> >> generic plan that is then saved and reused, which looks like this:
> >>
> >> QUERY PLAN
> >> --------------------------------------
> >> LockRows
> >> -> Index Scan using pk_pkey on pk x
> >> Index Cond: (a = $1)
> >> (3 rows)
> >
> >
> > What is performance when the referenced table is small? - a lot of
> codebooks are small between 1000 to 10K rows.
>
> I see the same ~2x improvement.
>
> create table p (a numeric primary key);
> insert into p select generate_series(1, 1000);
> create table f (a bigint references p);
>
> Unpatched:
>
> insert into f select i%1000+1 from generate_series(1, 1000000) i;
> INSERT 0 1000000
> Time: 5461.377 ms (00:05.461)
>
>
> Patched:
>
> insert into f select i%1000+1 from generate_series(1, 1000000) i;
> INSERT 0 1000000
> Time: 2357.440 ms (00:02.357)
>
> That's expected because the overhead of using SPI to check the PK
> table, which the patch gets rid of, is the same no matter the size of
> the index to be scanned.
>

It looks very well.

Regards

Pavel

> --
> Amit Langote
> EDB: http://www.enterprisedb.com
>