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?

LIMIT-ing without ORDER-ing rarely makes sense.

The Impaler
– The Impaler

2021-01-08 19:53:27 +00:00
Commented Jan 8, 2021 at 19:53 — The Impaler
– The Impaler, Commented Jan 8, 2021 at 19:53

Gordon Linoff · Accepted Answer · 2021-01-08 18:18:47Z

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.

answered Jan 8, 2021 at 18:18

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

The Impaler Over a year ago

The index will most likely fix the performance. On the other hand the join may return a different result.

wildplasser Over a year ago

Even worse: the JOIN could be a terrible choice.

Nibor Over a year ago

I do have an index on task(person_id)

Gordon Linoff Over a year ago

@Nibor . . . Interesting. I would expect Postgres to choose a better execution plan then. Perhaps the statistics are not up-to-date.

Collectives™ on Stack Overflow

Postgres: Slow sub query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related