Speicherverbrauch und Geschwindigkeit von Indexen

werdas34

Benutzer
Beiträge
12
Hallo,

ich hätte paar allgeine Fragen zu den Indexen, die ich im Internet leider nicht beantwortet finde.

Indexe erlauben bei richtiger Nutzung einen erheblichen Geschwinigkeitsvorteil beim ausführen von Abfragen, mithilfe eines Indexbaumes.
1) Wie viel Speicherplatz zusätzlich braucht ein Indexbaum wenn die Tabelle 100/200/500 Millionen Einträge hat? (Mir bewusst, das es immer vom konkreten Fall abhängt. Aber mit was kann man rechnen? Was sind Erfahrungswerte?)
2) Kann man Aussagen treffen, wie viel schneller die Abfragen von statten gehen? Im Internet fand ich nur "theoretisch 100fach schneller".
3) Insert, Update und Delete Operationen dauern länger. Kann man da Aussagen treffen, in der Form, wenn die Tabelle 100 Millionen Einträge hat, dann braucht eine solche Operation 1,5mal länger als ohne Verwendung eines Index.

Wir arbeiten mit einer Postgres Datenbank, falls das wichtig ist.

mfg werdas34
 
Werbung:
Wir arbeiten mit einer Postgres Datenbank, falls das wichtig ist.

Ja, ist wichtig.

a) das hängt von der Art des Indexes (BTREE/GIN/GiST/BRIN/...) und den Datentypen ab. Ein BRIN-Index z.B. ist EXTREM klein. Mein Freund ads hat einen nützlichen Blog-Post dazu: table size, database size | ads' corner Schon wieder bald 14 jahre alt, Kinder, wie die Zeit vergeht ...
b) Faktor 100 und mehr sehe ich oft in der Praxis. Hängt aber von sehr vielen Dingen ab. Bei einer kleinen Tabelle mit wenigen Rows hast Du eher wenig Gewinn.
c) depends

Da Du PG hast (vielleicht kann @Walter das passend verschieben): beschäftige Dich mit EXPLAIN und dem Kostenmodell. Via Google findest Du da viel, vielleicht sogar Videos von mir ...
 
Ich würde noch ergänzen, dass es auch von der Befüllung abhängt. Bei einem Primäschlüsselindex sicher nicht, aber bei allen anderen Indizierungen. Da spielen so Sachen wie Häufigkeiten von Dubletten, NULL Werte, Feldlängen eine Rolle.
Einfach an einem bestehenden System mit representativen Daten ausprobieren. Dann kann man die Indexgröße abfragen, die Performanceverhalten messen.
zu C) Grunsätzlich zu unterscheiden ist Schreib und Leselast in einem System. Klar ist, Indizierung beschleunigt nur das Lesen, es verlangsamt das Schreiben. Dann ist die Frage, wieviel Indizes auf einer Tabelle liegen. Dann kann man sich noch fragen ob und wo heute überhaupt noch gelöscht wird. Id.r wenig bis gar nicht, fällt also als Problembereich weg. Wenn nicht, kann es sehr speziell sein.
 
zu C) Grunsätzlich zu unterscheiden ist Schreib und Leselast in einem System. Klar ist, Indizierung beschleunigt nur das Lesen, es verlangsamt das Schreiben.

Reale Kundenanfrage: "Alle Abfragen sind schnell, aber alle INSERT/UPDATE/DELETE are slow". Nach Untersuchung stellte sich raus: Tabelle mit ca. 1000 Spalten, auf allen Spalten ein Index. Indexnutzung (siehe pg_catalog.pg_stat_user_indexes, idx_scan) bei 999 Spalten gleich 0. Auf die Frage, warum so viele Indexe: 'old school, all colums should have an index'. Tischkante massiv verbissen.
 
Danke für die Antworten. Dann habe ich am Wochenende was zu tun.

Vielleicht könnt ihr mir gute Tipps geben.
Das ganze ist ein Studentenprojekt. Wir haben eine Tabelle, die 70% der Server-Kapazität frisst. Einfach Server Aufstockung ist nicht drin, da Studenten sicher eine kreative Lösung finden werden. Daher auch die Frage, wie viel Speicher der Index zusätzlich braucht. Wir müssten sowieso Daten löschen für den Index, aber wollen auch nicht alles löschen und dann den Index erstellen. Wenn er nichts bringts könnte man den Index ja wieder entfernen.

Tabelle dient zum Trainieren eines Neuronalen Netzes. Sind da aber noch am Anfang.
Tabelle sieht in etwa so aus.
id(bigint) sensor_id(int), value_type(varchar), value(numeric), time(time). date(date) (müsste alles PK sein, da die Quelle von der wir die Daten bekommen, es nicht schafft eindeutige IDs zu vergeben)
Sind Sensorwerte und deren Timestamp plus Angabe welche Art von Wert das ist, z.B. Temperatur.

Bräuchten für das Netz dann die sensor_id und den value_type(Temperatur). Gegebenefalls noch Zeitpunkt. Entweder beides oer nur time.
Also in der Art:
Code:
CREATE INDEX ON sensordatavalues (sensor_id, value_type, time DESC);

Dazu wird jede halbe Stunde ca. 100.000 bis 150.000 neue Daten geschrieben. Also die Inserdauer darf nicht plötzlich um das fünfache steigen. Das könnte dann evtl zum Problem werden.

mfg werdas34
 
  • warum 2 Spalten, time und date?
  • warum PK über alle Spalten? Da ist doch die ID-Spalte, die kann doch PostgreSQL selber vergeben
  • Index sollte zu den Abfragen passen, diese kennen wir nicht
  • wenn jede halbe Stunde 100.000+x Records geschrieben werden, nach welcher Regel werden Daten gelöscht?
  • eventuell böte sich Partitionierung an
  • insbesondere wenn nie Daten gelöscht werden bzw. wenn immer nur ganze Partitions gelöscht werden und die Daten nach dem timestamp sortiert sind böte sich ein BRIN-Index auf dem Timestamp an - ist super klein
  • wie erfolgen die Inserts? Per single Insert, per COPY?

das sind mal so grob ein paar Gedanken ...
 
Ich kann nur vom jetztigen Stand berichten. Es sind aber bereits Optimierungen an der Datenbank geplant.

time und date wurden aufgeteilt, weil man dachte wenn man gezielt mach einer Zeitspanne (12 Uhr bis 15 Uhr) sucht, geht das besser wenn es aufgeteilt ist. Ich glaube bei Postgres wird da nicht anders verfahren, wenn date und time in einer Spalte sind. Hätte man sich sparen können. Aber man ist im Nachhinein immer schlauer.

Die IDs die wir von der Quelle bekommen sind nicht eindeutig. So war es damals das sinnvollste alles auf PK zu setzen.

Anfagen sollten in diese Richtung gehen:
Code:
SELECT
    *
FROM
    sensordatavalues
WHERE
    sensor_id = 19546
    AND value_type = 'temperature'

Daten werden gelöscht, sobald ein Schwellwert anhand der Servergröße erreicht wurde. D.h. ist der Speicher vom Server zu 80% belegt werden, die ältesten Daten gelpöscht. (In Zukunft sollte das vermieden werden, indem die Daten bereits im Netz sind oder irgendwie komprimiert abgespeichert wurden.)

Insert erfolgen per single insert. Da kann es aber auch noch Veränderungen geben. Stand jetzt ist nur ein Cache lokal geplant, der paar Datensätze puffert und die DB Zugriffe minimiert.
 
Gut, okay.

Wenn Du ein TIMESTAMP - Feld hast und Abfragen nach z.B. Datensätzen zwischen 12 und 13 Uhr sind, kannst Du einen funktionalen Index nutzen.

Code:
edb=# create table zeiten(id int generated always as identity, ts timestamp);
CREATE TABLE
edb=*# insert into zeiten (ts) select '2010-01-01'::timestamp + random()*100000 * '1second'::interval from generate_series(1,10000000) s;
INSERT 0 10000000
edb=*# create index idx_hour on zeiten ((extract (hour from ts)));
CREATE INDEX
edb=*# explain analyse select * from zeiten where extract(hour from ts) between 12 and 13;
                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on zeiten  (cost=1064.93..57250.47 rows=50000 width=12) (actual time=97.134..286.806 rows=719529 loops=1)
   Recheck Cond: ((date_part('hour'::text, ts) >= '12'::double precision) AND (date_part('hour'::text, ts) <= '13'::double precision))
   Heap Blocks: exact=54055
   ->  Bitmap Index Scan on idx_hour  (cost=0.00..1052.43 rows=50000 width=0) (actual time=85.893..85.893 rows=719529 loops=1)
         Index Cond: ((date_part('hour'::text, ts) >= '12'::double precision) AND (date_part('hour'::text, ts) <= '13'::double precision))
 Planning Time: 0.398 ms
 Execution Time: 325.533 ms
(7 rows)

Das sind übrigens die Größen:

Code:
edb=*# select pg_size_pretty(pg_total_relation_size('zeiten'));
 pg_size_pretty
edb=*# explain analyse select * from zeiten where ts = '2020-01-01'::timestamp;
                                                      QUERY PLAN                                                     
----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on zeiten  (cost=16.03..43043.92 rows=1 width=12) (actual time=0.281..0.282 rows=0 loops=1)
   Recheck Cond: (ts = '01-JAN-20 00:00:00'::timestamp without time zone)
   ->  Bitmap Index Scan on idx_brin  (cost=0.00..16.03 rows=23640 width=0) (actual time=0.278..0.278 rows=0 loops=1)
         Index Cond: (ts = '01-JAN-20 00:00:00'::timestamp without time zone)
 Planning Time: 0.269 ms
 Execution Time: 0.322 ms
(6 rows)

----------------
 637 MB
(1 row)

edb=*# select pg_size_pretty(pg_total_relation_size('idx_hour'));
 pg_size_pretty
----------------
 215 MB
(1 row)

Im Beispiel waren die zeiten ja per random() erzeugt, ich erzeuge dieselbe Tabelle noch mal, aber mit aufsteigenden Zeiten, und einen BRIN-Index:

Code:
edb=# create table zeiten(id int generated always as identity, ts timestamp);
CREATE TABLE
edb=*# insert into zeiten (ts) select '2010-01-01'::timestamp + s * '1second'::interval from generate_series(1,10000000) s;
INSERT 0 10000000
edb=*# create index idx_brin on zeiten using brin (ts);
CREATE INDEX
edb=*# select pg_size_pretty(pg_total_relation_size('idx_brin'));
 pg_size_pretty
----------------
 56 kB
(1 row)

Diese Index ist EXTREM klein, wie man sieht. Nun eine passende Abfrage:

Code:
edb=*# explain analyse select * from zeiten where ts = '2020-01-01'::timestamp;
                                                      QUERY PLAN                                                     
----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on zeiten  (cost=16.03..43043.92 rows=1 width=12) (actual time=0.281..0.282 rows=0 loops=1)
   Recheck Cond: (ts = '01-JAN-20 00:00:00'::timestamp without time zone)
   ->  Bitmap Index Scan on idx_brin  (cost=0.00..16.03 rows=23640 width=0) (actual time=0.278..0.278 rows=0 loops=1)
         Index Cond: (ts = '01-JAN-20 00:00:00'::timestamp without time zone)
 Planning Time: 0.269 ms
 Execution Time: 0.322 ms
(6 rows)


Wenn Du den PK auf alle Spalten setzt, wirst Du übrigens einen recht großen Index erhalten, und er wird nicht für Abfragen nützlich sein, weil das erste Feld des Indexes (vermutlich) die ID ist und Deine Abfrage NICHT den Wert der ID im WHERE hat.
Wenn Du sensor_id und value_type vorne im Index hast, wäre der Index für die von Dir gezeigte Abfrage verwendbar.
Single Inserts sind langsam, wenn Die Daten z.B. als CSV vorliegen wäre ein COPY deutlich schneller. Wenn ihr immer mal wieder die ältesten Daten löscht, wäre Partitionierung sinnvoll, das würde dann auch Probleme mit Bloat vermeiden.
 
Es kann sein das ich grad was falsch verstehe.
Index soll auf die Spalten angewendet werden, die in der where-Klausel vorkommen. Die Spalte ID ist (immer) PK. Aber ID kommt nicht in der where Klausel vor. Dann dürfte ein Index nie nützlich sein. Da ID nie Teil der where-Klausel ist. Oder verstehe ich das gerade falsch?
Und was ist mit "vorne im Index" gemeint? Spielt die Reihenfolge eine Rolle?

COPY ist tatsächlich eime gute Idee. Nur sind die Daten, die wir bekommen teilweise nicht konform zur Tabelle. D.h. wir bekommen varchar Werte, die in eine numeric Spalte gehören. Das funktioniert so nicht. Da die Quelle aus einen Mix von SQL und NonSQL Datenbanken besteht. So bekommen wir ein JSON was nicht (immer) zur Zieltabelle passt. Ich weiß nicht, ob man das mit COPY so gut selektieren kann. Auch wenn IDs doppelt vorkommen, dann diese nicht in die DB zu schreiben.
 
Tatsächlich macht ein Index nur Sinn auf Spalten, die in der Abfrage als Where Kriterium dienen.

Wenn Ihr sparsam sein müsst, lohnt sich bei jedem Feld eine gründliche Überlegung nach dem kleinsten Datentyp. Bei kleinen Tabellen macht das keinen Sinn, aber bei > 100T und limitierten Ressourcen sehr wohl.

Dabei kann man durchaus soweit gehen und die reale Genauigkeit von Fließkomma Sensordaten berücksichtigen und entsprechend runden. Eine KI würde zwar auch lernen, überflüssige Details zu ignorieren, aber man muss ja nicht mehr reinschieben, als real ist.

Möglichst kleine Datentypen für Floats kann man dann ggf. auf Datenbankseite auch per Integer Typ erreichen indem man einfach nur die signifikanten Stellen speichert und (irgendwo) im Code die Kommaverschiebung für I/O macht. Der KI ist es sicher egal.

Auch so was wie „where value_type = ‘temperature‘“ ist natürlich Luxus, wenn es um Speicherplatz geht. Die Typen (und auch andere Texte) kann man in Zahlen codieren (Und wenn man mag, ne kleine Nachschlagetabelle dazu bauen).

Man könnte soweit gehen und verschiedene Merkmale in ihren (möglichen) Kombinationen binär zu kodieren. Damit kann man in 2, 4 oder 8 Byte viele Daten unterbringen. Für die Eingabe/Ausgabe kann man es dann auch wieder augenfreundlicher handhaben (falls nötig).

Hier müsste man im Unterschied zu den Messwerten sicher darauf achten, dass die KI keine (gefilterten) Kriterien in die Finger bekommt...


Insert Performance bekommt ihr mit COPY auf jeden Fall, wenn die gebraucht wird. Wegen der Speicherlimitierung und der benötigten Umformung, muss man den Input vielleicht stückeln. Während der COPY Aktion wären die Daten ja 2x im Speicher.

Alternativ könnte man mit Foreign Data Wrapper arbeiten und entweder die CSV Daten anzapfen oder direkt die Original Quellen aus noSQL, JSON, .. dann hat man keine (kurzzeitige) Datendoppelung und kann trotzdem bequem und schnell per SQL transformieren.


Und mal ganz abseitig: Wenn ihr stumpf Trainingsdaten pumpt anhand einiger Kriterien und es keine relationalen Bezüge zu verschiedenen Tabellen gibt und nur per WHERE die Messdaten abgefragt werden .. vielleicht was vergessen.. dann braucht ihr nicht mal einen Primärschlüssel.


Und klar: kleinere Feldtypen wirken sich natürlich auch auf die Indexgröße aus.
 
Richtig, der PK stellt mit Hilfe eines Unique Index Eindeutigkeit sicher und schnellen Zugriff.

Meine Anmerkungen zur Indizierung und dem möglichen Auslassen des PK bezogen sich auf diesen speziellen Fall hier und sind ohne Kenntnis des Datenmodells und geplanter Nutzung auch spekulativ. Ohne weitere relationale Tabellen -danach sieht es hier aus- sinkt der Nutzen eines PK u.U. erheblich. Das wäre bei Messwerten nicht ungewöhnlich. Hier kommt es häufig nur auf zeitliche Abfolge und Quelle an, z.B.
 
Und was ist mit "vorne im Index" gemeint? Spielt die Reihenfolge eine Rolle?

Ja. Neuere Versionen von PG nutzen auch Indexe auf z.B. die Spalten a, b, c bei einer Where-Condition nur auf b und c, aber generell spielt die Reihenfolge im Index eine Rolle. Wenn a nicht im Where ist kann es passieren, daß der Index nicht genutzt wird.
 
Werbung:
Danke für die Hilfe.

Wir haben die Datenbank umdesignt bzw. wir sind grade dabei.

Für das Backup, damit das schneller geht nutzen wir grad BRIN.
Für das neue Design macht BRIN auch sehr viel Sinn.
Nur was ich nicht mehr finde. Als BRIN in PG 9.5 eingeführt wurde, musste man den Index regelmäßig manuell updaten (reindex?).
Wie sieht das in PG 12 aus?
Ich meine in einer Quelle gelesen zu haben, dass das bei späteren Versionen, das automatisiert von statten geht. Nur finde ich diese Quelle nicht mehr.

Muss man in PG 12 den BRIN Index nach dem Erstellen noch weiter pflegen oder macht das PG selbst?
 
Zurück
Oben