I'm using Postgres 9.5. I want to select some records, but only if they have at least one association in another table. I came up with this query:

select distinct m.name, m.id 
from machines m, parts rt 
where m.id = rt.machine_id 
  and m.name like '%Name%';

but I don't think this is particularly efficient, since if there are 50000 associations, I think they're all loaded and then "distinct"ed through, which seems a little inefficient. Is there a more efficient way to check load a record but only if it has one or more associations?

  • is there an index on machines.name?
    – McNets
    Commented Jan 29, 2017 at 21:28
  • 1
    @McNets is it SARGable? ;) Commented Jan 29, 2017 at 21:43
  • @sp_BlitzErik a proper index is ;) Commented Jan 29, 2017 at 22:02
  • Hi, Yes, there's an index on the "LIKE" portion.
    – Dave
    Commented Jan 30, 2017 at 2:30

1 Answer 1


The way your query is structured, you can just write it using an EXISTS expression:

    m.name, m.id 
    machines m
    m.name LIKE '%Name%'
                  FROM parts rt
                 WHERE rt.machine_id = m.id
               ) ;

If you want the query to be efficient, take into account that the most difficult part will probably be checking the machine name. Consider indexing machines.name using a trigram index. Trigram indices allow for fast finding LIKE '%something%' conditions.

CREATE EXTENSION pg_trgm;  -- Only needed if extension not already installed
CREATE INDEX name_like_idx 
    ON machines 
    USING GIST (name gist_trgm_ops);

Most probably, the database planner will make an execution plan where it will first find the machines whose name is LIKE '%Name%' (using the name_like_idx index), then, for every machine in this subset, it will check if there are parts associated with them; if so, they will be part of the result.

For efficiency, it is also assumed that there is also an index on parts.machine_id, because this will then be how PostgreSQL will efficiently check for the existence of the part. If machine_id is a foreign key, it is always advised to index it.

This query doesn't need DISTINCT (assuming that the ids are already unique).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.