Search code examples
sqlpostgresqljoinpostgresql-performancepostgresql-9.6

Best way to get distinct count from a query joining two tables

I have 2 tables, table A & table B.

Table A (has thousands of rows)

  • id
  • uuid
  • name
  • type
  • created_by
  • org_id

Table B (has a max of hundred rows)

  • org_id
  • org_name

I am trying to get the best join query to obtain a count with a WHERE clause. I need the count of distinct created_bys from table A with an org_name in Table B that contains 'myorg'. I currently have the below query (producing expected results) and wonder if this can be optimized further?

select count(distinct a.created_by)
from a left join
     b
     on a.org_id = b.org_id 
where b.org_name like '%myorg%';
Solution

  • You don't need a left join:

    select count(distinct a.created_by)
from a join
     b
     on a.org_id = b.org_id
where b.org_name like '%myorg%'

    For this query, you want an index on b.org_id, which I assume that you have.