Обсуждение: Accelerating subqueries

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

Fabrice Scemama wrote:
>
> Hi fellow PostgreSQL users.
>
> Here's a query:
>
> DELETE FROM Annonces WHERE Id_Ag IN
> (
>     SELECT Id FROM Installs
>     WHERE Si_Compte_Actif = 'f'
> );

The IN Clause is known to be very very slow. Use the
EXISTS Clause instead.

Regards Herbie
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Herbert Liechti                     E-Mail: Herbert.Liechti@thinx.ch
ThinX networked business services        Stahlrain 10, CH-5200 Brugg
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Fabrice Scemama wrote:
>
> 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

> Any ideas about a way to write a single query that fast ?

http://www.postgresql.org/docs/faq-english.html#4.23

----- Original Message -----
From: "Fabrice Scemama" <fabrices@ximmo.ftd.fr>
To: <pgsql-general@hub.org>
Sent: Thursday, April 06, 2000 4:03 PM
Subject: [GENERAL] Accelerating subqueries


> Hi fellow PostgreSQL users.
>
> Here's a query:
>
> DELETE FROM Annonces WHERE Id_Ag IN
> (
>     SELECT Id FROM Installs
>     WHERE Si_Compte_Actif = 'f'
> );
>

DELETE FROM Annonces WHERE EXISTS (SELECT Id FROM Installs WHERE
Si_Compte_Actif ='f' AND Installs.Id=Annonces.Id_Ag);

ought to be a lot faster if you have indices on all concerned fields.

But this should be faster still;

DELETE FROM Annonces WHERE Annonces.Id_Ag=Installs.Id AND
Installs.Si_Compte_Actif='f';

Yours,
Moray