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:
- Tune postgres so the index scan from the IN() subquery will be faster
- 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?
- 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!
product_userwould probably be the best way to re-write it. Unfortunately for you, re-writing the query sounds unlikely. The best outcome would come from convincing the 3rd party software to fix their crappy code though.
INlists as part of a predicate are a common problem for most RDBMS - it's not a PostgreSQL specific problem. They are the equivalent of a bunch of
ORclauses. It just complicates the query too much. I actually consider them an anti-pattern. I can't think of a single reason where your example query would be any different as a