Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

PostgreSQL performance

14 views
Skip to first unread message

Stefan Froehlich

unread,
Nov 2, 2012, 11:09:45 AM11/2/12
to
Ich habe da eine Datenbank mit Postings, die unter anderem folgende Tabelle
enthaelt:

| Column | Type |
| ----------------------+-----------------------------+
| idposting | integer |
| fkidpostingparent | integer |
| fkidpostingthread | integer |
| [...]
|
| Indexes:
| "posting_pkey" PRIMARY KEY, btree (idposting)
| "posting_fkidpostingparent" btree (fkidpostingparent)
| "posting_fkidpostingthread" btree (fkidpostingthread)


Nach dem initialen Einlesen einiger Mio. Datensaetze geht es nun darum, die
beiden Foreign Keys zu befuellen (was beim Befuellen wegen zufaelliger
Reihenfolge nicht moeglich war). Die Parents waren relativ zuegig erledigt.
Fure die Threads habe ich in meiner Naivitaet einmal:

| UPDATE posting
| SET fkidpostingthread=p2.fkidpostingthread
| FROM posting p2
| WHERE posting.fkidpostingparent=p2.idposting
| AND posting.fkidpostingthread IS NULL
| AND p2.fkidpostingthread IS NOT NULL

versucht (klarerweise iterativ anzuwenden). An der ersten Iteration
arbeitet PostgreSQL nun seit rund 30 Stunden... das muss doch deutlich
schneller gehen?

Explain sagt mir:

| QUERY PLAN
| -----------------------------------------------------------------------------------------
| Update on posting (cost=3414895.21..9530992.01 rows=4470847 width=868)
| -> Hash Join (cost=3414895.21..9530992.01 rows=4470847 width=868)
| Hash Cond: (posting.fkidpostingparent = p2.idposting)
| -> Seq Scan on posting (cost=0.00..3263193.96 rows=11995736 width=858)
| Filter: (fkidpostingthread IS NULL)
| -> Hash (cost=3263193.96..3263193.96 rows=8727060 width=14)
| -> Seq Scan on posting p2 (cost=0.00..3263193.96 rows=8727060 width=14)
| Filter: (fkidpostingthread IS NOT NULL)

Klar - nur bei 1/3 der Postings hat die Zuordnung initial geklappt, also
wird der Index ignoriert, da muss ich erst einmal durch. Aber weshalb der
zweite Sequential Scan ueber die ganze Tabelle? Waere es nicht sinnvoller,
die Ergebnisse des ersten Scans der Reihe nach via Index nachzuschlagen und
_dann_ zu pruefen, ob das Feld NULL ist oder nicht? Und wenn ja, wie lege
ich ihm das nahe?

Servus,
Stefan

--
http://kontaktinser.at/ - die kostenlose Kontaktboerse fuer Oesterreich
Offizieller Erstbesucher(TM) von mmeike

Stefan: der Grund, den man braucht!
(Sloganizer)

Tim Landscheidt

unread,
Nov 2, 2012, 2:18:55 PM11/2/12
to
Stefan...@Froehlich.Priv.at (Stefan Froehlich) wrote:

> Klar - nur bei 1/3 der Postings hat die Zuordnung initial geklappt, also
> wird der Index ignoriert, da muss ich erst einmal durch. Aber weshalb der
> zweite Sequential Scan ueber die ganze Tabelle? Waere es nicht sinnvoller,
> die Ergebnisse des ersten Scans der Reihe nach via Index nachzuschlagen und
> _dann_ zu pruefen, ob das Feld NULL ist oder nicht? Und wenn ja, wie lege
> ich ihm das nahe?

Ohne Dir bei dem EXPLAIN helfen zu k�nnen: Wieviele Daten-
s�tze hast Du und wie lange braucht:

| WITH RECURSIVE RecursivePostings (idposting, fkidpostingparent, fkidpostingthread) AS
| (SELECT idposting, fkidpostingparent, idposting AS fkidpostingthread FROM posting WHERE fkidpostingparent IS NULL UNION ALL
| SELECT c.idposting, c.fkidpostingparent, p.fkidpostingthread FROM posting AS c JOIN RecursivePostings AS p ON c.fkidpostingparent = p.idposting)
| SELECT * FROM RecursivePostings;

Bei mir ben�tigt das f�r etwas mehr als drei Millionen Test-
reihen:

| CREATE TABLE posting (idposting SERIAL PRIMARY KEY, fkidpostingparent INT REFERENCES posting (idposting), fkidpostingthread INT REFERENCES posting (idposting));
| INSERT INTO posting (fkidpostingparent) SELECT NULL FROM generate_series(1, 1000000);
| INSERT INTO posting (fkidpostingparent) SELECT idposting FROM posting WHERE RANDOM() > 0.5;
| INSERT INTO posting (fkidpostingparent) SELECT idposting FROM posting WHERE RANDOM() > 0.5;
| INSERT INTO posting (fkidpostingparent) SELECT idposting FROM posting WHERE RANDOM() > 0.5;

ungef�hr f�nf Minuten (inklusive Formatierung in psql und
Anzeige in Emacs).

Tim

Bernd Nawothnig

unread,
Nov 2, 2012, 4:07:38 PM11/2/12
to
On 2012-11-02, Stefan Froehlich wrote:
> Ich habe da eine Datenbank mit Postings, die unter anderem folgende Tabelle
> enthaelt:
>
>| Column | Type |
>| ----------------------+-----------------------------+
>| idposting | integer |
>| fkidpostingparent | integer |
>| fkidpostingthread | integer |
>| [...]
>|
>| Indexes:
>| "posting_pkey" PRIMARY KEY, btree (idposting)
>| "posting_fkidpostingparent" btree (fkidpostingparent)
>| "posting_fkidpostingthread" btree (fkidpostingthread)
>
>
> Nach dem initialen Einlesen einiger Mio. Datensaetze geht es nun darum, die
> beiden Foreign Keys zu befuellen (was beim Befuellen wegen zufaelliger
> Reihenfolge nicht moeglich war).

Da ich mal annehme, dass Du das alles in einer Transaktion machst,
suchst du evtl. die

DEFERRABLE INITIALLY DEFERRED

Klausel beim Anlegen der Indizes. Denn dann wird erst beim Commit
geprüft, ob der FOREIGN KEY Constraint erfüllt ist und Du kannst
sofort alle Spalten einfügen.




Bernd

--
no time toulouse

Bernd Nawothnig

unread,
Nov 2, 2012, 4:11:59 PM11/2/12
to
On 2012-11-02, Bernd "Ingrid" Nawothnig wrote:
> On 2012-11-02, Stefan Froehlich wrote:
>> Ich habe da eine Datenbank mit Postings, die unter anderem folgende Tabelle
>> enthaelt:
>>
>>| Column | Type |
>>| ----------------------+-----------------------------+
>>| idposting | integer |
>>| fkidpostingparent | integer |
>>| fkidpostingthread | integer |
>>| [...]
>>|
>>| Indexes:
>>| "posting_pkey" PRIMARY KEY, btree (idposting)
>>| "posting_fkidpostingparent" btree (fkidpostingparent)
>>| "posting_fkidpostingthread" btree (fkidpostingthread)
>>
>>
>> Nach dem initialen Einlesen einiger Mio. Datensaetze geht es nun darum, die
>> beiden Foreign Keys zu befuellen (was beim Befuellen wegen zufaelliger
>> Reihenfolge nicht moeglich war).
>
> Da ich mal annehme, dass Du das alles in einer Transaktion machst,
> suchst du evtl. die
>
> DEFERRABLE INITIALLY DEFERRED
>
> Klausel beim Anlegen der Indizes.

Muss natürlich heißen: bei der Erstellung des FOREIGN KEY Constraints.

Stefan Froehlich

unread,
Nov 3, 2012, 5:09:21 AM11/3/12
to
On Fri, 02 Nov 2012 21:07:38 Bernd Nawothnig wrote:
> > Nach dem initialen Einlesen einiger Mio. Datensaetze geht es nun darum,
> > die beiden Foreign Keys zu befuellen (was beim Befuellen wegen
> > zufaelliger Reihenfolge nicht moeglich war).

> Da ich mal annehme, dass Du das alles in einer Transaktion machst,
> suchst du evtl. die

> DEFERRABLE INITIALLY DEFERRED

> Klausel beim Anlegen der Indizes. Denn dann wird erst beim Commit
> geprüft, ob der FOREIGN KEY Constraint erfüllt ist und Du kannst
> sofort alle Spalten einfügen.

Klingt plausibel. Nachdem das erste Kommando just in der gleichen Minute
fertig war, als ich Dein Posting gelesen habe, habe ich fuer die zweite
Iteration einmal das Pruefen deferred - laeuft nun auch schon wieder rund
12 Stunden, schauen wir einmal.

Servus,
Stefan

--
http://kontaktinser.at/ - die kostenlose Kontaktboerse fuer Oesterreich
Offizieller Erstbesucher(TM) von mmeike

Zerspülte Hemmungen, oder warum Stefan so stolz haut!
(Sloganizer)

Stefan Froehlich

unread,
Nov 3, 2012, 5:13:30 AM11/3/12
to
On Fri, 02 Nov 2012 19:18:55 Tim Landscheidt wrote:
> Wieviele Datensätze hast Du

Ca. 12 Mio., davon wurden im ersten Durchlauf 1,7 Mio. zugeordnet
(was Rueckschluesse auf die durchschnittliche Threadtiefe zulaesst).

> und wie lange braucht:
>
> | WITH RECURSIVE RecursivePostings (idposting, fkidpostingparent, fkidpostingthread) AS
> | (SELECT idposting, fkidpostingparent, idposting AS fkidpostingthread FROM posting WHERE fkidpostingparent IS NULL UNION ALL
> | SELECT c.idposting, c.fkidpostingparent, p.fkidpostingthread FROM posting AS c JOIN RecursivePostings AS p ON c.fkidpostingparent = p.idposting)
> | SELECT * FROM RecursivePostings;

Das kann ich leider noch nicht sagen... bei der naechsten Iteration
werde ich mir das einmal ansehen. Mit WITH RECURSIVE habe ich noch
nie gearbeitet, deshalb habe ich die Idee von Bernd einmal vorgezogen.

Servus,
Stefan

--
http://kontaktinser.at/ - die kostenlose Kontaktboerse fuer Oesterreich
Offizieller Erstbesucher(TM) von mmeike

Stefan - Egal ob Kanzler oder Kuli: Rauchen weil es knutscht!
(Sloganizer)

Stefan Froehlich

unread,
Nov 18, 2012, 9:59:43 AM11/18/12
to
On Fri, 02 Nov 2012 21:07:38 Bernd Nawothnig wrote:
> > Nach dem initialen Einlesen einiger Mio. Datensaetze geht es nun darum,
> > die beiden Foreign Keys zu befuellen (was beim Befuellen wegen
> > zufaelliger Reihenfolge nicht moeglich war).

> Da ich mal annehme, dass Du das alles in einer Transaktion machst,
> suchst du evtl. die
>
> DEFERRABLE INITIALLY DEFERRED
>
> Klausel beim Anlegen der Indizes. Denn dann wird erst beim Commit
> geprüft, ob der FOREIGN KEY Constraint erfüllt ist und Du kannst
> sofort alle Spalten einfügen.

Fuer ziemlich grosse Werte von "sofort" - soll heissen, das bisschen,
was beim UPDATE gewonnen wurde, ging dann beim COMMIT wieder verloren.
Das beinahe lineare Zeitverhalten in Bezug zur Anzahl der aktualisierten
Datensaetze laesst mich vermuten, dass schlicht und einfach die Hardware
arg lahm ist...

(Bei den letzten paar 100 Durchlaeufen, wo jeweils nur noch einige, wenige
Saetze gefunden wurden, waere in jedem Fall ein anderer Algorithmus
deutlich angebrachter gewesen - auf die 3 Tage mehr oder weniger ist es mir
dann aber auch nicht mehr angekommen).

Servus,
Stefan

--
http://kontaktinser.at/ - die kostenlose Kontaktboerse fuer Oesterreich
Offizieller Erstbesucher(TM) von mmeike

Stefan - die hervorragendste Verrücktheit seit Menschengedenken.
(Sloganizer)

Bernd Nawothnig

unread,
Nov 19, 2012, 9:00:54 AM11/19/12
to
On 2012-11-18, Stefan Froehlich wrote:
> On Fri, 02 Nov 2012 21:07:38 Bernd Nawothnig wrote:
>> > Nach dem initialen Einlesen einiger Mio. Datensaetze geht es nun darum,
>> > die beiden Foreign Keys zu befuellen (was beim Befuellen wegen
>> > zufaelliger Reihenfolge nicht moeglich war).
>
>> Da ich mal annehme, dass Du das alles in einer Transaktion machst,
>> suchst du evtl. die
>>
>> DEFERRABLE INITIALLY DEFERRED
>>
>> Klausel beim Anlegen der Indizes. Denn dann wird erst beim Commit
>> geprüft, ob der FOREIGN KEY Constraint erfüllt ist und Du kannst
>> sofort alle Spalten einfügen.
>
> Fuer ziemlich grosse Werte von "sofort" - soll heissen, das bisschen,
> was beim UPDATE gewonnen wurde, ging dann beim COMMIT wieder verloren.
> Das beinahe lineare Zeitverhalten in Bezug zur Anzahl der aktualisierten
> Datensaetze laesst mich vermuten, dass schlicht und einfach die Hardware
> arg lahm ist...

Das mag natürlich auch sein. Aber generell sah mir das nach einem Fall
für die DEFERRABLE INITIALLY DEFERRED Klausel aus, denn warum erst
eine Spalte beim INSERT weglassen, nur um sie sofort anschließend via
UPDATE nachzupflegen?




Bernd

Stefan Froehlich

unread,
Nov 19, 2012, 6:09:09 PM11/19/12
to
On Mon, 19 Nov 2012 15:00:54 Bernd Nawothnig wrote:
> > Fuer ziemlich grosse Werte von "sofort" - soll heissen, das bisschen,
> > was beim UPDATE gewonnen wurde, ging dann beim COMMIT wieder verloren.
> > [...]

> [...] generell sah mir das nach einem Fall für die DEFERRABLE INITIALLY
> DEFERRED Klausel aus, denn warum erst eine Spalte beim INSERT weglassen,
> nur um sie sofort anschließend via UPDATE nachzupflegen?

Ah, "das alles" hatte ich nur auf die iterierten UPDATEs bezogen.

Die Gesamtheit von INSERT und UPDATE in eine Transaktion zu packen,
waere zwar methodisch korrekt, aber praktisch vollkommen witzlos:
bis die 18 Mio. Datensaetze eingelesen waren, hat es mehrere Wochen
gedauert. Eine Transaktion so lange offenzuhalten, erscheint mir
nicht mehr sehr sinnvoll.

Servus,
Stefan

--
http://kontaktinser.at/ - die kostenlose Kontaktboerse fuer Oesterreich
Offizieller Erstbesucher(TM) von mmeike

Ein entschlossenes Team, oder warum Stefan so gieraffig studiert!
(Sloganizer)

Stefan Froehlich

unread,
Nov 20, 2012, 4:31:59 AM11/20/12
to
On Fri, 02 Nov 2012 19:18:55 Tim Landscheidt wrote:
> Ohne Dir bei dem EXPLAIN helfen zu können: Wieviele Daten-
> sätze hast Du und wie lange braucht:

> | WITH RECURSIVE RecursivePostings (idposting, fkidpostingparent, fkidpostingthread) AS
> | (SELECT idposting, fkidpostingparent, idposting AS fkidpostingthread FROM posting WHERE fkidpostingparent IS NULL UNION ALL
> | SELECT c.idposting, c.fkidpostingparent, p.fkidpostingthread FROM posting AS c JOIN RecursivePostings AS p ON c.fkidpostingparent = p.idposting)
> | SELECT * FROM RecursivePostings;

Es handelt sich um knapp 19 Mio. Datensaetze. Ich habe den Versuch jetzt
nach knapp 2 Tagen abgebrochen (ohne dass die Maschine in dieser Zeit etwas
anderes zu tun gehabt haette)...

> Bei mir benötigt das für etwas mehr als drei Millionen Testreihen:
> [...] ungefähr fünf Minuten (inklusive Formatierung in psql und Anzeige
> in Emacs).

Fuer die Testreihe benoetigt die gleiche Maschine knapp 25 Sekunden.

Einerseits ist der Test nicht wirklich repraesentativ, da die maximale
Threadtiefe real irgendwo um 1.000 herum liegen duerfte.

Andererseits duerfte der Index inzwischen die Cache-Groesse sprengen -
wiederholte Aufrufe sind um keinen Deut schneller, als der erste (alleine
das initiale SELECT aus Deiner rekursiven Abfrage benoetigt dabei fuer 2
Mio. gefundene Kopfpostings rund 3 Minuten).

Und drittens ist moeglicherweise die Konfiguration von PostgreSQL nicht
wirklich gut; um das einschaetzen zu koennen, verwende ich das Teil erst zu
kurz.

Servus,
Stefan

--
http://kontaktinser.at/ - die kostenlose Kontaktboerse fuer Oesterreich
Offizieller Erstbesucher(TM) von mmeike

Stefan. Für riesige Fesseln, wenn's mal nicht läuft!
(Sloganizer)

Bernd Nawothnig

unread,
Nov 20, 2012, 11:27:55 AM11/20/12
to
On 2012-11-19, Stefan Froehlich wrote:
>> [...] generell sah mir das nach einem Fall für die DEFERRABLE INITIALLY
>> DEFERRED Klausel aus, denn warum erst eine Spalte beim INSERT weglassen,
>> nur um sie sofort anschließend via UPDATE nachzupflegen?
>
> Ah, "das alles" hatte ich nur auf die iterierten UPDATEs bezogen.
>
> Die Gesamtheit von INSERT und UPDATE in eine Transaktion zu packen,
> waere zwar methodisch korrekt, aber praktisch vollkommen witzlos:
> bis die 18 Mio. Datensaetze eingelesen waren, hat es mehrere Wochen
> gedauert. Eine Transaktion so lange offenzuhalten, erscheint mir
> nicht mehr sehr sinnvoll.

Gut, 18 Millionen in einem Rutsch wären in der Tat etwas kühn. Aber es
hängt schon davon ab, was man mit den Daten anstellen will. Denn nach
Weglassen der einen Spalte erscheinen sie mir nicht wirklich
benutzbar, also inkonsistent zu sein.

Wenn die zeitweilige Unbenutzbarkeit bereits sichtbarer Zeilen Ok ist,
mag man das anders angehen, aber dessen sollte man sich immerhin
bewusst sein.

Ein Ausweg wäre es, kleinere Häppchen z.B. über Dictionaries zu
ermitteln (alle Foreign-Keys jeder Teilmenge müssen vorhanden sein)
und in jeweils einer Transaktion einzufügen.

Zumindest in SQLite3 gilt, dass man grundsätzlich wenige, dafür lange
Transaktionen verwenden sollte. Zu häufige Commits sind ein übler
Performancekiller dort.

Siegfried Schmidt

unread,
Nov 20, 2012, 3:40:46 PM11/20/12
to
Bernd Nawothnig schrieb:

> Zumindest in SQLite3 gilt, dass man grundsÀtzlich wenige, dafÌr
> lange Transaktionen verwenden sollte. Zu hÀufige Commits sind ein
> ÃŒbler Performancekiller dort.

Der muss sich ja auch der konkurrierenden Zugriffe erwehren.

Siegfried

Bernd Nawothnig

unread,
Nov 20, 2012, 4:10:02 PM11/20/12
to
On 2012-11-20, Siegfried Schmidt wrote:
>> Zumindest in SQLite3 gilt, dass man grundsätzlich wenige, dafür
>> lange Transaktionen verwenden sollte. Zu häufige Commits sind ein
>> übler Performancekiller dort.
>
> Der muss sich ja auch der konkurrierenden Zugriffe erwehren.

Das kann nicht der Grund gewesen sein, weil es die nicht gab.

Ich schätze, das lag mehr am dort verwendeten Rollback Journal, wo mit
jeder neuen Transaktion eine Kopie des Datenbankfiles angelegt wird.
Der Performanceeinbruch ist umso mehr spürbar, je größer die Datenbank
und damit diese Datei ist. Bewegt man sich erstmal innerhalb einer
Transaktion ist alles aber rattenschnell. Auch ein Rollback kostet so
gut wie nichts.

Mit WAL, also Write Ahead Log wie bei PostgreSQL, was es ja ab SQLite
3.7 auch gibt, habe ich es nicht getestet, aber das sollte sich
merklich anders verhalten.

Siegfried Schmidt

unread,
Nov 20, 2012, 4:33:39 PM11/20/12
to
Bernd Nawothnig schrieb:

> Ich schÀtze, das lag mehr am dort verwendeten Rollback Journal, wo mit
> jeder neuen Transaktion eine Kopie des Datenbankfiles angelegt wird.

Glaube ich nicht - mit so einer Vorgehensweise gäbe es keine brauchbaren
Datenbanken im GB-Bereich.


Siegfried

Bernd Nawothnig

unread,
Nov 20, 2012, 5:24:09 PM11/20/12
to
On 2012-11-20, Siegfried Schmidt wrote:
>> Ich schätze, das lag mehr am dort verwendeten Rollback Journal, wo mit
>> jeder neuen Transaktion eine Kopie des Datenbankfiles angelegt wird.
>
> Glaube ich nicht - mit so einer Vorgehensweise gäbe es keine brauchbaren
> Datenbanken im GB-Bereich.

Es ist aber so:

=====================================================================

4.0 The Rollback Journal

When a process wants to change a database file (and it is not in WAL
mode), it first records the original unchanged database content in a
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
rollback journal.
^^^^^^^^^^^^^^^^

=====================================================================

Und Du kannst das auch an der Größe der neu angelegten Datei sehen.

Genau aus dem Grund bieten sie ja jetzt auch WAL an, zumindest ist das
einer der Gründe.

Stefan Froehlich

unread,
Nov 20, 2012, 6:56:36 PM11/20/12
to
On Tue, 20 Nov 2012 17:27:55 Bernd Nawothnig wrote:
> > Die Gesamtheit von INSERT und UPDATE in eine Transaktion zu
> > packen, waere zwar methodisch korrekt, aber praktisch vollkommen
> > witzlos: bis die 18 Mio. Datensaetze eingelesen waren, hat es
> > mehrere Wochen gedauert. Eine Transaktion so lange
> > offenzuhalten, erscheint mir nicht mehr sehr sinnvoll.

> Gut, 18 Millionen in einem Rutsch wären in der Tat etwas kühn.
> Aber es hängt schon davon ab, was man mit den Daten anstellen
> will. Denn nach Weglassen der einen Spalte erscheinen sie mir
> nicht wirklich benutzbar, also inkonsistent zu sein.

Inkonsistent sind sie nicht, es fallen nur alle
Abfragemoeglichkeiten weg, die sich auf Threads beziehen; andere
Suchen sind auch ohne dieses eine Feld moeglich.

Dazu kommt, dass das ohnehin ein einmaliger Initialimport war -
laufende Updates benoetigen weit weniger Ressourcen und koennen dann
auch leicht in einer Transaktion gekapselt werden.

Servus,
Stefan

--
http://kontaktinser.at/ - die kostenlose Kontaktboerse fuer Oesterreich
Offizieller Erstbesucher(TM) von mmeike

Die Macht zu verführen! Stefan, wenn das Pech dich verfolgt!
(Sloganizer)
0 new messages