Postgres Pro
Downloads
Home   >   mailing lists

Re: [HACKERS] GSoC 2017: Foreign Key Arrays - Mailing list pgsql-hackers

From Mark Rofail
Subject Re: [HACKERS] GSoC 2017: Foreign Key Arrays
Date
Msg-id CAJvoCut_Qw3dV342Lm5uo4gWw-J2MPKKiR8Ro4aWuh-0c+eCjA@mail.gmail.com
Whole thread Raw
In response to Re: [HACKERS] GSoC 2017: Foreign Key Arrays  ("Joel Jacobson" <joel@compiler.org>)
Responses Re: [HACKERS] GSoC 2017: Foreign Key Arrays  ("Joel Jacobson" <joel@compiler.org>)
List pgsql-hackers
Tree view
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
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
Attachment

pgsql-hackers by date:

Previous
From: "Joel Jacobson"
Date:
Subject: Re: [PATCH] Doc: improve documentation of oid columns that can be zero. (correct version)
Next
From: torikoshia
Date:
Subject: Re: adding wait_start column to pg_locks