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!

  • Please consider reading this advice
    – mustaccio
    May 23, 2023 at 11:44
  • Thanks, added more info and full query + explain info
    – Marcel
    May 23, 2023 at 11:59
  • 1
    FWIW, a simple JOIN between product and product_user would 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.
    – J.D.
    May 23, 2023 at 12:10
  • Yeah, that's going to be a problem :) There is actually some login behind why the query is this way, but in certain scenarios it's just super inefficient. Altough I also don't get why Postgres is this inefficient in chosing the best plan. As I turn off "enable_nestloop" for example the query is way faster.
    – Marcel
    May 23, 2023 at 12:53
  • @Marcel Long IN lists 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 OR clauses. 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 JOIN though, btw.
    – J.D.
    May 23, 2023 at 13:30


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.