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: 7713.1075331616@sss.pgh.pa.us (view raw or flat)
Thread:
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
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

In response to

Responses

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-2014 The PostgreSQL Global Development Group