út 19. 1. 2021 v 3:08 odesílatel Amit Langote <amitlangot...@gmail.com> napsal:
> On Tue, Jan 19, 2021 at 3:01 AM Pavel Stehule <pavel.steh...@gmail.com> > wrote: > > po 18. 1. 2021 v 13:40 odesílatel Amit Langote <amitlangot...@gmail.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 >
