Re: Specifying many rows in a table

"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