There is a lot of rows in table "logs" (the Request A FROM).

Request A is quick and Request B is quick.

Put together, Request A WHERE IN Request B is very long. Is that normal?

Request A is very quick:

--request A
SELECT "rid", max("createdAt") as "createdAt"
FROM "logs"
WHERE "rid" IN (17,71,196,187,111,86,108,81,54,184,245,27,118,100,175,136,130,67,45)
GROUP BY "rid";

Request B is very quick:

--request B
SELECT "dr"."rid"
  FROM (
    SELECT "eid", "tid", count(*) over (partition by "eid", "tid") count, id as "rid"
    FROM rs
    WHERE "deletedAt" is NULL
  ) "nr"
WHERE count > 1
) "dr"
INNER JOIN teams ON teams.id = "dr"."tid"
INNER JOIN projects ON projects.id = teams."pid"
ORDER BY "eid", "tid"

The result of B is the same list of numbers than in request A WHERE IN.

Replacing request A WHERE IN by request B, it becomes very slow.

Postgres: 9.5.6

Query plan for Request A: enter image description here

Query plan or Request B: enter image description here

Query plan for Requast A WHEREIN B: enter image description here


Put together, Request A WHERE IN Request B is very long. Is that normal?

Yes, that's fairly common when elaborating queries. In the first case, the IN(...) clause has only a handful of constants. The planner knows exactly how many values and how frequent they are in the table, so it has a good chance to find an optimal execution plan (in this case, an index scan).

When the inside of this IN (...) clause is replaced by a subquery, the planner may mis-estimate the number of values that this subquery will produce, or their frequency, or both, especially if the subquery is complex, and so it may choose a non-optimal plan compared to when there is a list of constants inside IN (...).

| improve this answer | |
  • Ok, I was surprised because request B is fast, but Postgres didn't know that, that's it? It can't decide to run sub-query first. – Slim Sep 25 '19 at 7:19
  • @Slim: It could decide as you say, if its estimate of the number of results produced by B was accurate. It guesses it's going to be 11182 rows while in reality it's about 20. As an ad-hoc solution, you could rewrite the combined query with B in a WITH clause, and it would choose the fast plan. – Daniel Vérité Sep 25 '19 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.