Skip site navigation (1) Skip section navigation (2)

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
Message-ID: (view raw, whole thread or download thread mbox)
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


pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group