PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – IN works slow without explicit data

This query:

select id
from user u
where u.account_id = 600

returns:

61,71,68,69,70,118,116,117,248,381,384,325,265,393

and it works really fast. This query:

select *
from logs l
where l.user_id in 
(
    61,71,68,69,70,118,116,117,248,381,384,325,265,393
)

works really fast too. But why is this query:

select *
from logs l
where l.user_id in 
(
    select id
    from user u
    where u.account_id = 600
)

works very slow (Im getting timeout). Why putting explicit data is solving the problem?

Tags:

2

Answers


  1. 0

    You should run the query like this and make sure that l.user_id is indexed.

    select l.*
from logs l, user u 
where l.user_id = u.id
and u.account_id = 600
    Login or Signup to reply.
  2. 0

    In the first case, PostgreSQL knows what the values will be when it plans the query, so it can get a good estimate for the number of result rows.

    In the second case, PostgreSQL doesn’t have this information when the query is planned, so it uses an estimate. That estimate seems to be off, do that PostgreSQL chooses a bad query plan.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search