Okay, so I'm trying to test the performance of Foreign Key Array for 3 scenarios: Original Patch, After My Modifications and After My Modifications with GIN index on the referencing column.
I have attached the sql test file here. It contains about 10k row insertions.
However, there is a bug that prevented me from testing the third scenario, I assume there's an issue of incompatible types problem since the right operand type is anyelement and the supporting procedures expect anyarray.
I am working on debugging it right now.
But if it comes to it, should I introduce a new opclass specifically for anyelement or add new supporting procedures to the old opclass ? .
I have also attached the results for the first 2 scenarios, however, the third scenario is the most important one since it's what the project is all about.
Also, this is kind of interesting. Upon reviewing the results
SELECT 1 FROM ONLY fktable x WHERE $1 @> fkcol FOR SHARE OF x;
produces worse results than the original
SELECT 1 FROM ONLY fktable x WHERE $1 = ANY (fkcol) FOR SHARE OF x;
so personally I don't think we should leave creating a GIN index up to the user, it should be automatically generated instead.