Re: [HACKERS] GSoC 2017: Foreign Key Arrays

Greetings,

I have performed the performance comparison.
The purpose of the test was to compare FK Arrays' performance compared to
standard FKs.

The results are shown in the figure below
[image: fk_array_perfomance_figure.png]
The Tests were carried as follow

1. create a referenced table of 1k rows
2. create an SQL file for each case
1. referencing table with array FKs
2. referencing table with array FKs with ELEMENT operator <<@
3. referencing table with array FKs and GIN index
4. referencing table with array FKs with ELEMENT operator <<@ and GIN
index
5. referencing table with standard FKs through many2many table
3. each case contains INSERT statements referencing the referenced table
4. after which I trigger an UPDATE and DELETE statements on the
referenced table
1. UPDATE: "UPDATE pktableforarray SET ptest1 = 1001 WHERE
pktableforarray.ptest1 = 1000;"
2. DELETE: "DELETE FROM PKTABLEFORARRAY WHERE pktableforarray.ptest1
= 999;"
5. I repeat this for 10^1, 10^2, 10^3, 10^4, 10^5, and 10^6 rows.

The test data used is attached below (except the 10^6, file size too big)

Changelog (FK arrays):
- v17 (compatible with current master 2021-02-02,
commit 1d71f3c83c113849fe3aa60cb2d2c12729485e97)
* refactor "generate_operator_clause"

Changelog (FK arrays Elem addon)
- v3 (compatible with FK arrays v17)
* fix bug in "ginqueryarrayextract"

I will post this patch to the commit fest.

/Mark