postgres indexed query running slower than expected

It looks like you're pushing the limits of select x where y IN (...) type queries. You basically have a very large table with an large set of conditions to search on.

Depending on the type of indexes, I'm guessing you have B+Tree this kind of query is inefficient. These type of indexes do well with general purpose range matching and DB inserts while performing worse on single value lookups. Your query is doing ~2500 lookups on this index for single values.

You have a few options to deal with this...

• Use Hash indexes (these perform much better on single value lookups)
• Help out the query optimizer by adding in a few range based constraints, so you could take the 2500 values and find the min and max values and add that to the query. So basically append where x_id > min_val and x_id < max_val
• Run the query in parallel mode if you have multiple db backends, simply breakup the 2500 constraints into say 100 groups and run all the queries at once and collect the results. It will be better if you group the constraints based on their value

The first option is certainly easier, but it will come at a price of making your inserts/deletes slower.

The second does not suffer from this, and you don't even need to limit it to one min max group. You could create N groups with N min and max constraints. Test it out with different groupings and see what works.

The last option is by far the best performing of course.

Your query is equivalent to:

select col1, col2, col3 
from table1 
where 
    col4 = 1
    OR col4 = 2
    OR col4 = 3
    OR col4 = 4
    ... repeat 2500 times ...

which is equivalent to:

select col1, col2, col3 
from table1 
where col4 = 1

UNION

select col1, col2, col3 
from table1 
where col4 = 2

UNION

select col1, col2, col3 
from table1 
where col4 = 3

... repeat 2500 times ...

Basically, it means that the index on a table with 10M rows is searched 2500 times. On top of that, if col4 is not unique, then each search is a scan, which may potentially return many rows. Then 2500 intermediate result sets are combined together.

The server doesn't know that the 2500 IDs that are listed in the IN clause do not repeat. It doesn't know that they are already sorted. So, it has little choice, but do 2500 independent index seeks, remember intermediate results somewhere (like in an implicit temp table) and then combine them together.

If you had a separate table table_with_ids with the list of 2500 IDs, which had a primary or unique key on ID, then the server would know that they are unique and they are sorted.

Your query would be something like this:

select col1, col2, col3 
from 
    table_with_ids 
    inner join table1 on table_with_ids.id = table1.col4

The server may be able to perform such join more efficiently.

I would test the performance using pre-populated (temp) table of 2500 IDs and compare it to the original. If the difference is significant, you can investigate further.

Actually, I'd start with running this simple query:

select col1, col2, col3 
from table1 
where 
    col4 = 1

and measure the time it takes to run. You can't get better than this. So, you'll have a lower bound and a clear indication of what you can and can't achieve. Then, maybe change it to where col4 in (1,2) and see how things change.

One more way to somewhat improve performance is to have an index not just on col4, but on col4, col1, col2, col3. It would still be one index, but on several columns. (In SQL Server I would have columns col1, col2, col3 "included" in the index on col4, rather than part of the index itself to make it smaller, but I don't think Postgres has such feature). In this case the server should be able to retrieve all data it needs from the index itself, without doing additional look-ups in the main table. Make it the so-called "covering" index.