Hi. I have a Perl script whose main loop generates thousands of SQL updates of the form
UPDATE edge SET keep = true WHERE node1 IN ( $node_list ) AND node2 = $node_id;
...where here $node_list stands for a comma-separated list of integers, and $node_id stands for some integer.
The list represented by $node_list can be fairly long (on average it has around 900 entries, and can be as long as 30K entries), and I'm concerned about the performance cost of testing for inclusion in such a long list. Is this done by a sequential search? If so, is there a better way to write this query? (FWIW, I have two indexes on the edge table using btree( node1 ) and btree( node2 ), respectively.)
Also, assuming that the optimal way to write the query depends on the length of $node_list, how can I estimate the "critical length" at which I should switch from one form of the query to the other?
TIA!
Kynn