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

Re: Specifying many rows in a table

From: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>
To: Steve Atkins <steve(at)blighty(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: Specifying many rows in a table
Date: 2004-01-28 20:15:27
Message-ID: Pine.LNX.4.33.0401281304310.26215-100000@css120.ihs.com (view raw or flat)
Thread:
Lists: pgsql-general
On Wed, 28 Jan 2004, Steve Atkins wrote:

> I have a large table (potentially tens or hundreds of millions of rows) and
> I need to extract some number of these rows, defined by an integer primary
> key.
> 
> So, the obvious answer is
> 
>   select * from table where id in (1,3,4);
> 
> 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);

If the ranges are sequential, then between would work.  I have a feeling 
that they aren't though, looking at your examples.

> This falls over when it exceeds the maximum expression depth of 10,000.
> And I have a sneaky feeling that increasing max_expr_depth isn't the
> right fix.

The optimizations made for in() queries in the 7.4 branch only really work 
when there's a subselect / table in the in.  You could try inserting those 
numbers into a temp table and subselecting it.  



In response to

Responses

pgsql-general by date

Next:From: MaRCeLO PeReiRADate: 2004-01-28 20:19:35
Subject: Re: 7.3.4 freezing
Previous:From: MaRCeLO PeReiRADate: 2004-01-28 20:04:37
Subject: Re: 7.3.4 freezing

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