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.
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?
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.
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 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.
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;