From: | Mark Rofail <markm(dot)rofail(at)gmail(dot)com> |
---|---|
To: | Joel Jacobson <joel(at)compiler(dot)org> |
Cc: | Zhihong Yu <zyu(at)yugabyte(dot)com>, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>, Alexander Korotkov <aekorotkov(at)gmail(dot)com>, Andreas Karlsson <andreas(at)proxel(dot)se>, David Steele <david(at)pgmasters(dot)net>, Erik Rijkers <er(at)xs4all(dot)nl>, Hans-Jürgen Schönig <hs(at)cybertec(dot)at>, Robert Haas <robertmhaas(at)gmail(dot)com>, Stephen Frost <sfrost(at)snowman(dot)net>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, Michael Paquier <michael(at)paquier(dot)xyz> |
Subject: | Re: [HACKERS] GSoC 2017: Foreign Key Arrays |
Date: | 2021-02-02 12:51:18 |
Message-ID: | CAJvoCut_Qw3dV342Lm5uo4gWw-J2MPKKiR8Ro4aWuh-0c+eCjA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
Attachment | Content-Type | Size |
---|---|---|
fk_array_perfomance_figure.png | image/png | 326.4 KB |
Array-containselem-gin-v3.patch | text/x-patch | 27.6 KB |
Array-ELEMENT-foreign-key-v17.patch | text/x-patch | 110.6 KB |
test_data.zip | application/zip | 4.0 MB |
From | Date | Subject | |
---|---|---|---|
Next Message | torikoshia | 2021-02-02 13:00:47 | Re: adding wait_start column to pg_locks |
Previous Message | Joel Jacobson | 2021-02-02 12:50:42 | Re: [PATCH] Doc: improve documentation of oid columns that can be zero. (correct version) |