From: | Fabrice Scemama <fabrices(at)ximmo(dot)ftd(dot)fr> |
---|---|
To: | pgsql-general(at)hub(dot)org |
Subject: | Accelerating subqueries |
Date: | 2000-04-06 15:03:23 |
Message-ID: | 38ECA73B.E72E558@ximmo.ftd.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi fellow PostgreSQL users.
Here's a query:
DELETE FROM Annonces WHERE Id_Ag IN
(
SELECT Id FROM Installs
WHERE Si_Compte_Actif = 'f'
);
This query will take more than one hour of CPU time
on a PII 333 to execute. Table Installs has about 3000
tuples, and table Annonces about 50.000 .
All Id* fields are primary keys not null default nextval('...seq').
Here's the query plan:
Seq Scan on annonces (cost=4551.58 rows=52048 width=6)
SubPlan
-> Seq Scan on installs (cost=375.06 rows=4736 width=4)
EXPLAIN
On the other hand, I've coded a little Perl script that
does the same thing, but another way. Here it is:
-------------------------cut
use strict;
use DBI;
my $dbh = DBI->connect('DBI:Pg:dbname=mydb', 'user', 'pass',
{ AutoCommit => 0} );
my $sth_Bind;
{
my $query = "DELETE FROM annonces where Id_Ag = ?";
$sth_Bind = $dbh->prepare($query);
}
my $query = "
SELECT Id, Code_Ag FROM Installs
where si_compte_actif='f'
ORDER BY Code_Ag
";
my $sth = $dbh->prepare($query);
$sth->execute;
while(my @row = $sth->fetchrow_array) {
print "$$> Code_Ag: [$row[1]]\n";
$sth_Bind->execute($row[0]);
}
$sth->finish;
print "\n\nOK?";<STDIN>;
$dbh->commit;
$dbh->disconnect;
------------------------cut
And this script will do the DELETE within less
than a minute.
Any ideas about a way to write a single query that fast ?
Fabrice Scemama
From | Date | Subject | |
---|---|---|---|
Next Message | Ed Loehr | 2000-04-06 15:36:23 | Re: Accelerating subqueries |
Previous Message | Herbert Liechti | 2000-04-06 14:03:23 | Re: Accelerating subqueries |