X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B0C12D7FE7 for ; Wed, 7 Sep 2005 14:57:09 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 03256-05 for ; Wed, 7 Sep 2005 17:55:44 +0000 (GMT) Received: from rwcrmhc12.comcast.net (rwcrmhc12.comcast.net [204.127.198.43]) by svr1.postgresql.org (Postfix) with ESMTP id 868E6D7F7A for ; Wed, 7 Sep 2005 14:55:43 -0300 (ADT) Received: from dell8200 (pcp08360899pcs.rte20201.de.comcast.net[69.136.82.172]) by comcast.net (rwcrmhc12) with SMTP id <2005090717554201400avlqle>; Wed, 7 Sep 2005 17:55:43 +0000 From: "Rick Schumeyer" To: Subject: Help with multistage query Date: Wed, 7 Sep 2005 13:57:44 -0400 Message-ID: <000001c5b3d5$a67a3540$0300a8c0@dell8200> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0001_01C5B3B4.1F6B0640" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Importance: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=-1.321 required=5 tests=[AWL=-1.321, HTML_MESSAGE=0.001] X-Spam-Level: X-Archive-Number: 200509/74 X-Sequence-Number: 22621 This is a multi-part message in MIME format. ------=_NextPart_000_0001_01C5B3B4.1F6B0640 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable I have a perl script that issues a series of SQL statements to perform = some queries. The script works, but I believe there must be a more elegant = way to do this. =20 The simplified queries look like this: =20 SELECT id FROM t1 WHERE condition1; ;returns about 2k records which = are stored in @idarray =20 foreach $id (@idarray) { SELECT x FROM t2 WHERE id=3D$id; ; each select returns about 100 = records which are saved in a perl variable } =20 At this point I have a list of about 200k records, from which I can = manually filter based on x values. =20 There are indices on id in both t1 and t2, so the first two queries are = both index scans. I cannot afford a table scan on t2 due to the size of the table. =20 Like I said, this works (and uses only index scans), but I would think = it would be better to somehow select the 200k records into a temp table. Because the temp table would be relatively small, a seq scan is ok to produce my final list. =20 Also, I am now issuing the second query about 2k times.this seems inefficient. =20 I would think there would a way to restate the first two queries as = either a join or a subselect. My initial attempts result in a table scan (according to EXPLAIN) on t2. =20 For example I tried=20 SELECT x FROM t2 WHERE id in ( SELECT id FROM t1 WHERE condition1); but this gives a seq scan. =20 Any ideas are appreciated. ------=_NextPart_000_0001_01C5B3B4.1F6B0640 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

I have a perl script that issues a series of SQL = statements to perform some queries.  The script works, but I believe there = must be a more elegant way to do this.

 

The simplified queries look like = this:

 

SELECT id FROM t1 WHERE condition1;   = ;returns about 2k records which are stored in @idarray

 

foreach $id (@idarray) {

   SELECT x FROM t2 WHERE = id=3D$id;   ; each select returns about 100 records which are saved in a perl = variable

}

 

At this point I have a list of about 200k records, = from which I can manually filter based on x values.

 

There are indices on id in both t1 and t2, so the = first two queries are both index scans.  I cannot afford a table scan on t2 = due to the size of the table.

 

Like I said, this works (and uses only index scans), = but I would think it would be better to somehow select the 200k records into a = temp table.  Because the temp table would be relatively small, a seq = scan is ok to produce my final list.

 

Also, I am now issuing the second query about 2k = times…this seems inefficient.

 

I would think there would  a way to restate the = first two queries as either a join or a subselect.  My initial attempts = result in a table scan (according to EXPLAIN) on t2.

 

For example I tried

   SELECT x FROM t2 WHERE id in ( SELECT id = FROM t1 WHERE condition1);

but this gives a seq scan.

 

Any ideas are appreciated.

------=_NextPart_000_0001_01C5B3B4.1F6B0640--