Re: Specifying many rows in a table

"scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com> writes:

> The optimizations made for in() queries in the 7.4 branch only really work
> when there's a subselect / table in the in. You could try inserting those
> numbers into a temp table and subselecting it.

I'll second this option.

But the other choice is to use a prepared statement like "select * from tab
where tab_id = ?" and simply execute it 10,000 times with different
parameters.

This involves lots of round trips to the server and is a lot less efficient,
but it doesn't require building a temporary table, which might be an
advantage. In fact if the data is coming from outside the database then you'll
probably end up building the temporary table using lots of executions like
this anyways, so it might not really be a disadvantage.

In the future the the ideal solution might be to have array processing support
in postgres, where you prepare a statement then ship the server an array of
parameter lists and the server executes the statement once for each array
element. Ideally it would let you stream the array to the server and it
execute them as fast as you can ship the data, avoiding round trip latency.
But I don't think there's any such support in postgres currently.

--
greg