From 644b730bf07a0187ea05439bfd36b4e75fad2a3d Mon Sep 17 00:00:00 2001
From: Moritz Bunkus <m.bunkus@linet-services.de>
Date: Mon, 26 Aug 2019 11:36:36 +0200
Subject: [PATCH] =?utf8?q?SelfTest:=20Geschwindigkeitssteigerung=20durch?=
 =?utf8?q?=20=C2=BBNOT=20EXISTS=C2=AB=20anstelle=20von=20=C2=BBNOT=20IN?=
 =?utf8?q?=C2=AB?=
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

Nicht ganz frische PostgreSQL-Versionen (mindestens bis 9.6 inklusive)
optimieren »NOT IN«-mit-Subquery nicht automatisch und müssen daher
für jede Zeile des äußeren Selects einen linearen Scan auf die
Subquery-Tabelle machen.

Deutlich effektiver ist das in diesem Fall äquivalente »NOT
EXISTS«-mit-Subquery.

Beispiel: Namen aller Kunden, für die noch keine Rechnung geschrieben
wurde. Falsch mit »NOT IN«:

SELECT name
FROM customer
WHERE id NOT IN (
  SELECT customer_id
  FROM ar
);

Besser und semantisch äquivalent:

SELECT name
FROM customer
WHERE NOT EXISTS (
  SELECT customer_id
  FROM ar
  WHERE customer_id = customer.id
);

Geschwindigkeitssteigerung ist auch ein Euphemismus. Vor der Änderung
war der Selftest bei der LINET-Produktivdatenbank nicht in der Lage,
seine Tests innerhalb von drei Tagen auszuführen. Nach der Änderung
dauert der Selftest weniger als eine Minute.

Neure PostgreSQL-Versionen (z.B. v11) erkennen dieses Pattern
automatisch.
---
 SL/BackgroundJob/SelfTest/Transactions.pm | 6 +++---
 1 file changed, 3 insertions(+), 3 deletions(-)

diff --git a/SL/BackgroundJob/SelfTest/Transactions.pm b/SL/BackgroundJob/SelfTest/Transactions.pm
index 3fbc0d14e..3eeb57f09 100644
--- a/SL/BackgroundJob/SelfTest/Transactions.pm
+++ b/SL/BackgroundJob/SelfTest/Transactions.pm
@@ -631,7 +631,7 @@ sub check_orphaned_reconciliated_links {
   my $query = qq|
           SELECT purpose from bank_transactions
           WHERE cleared is true
-          AND id not in (SELECT bank_transaction_id from reconciliation_links)
+          AND NOT EXISTS (SELECT bank_transaction_id from reconciliation_links WHERE bank_transaction_id = bank_transactions.id)
           AND transdate >= ? AND transdate <= ?|;
 
   my $bt_cleared_no_link = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
@@ -681,7 +681,7 @@ sub check_orphaned_bank_transaction_acc_trans_links {
   my $query = qq|
           SELECT purpose from bank_transactions
           WHERE invoice_amount <> 0
-          AND id not in (SELECT bank_transaction_id from bank_transaction_acc_trans)
+          AND NOT EXISTS (SELECT bank_transaction_id FROM bank_transaction_acc_trans WHERE bank_transaction_id = bank_transactions.id)
           AND itime > (SELECT min(itime) from bank_transaction_acc_trans)
           AND transdate >= ? AND transdate <= ?|;
 
@@ -701,7 +701,7 @@ sub check_orphaned_bank_transaction_acc_trans_links {
           SELECT purpose from bank_transactions
           WHERE id in
           (SELECT bank_transaction_id from bank_transaction_acc_trans
-           where acc_trans_id NOT IN (select acc_trans_id from acc_trans)
+           WHERE NOT EXISTS (SELECT acc_trans.acc_trans_id FROM acc_trans WHERE acc_trans.acc_trans_id = bank_transaction_acc_trans.acc_trans_id)
            AND transdate >= ? AND transdate <= ?)|;
 
   my $bt_assigned_no_acc_trans = selectall_hashref_query($::form, $self->dbh, $query, $self->fromdate, $self->todate);
-- 
2.20.1