From: Rick Schumeyer <rschumeyer@ieee.org>
To: pgsql-sql@postgresql.org
Subject: Help with multistage query
Date: Wed, 7 Sep 2005 13:57:44 -0400
Message-ID: <000001c5b3d5$a67a3540$0300a8c0@dell8200> (raw)
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
}
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rschumeyer@ieee.org
Subject: Re: Help with multistage query
In-Reply-To: <000001c5b3d5$a67a3540$0300a8c0@dell8200>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox