CODEWITHSUNDEEP
sqlpostgresqlperformanceselect
Nibor
Nibor

Reputation: 679

Postgres: Slow sub query

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?

Upvotes: 0

Views: 84

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.

Upvotes: 1

Related Questions