Hallo zusammen,

folgende Aufgabenstellung:

Vorhanden sind 2 Tabellen mit 200.000 (table1) und
150.000 (table2) Datensätzen.

Schema:
CREATE table1
(
id SERIAL,
...
PRIMARY KEY(id)
);

CREATE table2
(
...
t1_id =CENTEGER, -- kein foreign key!
...
);

CREATE INDEX i1 ON table2(t1_id);

Es sollen ALLE Datensätze aus table1 herausgefunden werden,
die KEINEN entsprechenden Datensatz in der table2 haben:

SELECT table1.id, ...
FROM table1
WHERE
...
table1.id NOT IN (SELECT table2.t1_id FROM table2);

Wenn ich dieses SQL-Kommando absetze, läuft es ziemlich lange -
länger als 30 Minuten habe ich bisher noch nicht gewartet.
Warum ist solch ein Kommando so zeitintensiv unter PostgreSQL 8.1.2
(Unix) und unter 8.2.3 (XP)?

Ein

SELECT table1.id FROM table1
EXCEPT
SELECT table2.t1_id FROM table2;

benötigt 5 bis 6 Sekunden.

Vielen Dank schon mal für Euere Hilfe

Thomas


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Hallo Thomas,

Steubesand, Thomas wrote:
> Hallo zusammen,
>
> folgende Aufgabenstellung:
>
> Vorhanden sind 2 Tabellen mit 200.000 (table1) und
> 150.000 (table2) Datensätzen.
>
> Schema:
> CREATE table1
> (
> id SERIAL,
> ...
> PRIMARY KEY(id)
> );
>
> CREATE table2
> (
> ...
> t1_id =CENTEGER, -- kein foreign key!
> ...
> );
>
> CREATE INDEX i1 ON table2(t1_id);
>
> Es sollen ALLE Datensätze aus table1 herausgefunden werden,
> die KEINEN entsprechenden Datensatz in der table2 haben:
>
> SELECT table1.id, ...
> FROM table1
> WHERE
> ...
> table1.id NOT IN (SELECT table2.t1_id FROM table2);
>
> Wenn ich dieses SQL-Kommando absetze, läuft es ziemlich lange -
> länger als 30 Minuten habe ich bisher noch nicht gewartet.
> Warum ist solch ein Kommando so zeitintensiv unter PostgreSQL 8.1.2
> (Unix) und unter 8.2.3 (XP)?
>

Was sagt denn EXPLAIN ANALYZE zu Deiner Frage?
http://www.cul.de/data/freex22007pr.pdf

Subselects sind generell performancehemmend, weil hier natuerlich erst
die eine Tabelle durchsucht werden muss und dann die andere.
Ein geschickter Join statt eines Subselects würde das ganze schon viel
schneller machen.

SELECT tab1.id, ... FROM tab1 LEFT JOIN tab2 ON tab1.id =3D tab2.tab1_id
WHERE tab2.tab1_id IS NULL;

Susanne

> Ein
>
> SELECT table1.id FROM table1
> EXCEPT
> SELECT table2.t1_id FROM table2;
>
> benötigt 5 bis 6 Sekunden.
>
> Vielen Dank schon mal für Euere Hilfe
>
> Thomas
>
>
> ---------------------------(end of broadcast)--------------------------=
-
> TIP 5: don't forget to increase your free space map settings
>


--
Susanne Ebrecht,
52066 Aachen, Germany


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

am Wed, dem 18.07.2007, um 13:16:54 +0200 mailte Steubesand, Thomas folg=
endes:
> Hallo zusammen,
>
> folgende Aufgabenstellung:
>
> Vorhanden sind 2 Tabellen mit 200.000 (table1) und
> 150.000 (table2) Datensätzen.
>
> Es sollen ALLE Datensätze aus table1 herausgefunden werden,
> die KEINEN entsprechenden Datensatz in der table2 haben:
>
> SELECT table1.id, ...
> FROM table1
> WHERE
> ...
> table1.id NOT IN (SELECT table2.t1_id FROM table2);
>
> Wenn ich dieses SQL-Kommando absetze, läuft es ziemlich lange -
> länger als 30 Minuten habe ich bisher noch nicht gewartet.
> Warum ist solch ein Kommando so zeitintensiv unter PostgreSQL 8.1.2

EXPLAIN würde Dir hier zeigen, daß es das NOT IN umformt in eine Folg=
e
von einzelnen Vergleichen, welche AND-verknüpft sind. Also etwa 150.000
einzelne Bedingungen.


> (Unix) und unter 8.2.3 (XP)?
>
> Ein
>
> SELECT table1.id FROM table1
> EXCEPT
> SELECT table2.t1_id FROM table2;
>
> benötigt 5 bis 6 Sekunden.

Hier kann der Planner besser erkennen, was Du wirlich willst. Mag sein,
daß das vom Planner doof ist, daß NOT IN (..) so zu handhaben, aber e=
r
macht es halt so. Mag wohl auch daran liegen, daß der Inhalt in der
Klammer nicht zwangsläufig in Tabellenform vorliegen muß sondern als
Aufzählung im SELECT.


Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Hallo Thomas,

Steubesand, Thomas <T.Steubesand [at] rz.fh-trier.de> wrote:
> Es sollen ALLE Datensätze aus table1 herausgefunden werden,
> die KEINEN entsprechenden Datensatz in der table2 haben:

versuch mal:

SELECT table1.id, ...
FROM table1
LEFT OUTER JOIN table2
ON table1.id =3D table2.t1_id
WHERE table2.t1_id IS NULL
;

wenn es in t2.t1_id IDs mehrfach gibt, ist evt. schneller erst noch mal z=
u
deduplizieren, die tatsächlich gejointen Records werden aber eh wieder
verworfen...

Gruß
Tobias


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Mittwoch 18 Juli 2007, Steubesand, Thomas wrote:
> Hallo zusammen,
>
> folgende Aufgabenstellung:
>
> Vorhanden sind 2 Tabellen mit 200.000 (table1) und
> 150.000 (table2) Datensätzen.
>
> Schema:
> CREATE table1
> (
> id SERIAL,
> ...
> PRIMARY KEY(id)
> );
>
> CREATE table2
> (
> ...
> t1_id =CENTEGER, -- kein foreign key!
> ...
> );
>
> CREATE INDEX i1 ON table2(t1_id);
>
> Es sollen ALLE Datensätze aus table1 herausgefunden werden,
> die KEINEN entsprechenden Datensatz in der table2 haben:
>
> SELECT table1.id, ...
> FROM table1
> WHERE
> ...
> table1.id NOT IN (SELECT table2.t1_id FROM table2);
>
> Wenn ich dieses SQL-Kommando absetze, läuft es ziemlich lange -
> länger als 30 Minuten habe ich bisher noch nicht gewartet.
> Warum ist solch ein Kommando so zeitintensiv unter PostgreSQL 8.1.2
> (Unix) und unter 8.2.3 (XP)?
>
> Ein
>
> SELECT table1.id FROM table1
> EXCEPT
> SELECT table2.t1_id FROM table2;
>
> benötigt 5 bis 6 Sekunden.
>
> Vielen Dank schon mal für Euere Hilfe
>
> Thomas
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

Schreib doch mal ein Explain vor das Statement, dann siehst du wie die DB d=
as
abrbeitet. Alternativ könntest du einen outer join versuchen
select tt1.* from tt1 left outer join tt2 on tt2.tt1_id =3D tt1.id where
tt2.tt1_id is null;

Rolf Schaufelberger

--
Mit freundlichen Grüßen
Rolf Schaufelberger


plusW
Rolf Schaufelberger
Beim Brünnele 6 Tel. 49 7181 994 35 50
73614 Schorndorf Fax. 49 7181 994 32 75
www.plusw.de
www.mypixler.com
www.imagewriter.de


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

This is a multi-part message in MIME format.
--------------020607000006010009010806
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable

Hi,

bei sowas hat sich folgendes als schneller erwiesen:

select
id
from
table1 a left join table2 b on a.id =3D b.t1_id
where
b.id is null

Gruss
Thomas

Steubesand, Thomas schrieb:
> Hallo zusammen,
>
> folgende Aufgabenstellung:
>
> Vorhanden sind 2 Tabellen mit 200.000 (table1) und
> 150.000 (table2) Datensätzen.
>
> Schema:
> CREATE table1
> (
> id SERIAL,
> ...
> PRIMARY KEY(id)
> );
>
> CREATE table2
> (
> ...
> t1_id =CENTEGER, -- kein foreign key!
> ...
> );
>
> CREATE INDEX i1 ON table2(t1_id);
>
> Es sollen ALLE Datensätze aus table1 herausgefunden werden,
> die KEINEN entsprechenden Datensatz in der table2 haben:
>
> SELECT table1.id, ...
> FROM table1
> WHERE
> ...
> table1.id NOT IN (SELECT table2.t1_id FROM table2);
>
> Wenn ich dieses SQL-Kommando absetze, läuft es ziemlich lange -
> länger als 30 Minuten habe ich bisher noch nicht gewartet.
> Warum ist solch ein Kommando so zeitintensiv unter PostgreSQL 8.1.2
> (Unix) und unter 8.2.3 (XP)?
>
> Ein
>
> SELECT table1.id FROM table1
> EXCEPT
> SELECT table2.t1_id FROM table2;
>
> benötigt 5 bis 6 Sekunden.
>
> Vielen Dank schon mal für Euere Hilfe
>
> Thomas
>
>
> ---------------------------(end of broadcast)--------------------------=
-
> TIP 5: don't forget to increase your free space map settings
>

--
Thomas Markus

Tel: +49 30 29 36 399 - 22
Fax: +49 30 29 36 399 - 50
Mail: t.markus [at] proventis.net
Web: http://www.proventis.net
Web: http://www.blue-ant.de

proventis GmbH
Zimmerstraße 79-80
10117 Berlin

Geschäftsführer: Norman Frischmuth
Sitz: Berlin
Handelsregister: AG Berlin-Charlottenburg, HR 82917

We support your project business!


--------------020607000006010009010806
Content-Type: text/x-vcard; charset=utf-8;
name="t.markus.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="t.markus.vcf"

begin:vcard
fn:Thomas Markus
n:Markus;Thomas
org:proventis GmbH
adr:;;Zimmerstr. 79-80;Berlin;Berlin;10117;Germany
email;internet:t.markus [at] proventis.net
tel;work:+49 30 29 36 399 22
x-mozilla-html:FALSE
url:http://www.proventis.net
version:2.1
end:vcard


--------------020607000006010009010806
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--------------020607000006010009010806--