With an IN clause in postgresql

Question

what are the performance aspects if there is something similar in your request:

... 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 everything all right and fast enough?

thanks

Answer 1

Of course, you want to check the execution plan. Depending on the data, this may or may not be “good.”

If the table is large enough, it is possible that PG will convert it to the "array contains" operation and decide not to use the index on it. This may lead to a seq scan (unless you have other WHERE criteria in this table).

In some cases, OR better than IN because it runs as two index scans and combines. May not work in your case, because you have so many values. Again, data dependent.

If your table is small, in such cases you usually need to rely on other criteria that are easily indexed, such as dates, states, "types", etc. Then this IN is just a “double-check” filter on the limited data.

Answer 2

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

As already mentioned, you must use “explain” and “explain analysis” to understand what is happening there.