Re: Specifying many rows in a table

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>
Cc: Steve Atkins <steve(at)blighty(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Specifying many rows in a table
Date: 2004-01-28 23:13:36
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

"scott.marlowe" <scott(dot)marlowe(at)ihs(dot)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

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 =;

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

In response to


Browse pgsql-general by date

  From Date Subject
Next Message Kris Jurka 2004-01-28 23:35:20 Re: Is autocommit=true bad?
Previous Message Tom Lane 2004-01-28 22:42:16 Re: 7.3.4 freezing