JOIN großer Tabelle

hallo.

ich habe eine tabelle mit 32 gb und eine mit ca 100 mb. nach dem join dieser beiden tabellen und diverser berechnungen ist diese tabelle danach fast 300 gb groß.
nach einer weiteren Berechnung werden die Inhalte auf 3 Spalten aggregiert. Zwei character Spalten und einer numeric (bis zu 20 dezimalstellen). Dabei hat die Tabelle aber immer noch ca 170 gb.
Ist das normal? Kann man das irgendwie reduzieren? Das beansprucht natürlich immer viel Zeit bei jedem Join.

meine Frage ist nun, warum ist diese Tabelle immer noch so groß wenn es nur 3 spalten beihaltet jedoch ca 10 mio * 99 einheiten hat.

lg, tw

Kannst Du uns bitte die entsprechenden CREATE TABLE Befehle und das SELECT zeigen?

schwer nachvollziehbar …

nach einem Select mit eine JOIN (was für ein Join?) erhälst Du eine Ergebnisstabelle. Speicherst Du diese, und diese hat dann 300GB? Erfolgen die Berechnungen in dieser Tabelle dann, via UPDATE? Läuft Autovacuum?

Und die Fragen von @castorp bitte beantworten.

Also ich habe folgendes gemacht und diese Tabelle auch abgespeichert (172gb):

CREATE TABLE DH_appln_nace2 AS
SELECT distinct a.appln_id, a.w08_2
,sum(a.sum)/p.count_ipc_appln DH_nace2_weight
FROM appln_weight a
join (select appln_id, count_ipc_appln from count_ipc_appln
) p on p.appln_id=a.appln_id

group by a.appln_id, a.w08_2, p.count_ipc_appln
order by a.appln_id, a.w08_2;

appln_weight ist eine Tabelle mit mehreren IDs; darin enthalten ist die ID (appln_id), w08_2 (Sektor = mehrere für jede ID)
count_ipc_appln ist eine Tabelle mit counts (3 spalten); ID, count von Klassen je ID, distinct count von Klassen

Das Ergebnis in dieser Tabelle schaut dann so aus:

ID w08_2 weight
1 Sektor1 0.000123
1 Sektor2 0.0000123

IDs habe ich an die 10 mio und es können bis zu 99 Sektoren je ID sein. Für jede ID*Sektor ein weight.

Nein, autovacuum läuft nicht.

Ich möchte diese Tabelle dann für weitere Abfragen verwenden.

Benötigt ihr noch weitere Informationen?

gut, diese Tabelle hat nun also 172GB. Erst war von 300GB die Rede. Führst Du in dieser Tabelle nun Berechnunges aus, und speicherst die in dieser Tabelle? Also via UPDATE? Das könnte erklären, wenn diese dann 300GB hat, zumal nach Deiner Aussage Autovacuum nicht läuft.

Warum eigentlich nicht?

Warum hast Du das abgeschaltet. Das ist ein ganz großer Fehler.

Hmm…
Also hat die Tabelle rund 100 Millionen Datensätze. Ein Datensatz sind rund 75 bytes bei den drei Spalten (unter der Annahme, dass “weight” ein “double” ist und w08_2 im Schnitt 15 Zeichen lang ist).

Da komme ich aber eher auf 7GB als auf 300GB. Wie genau hast Du die 300GB ermittelt?

Das Resultat von explain (analyze, buffers, format text) Deines SELECTs wäre mal interessant. Bitte sorge aber dafür, dass die Ausgabe hier als “code” formatiert wird (z.B. durch Einrücken jeder Zeile mit 4 Leerzeichen) - oder lade es direkt nach https://explain.depesz.com/ hoch.

Also die 172 gb wurden aus der 300gb tabelle appln_weight erstellt.
Jedoch ist das auch noch relativ groß für einen weiteren join.

In dieser Tabelle werden dann keine weiteren Berechnungen gemacht. Deshalb auch meine Frage ob ich diese nicht verkleinern kann da sie ohne hin nur character und numeric beinhaltet. Das würde die folgenden Joins sicher schneller machen.

Habe das autovacuum und track_on aktiviert.

Es sind 1000 Mio Datensätze oder? ich habe 10 mio ids * 99 Sektoren also 10*100 = 1000.

Dachte das autovacuum bei default aktiv ist und nicht aktiviert werden muss bei den neueren Versionen.

Ups, ja natürlich. Aber das ergibt trotzdem nur rund 80GB, nicht 300GB oder 170GB.

Wie genau (mit welcher Abfrage) hast Du die Größe der Tabelle gemessen?

Ist es auch, aber Du hast ja geschrieben, dass Autovacuum nicht läuft - deswegen sind wir davon ausgegangen, dass Du das aus irgendeinem Grund ausgeschaltet hast.

Autovacuum ist nun im postgre.conf aktiv und nicht auskommentiert.

Die Groesse der Tabelle habe ich aus dem pgadmin ausgelesen (Table size).

Ich weiß es ist schwierig das von außen zu beurteilen. Habe diese Tabellen eigentlich erstellt um die weiteren Joins zu beschleunigen. Aber bei 170 gb ist das wo möglich schwierig…

Wie gesagt: Query tuning fängt immer bei der Analyse des Execution Plans an. explain (analyze, buffers, format json) select ... und dann die Ausgabe hier als formatierten Text posten oder nach https://explain.depesz.com/ hochladen und den Link hier posten.

Das DISTINCT scheint mir doch unnötig zu sein, da Du ja schon ein GROUP BY verwendest.

Dass p.count_ipc_appln Teil des GROUP BY ist, ist irgendwie merkwürdig - eine Spalte die “count” heisst, in einem GROUP BY …

Kann es sein, dass Du die Spalte nur aufgenommen hast, weil sonst ein Fehler passiert?

Das ORDER BY würde ich ganz weglassen. Letztendlich musst Du beim Lesen aus der Tabelle sowieso ein ORDER BY verwenden wenn Du eine garantierte Sortierreihenfolge haben willst.

Ich frage mich ob das hier vielleicht schneller ist (bin mir aber nicht 100%ig sicher, ob es das ist was Du willst).

SELECT a.appln_id, a.w08_2, a.total_sum / p.count_ipc_appln as dh_nace2_weight
FROM count_ipc_appln p
  JOIN (
   SELECT a.appln_id, a.w08_2, sum(a.sum) as total_sum
   FROM appln_weight 
   group by a.appln_id, a.w08_2
  ) a ON p.appln_id = a.appln_id;