1

Considering the following two (equivalent) SQL statements. Why do their execution plan differ (especially in performance) that much? I'm using Postgres 11.9 in this example.

select p.name
from person p
where exists (
    select 1
    from person_task pt
    join task t on pt.task_id = t.id  and t.state <> 'closed'
        and pt.person_id = p.id -- Using ON
)
limit 10

-- EXPLAIN (ANALYZE):

Limit  (cost=0.00..270.98 rows=10 width=8) (actual time=10.412..60.876 rows=10 loops=1)
  ->  Seq Scan on person p  (cost=0.00..28947484.16 rows=1068266 width=8) (actual time=10.411..60.869 rows=10 loops=1)
        Filter: (SubPlan 1)
        Rows Removed by Filter: 68
        SubPlan 1
          ->  Nested Loop  (cost=1.00..20257.91 rows=1632 width=0) (actual time=0.778..0.778 rows=0 loops=78)
                ->  Index Scan using person_taskx1 on person_task pt  (cost=0.56..6551.27 rows=1632 width=8) (actual time=0.633..0.633 rows=0 loops=78)
                      Index Cond: (id = p.id)
                ->  Index Scan using taskxpk on task t  (cost=0.44..8.40 rows=1 width=8) (actual time=1.121..1.121 rows=1 loops=10)
                      Index Cond: (id = pt.task_id)
                      Filter: (state <> 'closed')
Planning Time: 0.466 ms
Execution Time: 60.920 ms

select p.name
from person p
where exists (
    select 1
    from person_task pt
    join task t on pt.task_id = t.id and t.state <> 'closed'
    where pt.person_id = p.id -- Using WHERE
)
limit 10

-- EXPLAIN (ANALYZE):

Limit  (cost=2818814.57..2841563.37 rows=10 width=8) (actual time=29.075..6884.259 rows=10 loops=1)
  ->  Merge Semi Join  (cost=2818814.57..59308642.64 rows=24832 width=8) (actual time=29.075..6884.251 rows=10 loops=1)
        Merge Cond: (p.id = pt.person_id)
        ->  Index Scan using personxpk on person p  (cost=0.43..1440340.27 rows=2136533 width=16) (actual time=0.003..0.168 rows=18 loops=1)
        ->  Gather Merge  (cost=1001.03..57357094.42 rows=40517669 width=8) (actual time=9.441..6881.180 rows=23747 loops=1)
              Workers Planned: 2
              Workers Launched: 2
              ->  Nested Loop  (cost=1.00..52679350.05 rows=16882362 width=8) (actual time=1.862..4207.577 rows=7938 loops=3)
                    ->  Parallel Index Scan using person_taskx1 on person_task pt  (cost=0.56..25848782.35 rows=16882362 width=16) (actual time=1.344..1807.664 rows=7938 loops=3)
                    ->  Index Scan using taskxpk on task t  (cost=0.44..1.59 rows=1 width=8) (actual time=0.301..0.301 rows=1 loops=23814)
                          Index Cond: (id = pt.task_id)
                          Filter: (state <> 'closed')
Planning Time: 0.430 ms
Execution Time: 6884.349 ms
6
  • Are these the exact queries? The query says state <> 'closed' and in the plan there is Filter: (state <> 'open') – Lennart Jan 19 at 21:07
  • I tried with both filters open and closed. But the query plan was the same. – Nibor Jan 19 at 22:07
  • 2
    LIMIT 10 without ORDER BY is notoriously unreliable. Postgres is free to pick any 10 rows and the cost may vary wildly. Can you run another test with a deterministic ORDER BY and see if the difference goes away? Anyway, the difference you show is still unexpected. Not sure why the planner goes a different route. – Erwin Brandstetter Jan 19 at 23:22
  • 1
    BTW, almost the same question as this one on SO: stackoverflow.com/q/65673841/939860 Coincidence? – Erwin Brandstetter Jan 19 at 23:24
  • Considering the following two (equivalent) SQL statements. Non-deterministic queries cannot be equivalent. – Akina Jan 20 at 5:23

Your Answer

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

Browse other questions tagged or ask your own question.