sql - Postgres: Slow sub query

Question

I have the following SQL Statement:

SELECT p.name
FROM person p
WHERE EXISTS (
    SELECT 1
    FROM task t
    WHERE t.person_id = p.id
)
LIMIT 100

The task table contains millions of entries. But somehow Postgres thinks it is smart to execute the inner select first. This results in the query to run for several minutes.

If I change the SELECT 1 to SELECT COUNT(1), I can trick Postgres into estimating that the inner select is more expensive. This results in the query being completed in less than a second.

How can I optimize the execution plan of Postgres without changing the above query?

Answer 1

Do you have an index on task(person_id)?

Without such an index, you might find that a join is a better choice for the query.