Select by Tuples in PostgreSQL

Question

I have a table user_issues with 3 columns: id, component_id and path and I need to filter it but tuples of component_id and path.

So I used the following query in order to achieve this:

SELECT id 
FROM user_issues ui 
JOIN (VALUES ($1, $2), ($3, $4)) AS v(component_id, path) ON ui.component_id = v.component_id AND ui.path = v.path;

I wonder if there is a more elegant or efficient way of achieving this (instead of using the VALUES clause to create a set of rows (tuples)).

Answer 1

Your original already yields best performance.

As for "more elegant":

SELECT id
FROM   user_issues ui 
JOIN  (VALUES ($1, $2), ($3, $4)) v(component_id, path) USING (component_id, path);

Possibly even:

SELECT id
FROM   user_issues ui 
NATURAL JOIN  (VALUES ($1, $2), ($3, $4)) v(component_id, path);

But I would resist the temptation to simplify with a NATURAL join. If one of the column names does not match, this degenerates into a CROSS JOIN. The manual:

If there are no common column names, NATURAL JOIN behaves like JOIN ... ON TRUE, producing a cross-product join.

The "most elegant" way really depends on how the food-chain delivers filter values. If you get them in the form of two (or more) arrays (in a pivoted form), unnesting multiple arrays in parallel will be more elegant:

SELECT id
FROM   user_issues ui
JOIN   unnest ('{$1,$3}'::int[]  -- explicit type cast may be necessary
             , '{$2,$4}'::text[]) t(component_id, path) USING (component_id, path);

See:

• Unnest multiple arrays in parallel