SQL (Postgres): Subquery in WHERE clause with IN operator is slow I have the following SQL Statement:
select count(*)
from person p
cross join task t
where t.id in (
    select tp.task_id
    from task_participant tp
    where tp.person_id = p.id )
and p.id = 1234

The EXPLAIN (ANALYZE, COSTS) generates the following result:
Aggregate  (cost=15850181.01..15850181.02 rows=1 width=8) (actual time=25155.942..25155.943 rows=1 loops=1)
  -&gt;  Nested Loop  (cost=0.28..15849814.75 rows=146504 width=0) (actual time=973.619..25155.778 rows=143 loops=1)
        Join Filter: (SubPlan 1)
        Rows Removed by Join Filter: 286635
        -&gt;  Index Only Scan using personxpk on person p  (cost=0.28..4.30 rows=1 width=8) (actual time=0.018..0.021 rows=1 loops=1)
              Index Cond: (id = 1234)
              Heap Fetches: 1
        -&gt;  Seq Scan on task t  (cost=0.00..13952.08 rows=293008 width=8) (actual time=0.006..53.361 rows=286778 loops=1)
        SubPlan 1
          -&gt;  Bitmap Heap Scan on task_participant tp  (cost=4.63..103.38 rows=26 width=8) (actual time=0.013..0.078 rows=143 loops=286778)
                Recheck Cond: (person_id = p.id)
                Heap Blocks: exact=38705207
                -&gt;  Bitmap Index Scan on task_participantx1  (cost=0.00..4.62 rows=26 width=0) (actual time=0.007..0.007 rows=143 loops=286778)
                      Index Cond: (person_id = p.id)
Planning Time: 0.186 ms
Execution Time: 25155.994 ms

I don't understand why the sub select is being executed 286778 times. I think that Postgres should execute the subselect only 143 times, because the condition is clear based on the outer query. What am I missing?
EDIT:
It is a generated query. The desired result (and performance) could also be achieved with:
select count(*)
from person p
join task_participant tp on tp.person_id = p.id
join task t on t.id = tp.task_id
where p.id = 1234

However, I cannot easily rewrite the current query to this better approach. I'm hoping to find a way to achieve a similar execution time without completely changing the query.
Test
select count(*)
from person p
cross join task t
where EXISTS (
    select NULL
    from task_participant tp
    where tp.person_id = p.id
      AND t.id = tp.task_id )
  and p.id = 1234