Thread: Help with multistage query

Help with multistage query

From

"Rick Schumeyer"

Date:

07 September 2005, 19:29:31

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">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.</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">The simplified queries look like this:</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">SELECT id FROM t1 WHERE condition1;   ;returns about 2k records which are stored in
@idarray</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">foreach $id (@idarray) {</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">   SELECT x FROM t2 WHERE id=$id;   ; each select returns about 100 records which are saved in a
perlvariable</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">}</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">At this point I have a list of about 200k records, from which I can manually filter based on x
values.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">There are indices on id in both t1 and t2, so the first two queries are both index scans.  I cannot
afforda table scan on t2 due to the size of the table.</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Like I said, this works (and uses only index scans), but I would think it would be better to somehow
selectthe 200k records into a temp table.  Because the temp table would be relatively small, a seq scan is ok to
producemy final list.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Also, I am now issuing the second query about 2k times…this seems inefficient.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I would think there would  a way to restate the first two queries as either a join or a subselect. 
Myinitial attempts result in a table scan (according to EXPLAIN) on t2.</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">For example I tried </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">   SELECT x FROM t2 WHERE id in ( SELECT id FROM t1 WHERE condition1);</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">but this gives a seq scan.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Any ideas are appreciated.</span></font></div>

Re: Help with multistage query

From

"Russell Simpkins"

Date:

07 September 2005, 21:26:28

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

Re: Help with multistage query

From

"Matt Emmerton"

Date:

07 September 2005, 21:50:24

----- 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 = <condition>

Matt

Re: Help with multistage query

From

"Jim C. Nasby"

Date:

04 October 2005, 22:34:54

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
believethere 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 = <condition>

Actually, I think you want AND t2.x <condition>, 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