PostgreSQL Performance of “where exists”

I’m always interested in bits about the performance of specific types of SQL queries, so I was curious when I came across Bill Schneider‘s post about PostgreSQL performance of “where exists”. He was comparing the performance of two different queries that provide the same results, so I took a look at some of my data and converted them to fit data that I already had:

Query 1

select course_abbr from courses_basetable
where exists (
  select 1 from editions
  where editions.course_abbr = courses_basetable.course_abbr
)

Query 2

select distinct(editions.course_abbr) from editions
join courses on
  editions.course_abbr = courses.course_abbr

Both of these queries return the same list of course abbreviations, but perform slightly different. To avoid the suspense I’m going to give away the ending now: Query 1 runs faster than Query 2 (tested using PostgreSQL 7.4.6), but for Bill Query 2 ran faster (using PostgreSQL 7.3.x). For my tests Query 1 took an average time of 29 ms and Query 2 took an average of 31 ms. So what, 2 ms you say, I throw that much CPU time away several times a minute! The absolute times don’t really matter, the percentage difference does. In this case Query 1 is more than 5% faster than Query 2.

To find out more about why these two queries perform so differently I ran them both using EXPLAIN ANALYZE to see what was going on. It should be rather obvious why Query 2 takes longer after seeing the EXPLAIN ANALYZE results:

Query 1

Seq Scan on courses_basetable  (cost=0.00..35.82 rows=19 width=8)
(actual time=0.369..9.485 rows=36 loops=1)
  Filter: (subplan)
  SubPlan
    ->  Seq Scan on editions  (cost=0.00..1.81 rows=2 width=0)
          (actual time=0.207..0.207 rows=1 loops=38)
          Filter: ((course_abbr)::text = ($0)::text)

Query 2

Unique  (cost=6.06..6.38 rows=36 width=8)
(actual time=4.697..5.252 rows=36 loops=1)
  ->  Sort  (cost=6.06..6.22 rows=65 width=8)
        (actual time=4.682..4.849 rows=65 loops=1)
        Sort Key: editions.course_abbr
        ->  Hash Join  (cost=1.47..4.10 rows=65 width=8)
              (actual time=1.652..3.357 rows=65 loops=1)
              Hash Cond: (("outer".course_abbr)::text = ("inner".course_abbr)::text)
              ->  Seq Scan on editions  (cost=0.00..1.65 rows=65 width=8)
                    (actual time=0.017..0.462 rows=65 loops=1)
              ->  Hash  (cost=1.38..1.38 rows=38 width=8)
                    (actual time=1.034..1.034 rows=0 loops=1)
                    ->  Seq Scan on courses_basetable  (cost=0.00..1.38 rows=38 width=8)
                          (actual time=0.106..0.510 rows=38 loops=1)

Query 2 has more work to than Query 1. If anyone else has other data points for this query let me know.

On a side note, Blogger really needs to support TrackBack, I’m tired of giving a response in a post and then having to leave a comment to let the person know where to find it.