From rschumeyer@ieee.org Wed Sep 7 17:57:09 2005 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-- From russellsimpkins@hotmail.com Wed Sep 7 20:05:16 2005 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 3DFD8D8241 for ; Wed, 7 Sep 2005 17:05:16 -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 31006-07 for ; Wed, 7 Sep 2005 20:05:05 +0000 (GMT) Received: from hotmail.com (bay103-dav13.bay103.hotmail.com [65.54.174.85]) by svr1.postgresql.org (Postfix) with ESMTP id E4FCCD8134 for ; Wed, 7 Sep 2005 17:05:05 -0300 (ADT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Wed, 7 Sep 2005 13:05:05 -0700 Message-ID: Received: from 65.54.174.200 by BAY103-DAV13.phx.gbl with DAV; Wed, 07 Sep 2005 20:05:05 +0000 X-Originating-IP: [65.54.174.200] X-Originating-Email: [russellsimpkins@hotmail.com] X-Sender: russellsimpkins@hotmail.com From: "Russell Simpkins" To: References: <000001c5b3d5$a67a3540$0300a8c0@dell8200> Subject: Re: Help with multistage query Date: Wed, 7 Sep 2005 16:05:05 -0400 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0185_01C5B3C5.E91FC5F0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-OriginalArrivalTime: 07 Sep 2005 20:05:05.0647 (UTC) FILETIME=[708467F0:01C5B3E7] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=3.383 required=5 tests=[AWL=-1.405, DNS_FROM_RFC_ABUSE=0.374, DNS_FROM_RFC_POST=1.376, FORGED_MUA_OUTLOOK=3.037, HTML_MESSAGE=0.001, MSGID_FROM_MTA_HEADER=0] X-Spam-Level: *** X-Archive-Number: 200509/76 X-Sequence-Number: 22623 This is a multi-part message in MIME format. ------=_NextPart_000_0185_01C5B3C5.E91FC5F0 Content-Type: text/plain; charset="iso-8859-1" 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 } how about=20 select t1.id from t1, t2 where t1.id =3D t2.id and t2.id =3D x ------=_NextPart_000_0185_01C5B3C5.E91FC5F0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

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

The simplified queries = look like=20 this:

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

foreach $id (@idarray)=20 {

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

}

how about

select t1.id from t1, t2 where t1.id = =3D t2.id and=20 t2.id =3D x

------=_NextPart_000_0185_01C5B3C5.E91FC5F0-- From matt@gsicomp.on.ca Wed Sep 7 21:37:42 2005 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 80604D7FBD for ; Wed, 7 Sep 2005 18:37:42 -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 57700-02 for ; Wed, 7 Sep 2005 21:37:39 +0000 (GMT) Received: from skippyii.compar.com (compar.com [216.208.38.130]) by svr1.postgresql.org (Postfix) with ESMTP id 71008D7F9C for ; Wed, 7 Sep 2005 18:37:36 -0300 (ADT) Received: from hermes (CPE00062566c7bb-CM000039c69a66.cpe.net.cable.rogers.com [70.28.254.189]) by skippyii.compar.com (8.13.1/8.13.1) with ESMTP id j87LbuOn099943; Wed, 7 Sep 2005 17:37:59 -0400 (EDT) (envelope-from matt@gsicomp.on.ca) Message-ID: <003001c5b3f4$6a6911b0$1200a8c0@gsicomp.on.ca> From: "Matt Emmerton" To: "Russell Simpkins" , References: <000001c5b3d5$a67a3540$0300a8c0@dell8200> Subject: Re: Help with multistage query Date: Wed, 7 Sep 2005 17:37:47 -0400 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0029_01C5B3D2.DC9937A0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1506 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.001 required=5 tests=[AWL=-0.001, HTML_MESSAGE=0.001] X-Spam-Level: X-Archive-Number: 200509/79 X-Sequence-Number: 22626 This is a multi-part message in MIME format. ------=_NextPart_000_0029_01C5B3D2.DC9937A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable ----- Original Message -----=20 From: Russell Simpkins=20 To: pgsql-sql@postgresql.org=20 Sent: Wednesday, September 07, 2005 4:05 PM Subject: Re: [SQL] Help with multistage query 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 } how about=20 select t1.id from t1, t2 where t1.id =3D t2.id and t2.id =3D x or more correctly, based on the OP's example: select t2.x from t1, t2 where t1.id =3D t2.id and t1.id =3D -- Matt ------=_NextPart_000_0029_01C5B3D2.DC9937A0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

----- Original Message -----
From:=20 Russell Simpkins =
To: pgsql-sql@postgresql.org =

Sent: Wednesday, September 07, = 2005 4:05=20 PM

Subject: Re: [SQL] Help with = multistage=20 query

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

The simplified queries = look like=20 this:

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

foreach $id (@idarray) = {

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

}

how about

select t1.id from t1, t2 where t1.id = =3D t2.id and=20 t2.id =3D x

or more correctly, based on the OP's example:

select t2.x from t1, t2 where t1.id =3D t2.id and t1.id = =3D=20 <condition>

--

Matt

------=_NextPart_000_0029_01C5B3D2.DC9937A0-- From jnasby@pervasive.com Tue Oct 4 22:34:52 2005 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 AE739DA0DE for ; Tue, 4 Oct 2005 19:34:52 -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 04829-05 for ; Tue, 4 Oct 2005 22:34:47 +0000 (GMT) Received: from flake.decibel.org (flake.decibel.org [67.100.216.10]) by svr1.postgresql.org (Postfix) with ESMTP id A5A2BDA0A4 for ; Tue, 4 Oct 2005 19:34:48 -0300 (ADT) Received: by flake.decibel.org (Postfix, from userid 1001) id 813481525C; Tue, 4 Oct 2005 17:34:50 -0500 (CDT) Date: Tue, 4 Oct 2005 17:34:50 -0500 From: "Jim C. Nasby" To: Matt Emmerton Cc: Russell Simpkins , pgsql-sql@postgresql.org Subject: Re: Help with multistage query Message-ID: <20051004223450.GM40138@pervasive.com> References: <000001c5b3d5$a67a3540$0300a8c0@dell8200> <003001c5b3f4$6a6911b0$1200a8c0@gsicomp.on.ca> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <003001c5b3f4$6a6911b0$1200a8c0@gsicomp.on.ca> X-Operating-System: FreeBSD 4.11-RELEASE-p10 i386 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.9i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.008 required=5 tests=[AWL=0.008] X-Spam-Level: X-Archive-Number: 200510/12 X-Sequence-Number: 22865 On Wed, Sep 07, 2005 at 05:37:47PM -0400, Matt Emmerton wrote: > > ----- Original Message ----- > From: Russell Simpkins > To: pgsql-sql@postgresql.org > Sent: Wednesday, September 07, 2005 4:05 PM > Subject: Re: [SQL] Help with multistage query > > 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=$id; ; each select returns about 100 records which are saved in a perl variable > > } > > how about > select t1.id from t1, t2 where t1.id = t2.id and t2.id = x > > or more correctly, based on the OP's example: > > select t2.x from t1, t2 where t1.id = t2.id and t1.id = Actually, I think you want AND t2.x , not t1.id. BTW, I recommend not using id as a bareword field name. Very easy to get confused when you start joining a bunch of stuff together. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461