Outer Joins are faster than INs

I had a situation where I had to count Trial Licenses based on whether the person is an existing Customer.

Think of it as two tables:

• license table
• customer table

For each license, I had to lookup the customer table, with counts of how many matched and didn’t match.

As a first pass, I did something like this:

select
  to_char(created, 'YYYY-MM') as month,
  sum(case when owner in (select owner from customer) then 1 else 0 end) as "customer count",
  sum(case when owner in (select owner from customer) then 0 else 1 end) as "non-customer count",
  count(*) as "total"
from license

Performance was bad because of the repeated calls to the customer table. I could have split it into two queries so that the sub-select went into the WHERE, but I’d have to stitch the results back together since I wanted a count of both customers and non-customers.

Then I tried it via a LEFT OUTER JOIN, something like this:

select
  to_char(created, 'YYYY-MM') as month,
  count(customers) as "customer count",
  count(*) - count(customers) as "non-customer count"
  count(*) as "total"
from license LEFT OUTER JOIN customer using owner

Performance improved dramatically! A JOIN works so much faster than sub-selects, at least in PostgreSQL.