Super slow index scan from IN subquery (generated by third party software). Tuning postgres or rewriting query?

Question

I have a query which has a huge bottleneck. Doing explain analyze I found out that an index scan is causing the problem.

The query is:

select * from product where (product.id = IN(select pu.id from product_user pu where pu.userid = 123)) limit 40

So the mytable.id = IN() causes an index scan which is really slow. After some searching on the internet I found that rewriting the query like this..

select * from product where (product.id = ANY((SELECT ARRAY(select pu.id from product_user pu where pu.userid = 123)::bigint[]))) limit 40

..performs way better, like 70% faster. Please note that the subquery (standalone) is super fast, but does return like 300k records.

Also, adding a manual btree index on the primary key (ID) column of the product table does increase performance a bit as well. Not sure why this is faster than the default primary key index.

Here's the full original query + query plan: https://explain.depesz.com/s/UW1w > Here's the same a bit more readable with a few strings translated: https://explain.depesz.com/s/Ry99#query

Here's the one with ANY(SELECT ARRAY.. instead of IN: https://explain.depesz.com/s/DS33

The query is generated by third party software. I have almost no influence on this query. Is there any way to either:

1. Tune postgres so the index scan from the IN() subquery will be faster
2. Rewrite a query right before execution on database level. We host the postgres database in the Google Cloud (managed databases), some kind of interceptor, does it exist?

Additional info:

• Postgres 13
• Total size of mytable: 9.000.000 rows
• Total size of subquery: +-300.000 rows

Any tips and suggestions are very welcome! Thanks!