Unique index on multiple columns vs single column index
Since your DBMS isn't specified, I'll respond in general terms.

Assuming your uniqueness index specifies product_id as the first column and item_id as the second, the index can be used either to locate a specific row based on product_id and item_id, or all rows with a given product_id.

An index can be used to speed searches when the query uses the first column defined as part of the index, and will locate rows based on the first n columns in the index if the WHERE clause (or JOIN conditions) use all of the first n rows.

So, if product_id is the first column listed in the index's creation, then it could be used any time you're locating records based on product_id. The index would not be used for SELECT product_id FROM product_items where item_id = 'some-id' and created_at &gt; '2018-01-01', because item_id isn't the first column in the unique index.

NOTE: Your unique (non-clustered) index might well be scanned if the query was SELECT product_id FROM product_items WHERE item_id = 'some-id', because it would be a covering index. Since all the columns needed be the query exist in the index, the DB engine may be able to treat the index as if it were the product_items table itself, allowing it to just read the index, instead of the entire table and the full data rows. (If the unique index is also product_items's clustered index, then the index is the table).