"scott.marlowe" <scott.marlowe@ihs.com> writes:
> On Wed, 28 Jan 2004, Steve Atkins wrote:
>> But I may want to extract a large number of rows, many thousands
>> select * from table where id in (1, 3, 5, ...., 100000, 100017, 23000);
> You could try inserting those
> numbers into a temp table and subselecting it.
That's what I'd try too; it should work reasonably well in 7.4. Two
thoughts:
1. Be sure to "analyze" the temp table after filling it. The planner
must know the number of rows in the table to have any shot at choosing a
good plan.
2. If you know that the set of numbers you have emitted are all
different, don't use IN at all, but a straight join to the temp table:
select table.* from table, tmp where id = tmp.id;
With IN the system will have to work harder to eliminate duplicates from
the temp table; why make it do that if you know it's not necessary?
regards, tom lane