WHERE IN based on set of primary keys across multiple columns
Let's say I have a table in which the primary key is composed of multiple columns - state and city. I have another service which produces a list of state, city pairs. I want to take that list, and retrieve the full row for each state, city pair in my table. So I do something like this.
SELECT * FROM MyTable WHERE (State, City) in (('New York', 'New York'), ('Washington', 'Seattle'), ('California', 'Los Angeles'), ...);
What I'm finding i that this query is vastly slower than just using one of the columns, as in
SELECT * FROM MyTable WHERE (City) in ('New York', 'Seattle', 'Los Angeles', ...);
The first query is taking 2 minutes while the second one takes under a second.
For reference, my table contains about 18 million rows and I'm passing roughly 100 state, city pairs in my WHERE IN clause. Because of the data, both queries in this particular example end up returning the same data. In theory, though, there could be multiple cities with the same name in different states, which is why I wrote the first query to match on tuples.
Any thoughts on what could be making the first query so expensive? Is there a better way to write that? My table has the primary key (State, City) but I haven't experimented with segmenting or anything else.