performance with IN clause in postgresql

Question

Hello togehter,

what are the performance aspects if you have something like this in your query:

    ... AND x.somfield IN (
33620,262,394,450,673,674,675,2331,2370,2903,4191,4687,5153,6776,6898,6899,7127,7217,7225,
        7227,7757,8830,8889,8999,9036,9284,9381,9382,9411,9412,9423,10088,10089,10304,10333,10515,
        10527,10596,10651,11442,12636,12976,13275,14261,14262,14382,14389,14567,14568,15792,16557,
        17043,17459,17675,17699,17700,17712,18240,18370,18591,18980,19023,19024,19025,19026,19211,
        19272,20276,20426,20471,20494,20833,21126,21315,21990,22168,22284,22349,22563,22796,23739,
        24006,24321,24642,24827,24867,25049,25248,25249,25276,25572,25665,26000,26046,26646,26647,
        26656,27343,27406,27753,28560,28850,29796,29817,30026,30090,31020,31505,32188,32347,32629
        ,32924,32931,33062,33254,33600,33601,33602,33603,33604,33605,33606,33607,33608,34010,34472,
        35800,35977,36179,37342,37439,37459,38425,39592,39661,39926,40376,40561,41226,41279,41568,
        42272,42481,43483,43867,44958,45295,45408,46022,46258) AND ...

should i avoid this or is it okay and fast enough?

thanks

Answer 1

If the query uses index on the x.somfield - it will be fast enough.

As it was mentioned - you sould use "explain" and "explain analyze" to realy understand what's going on there.

Answer 2

You certainly want to check the execution plan. Depending on data, it may or may not be "okay".

If the table is large enough, it's possible that PG converts that to "array contains" operation and decides not to use an index on it. This could lead to a seq scan (if you don't have other WHERE criteria on this table).

In some cases OR is better than IN, because it's executed as two index scans and combined. May not work in your case though, because you have so many values in there. Again, depends on data.

Unless your table is small, in such cases you usually need to rely on other criteria which are easily indexed, such as dates, states, "types" etc. Then this IN is merely a "recheck" filter on limited data.