Playing with postgresql I have seen that sometime a very long IN (list
of ids) can rise a max_recursion_error (or something like that).
An easy workaround when the list is computer generated and EXISTS is
infeasible (too slow), is breaking the list in log(n) OR parts.
Like in this python snippet:
if len(candidates) > 2000:
step = int(len(candidates)/math.log(len(candidates)))
parts = []
for i in range(0,len(candidates),step):
candidates_list = ", ".join(map(str, candidates[i:i+step]))
parts.append("%(space)s_id IN (%(candidates_list)s)" % locals())
where_sql = "\nOR\n".join(parts)
This is an example run:
In [1]:a = [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15]
In [2]:for i in range(0,len(a),7):
...: print a[i:i+7]
...: i = i+7
[1, 2, 3, 4, 5, 6, 7]
[8, 9, 10, 11, 12, 13, 14]
[15]
In my (small) experience this trick can speeds-up a lot of queries of
this kind.
Bye,
Matteo Bertini
