PostgreSQL 9.5 Tabellen ohne Indices

vogste

vogste

Benutzer
Beiträge
23
Hallo zusammen,

heute mal ein Frage aus Unwissenheit. Als ORACLE DB Admin bin ich gewohnt Tabellen zu indizieren.
Jetzt habe ich folgendes Problem: Bei uns ist eine Datenbank mit 14 Tabellen, zwei Tabellen haben alleine über 44 Millionen Zeilen, im Einsatz.

Diese 14 Tabellen stehen untereinander mittels Primär und Fremschlüssel in Beziehung.

Einfache Abfragen dauern teils 5-10 min. Selbst ein einfacher Count auf den Primärschlüssel einer der beiden Tabellen dauert bei der einen Tabelle 1min und bei der zweiten Tabelle 2 min.
Jetzt war ich so blauäugig und habe mal die Indices auf das Schema abgefragt und als Rückmeldung bekommen, dass es keine Indices gibt.

Wie seht Ihr das, sollten oder müssen solch grosse Tabellen indiziert sein.

Danke für Eure Rückmeldungen.

Gruss
Stephan
 
Werbung:
Primary Keys erhalten automatisch einen Index, ansonsten hast Du keine. Select count(*) ohne Where-Bedingung erfordert (fast) immer einen kompletten Tablescan, weil PostgreSQL im Index keine Informationen über die Sichtbarkeit eines Datenstzes in unterschiedlichen Transaktionen hat. Sollte Dir ein Schätzwert reichen kannst Du relpages aus pg_class auslesen.

5-10 Minuten ist arg lang, was sagt ein Explain analyse?
 
Hallo AKretschmer,

Danke für die Rückmeldung!

Das mit den Primary Keys als automatischer Index, war mir so nicht bewusst. Danke für die Aufklärung.

relpages auf die Tabelle mit 44.415.626 Rows gibt 607.736 zurück.

Die Explain Analyse gibt folgendes zurück.
"Aggregate (cost=1191511.88..1191511.89 rows=1 width=8) (actual time=111798.810..111798.811 rows=1 loops=1)"
" -> Seq Scan on raw_usage_measure (cost=0.00..1080632.30 rows=44351830 width=8) (actual time=0.050..75474.975 rows=44415626 loops=1)"
"Planning time: 0.113 ms"
"Execution time: 111798.876 ms"

Mir sagt aber beides nicht wirklich was.

Danke und Gruss
Stephan
 
Erklärung: relpages ist die Anzahl von 8KB-Blöcken, die die Tabelle auf Platte belegt. Reltuples die (geschätzte) Anzahl von Zeilen.

Diesen Schätzwert findest Du im Explain wieder (rows=44351830), die exakte Anzahl ist dann in Klammern als actual ... rows = 44415626). Ansonsten, wie gesagt, sieht man einen Seq-Scan, also das sequentielle und komplette durchlesen der Tabelle und Prüfung, ob jeder einzelne Datensatz in dieser Transaktion sichtbar ist oder nicht. Sollte Dir die grobe Schätzung reiche, dann reltuples.

Falls nein: manche stricken sich einen TRIGGER, der die aktuelle Zeilenzahl zählt.
 
Hallo AKretschmer,

mein Problem ist, dass ich mit einem BI Tool (Tableau) auf die Datentabellen zugreifen muss und dort beispielsweise alle Daten eines Start- und Enddatums zusammen sammeln muss, um diese dann grafisch aufzubereiten. Nach dem setzen des Start und Enddatums läuft es also ca. 10 Minuten bis die Daten angezeigt werden.

Allerdings habe ich gerade festgestellt, dass die PostgreSQL auf einer Workstation Windows 7 mit 8 GB RAM läuft. Ich denke das dies auch für die Antwortzeiten verantwortlich ist.

Danke und Gruss
Stephan
 
Btw.: Du hast noch 9.5. Seit 9.6 (aktuell ist 10) kann PostgreSQL Abfragen auf mehrere Cores verteilen. Bringt richtig was, z.B. auch bei solchen Abfragen.
 
vogste schrieb:
Hallo AKretschmer,

mein Problem ist, dass ich mit einem BI Tool (Tableau) auf die Datentabellen zugreifen muss und dort beispielsweise alle Daten eines Start- und Enddatums zusammen sammeln muss, um diese dann grafisch aufzubereiten. Nach dem setzen des Start und Enddatums läuft es also ca. 10 Minuten bis die Daten angezeigt werden.

Allerdings habe ich gerade festgestellt, dass die PostgreSQL auf einer Workstation Windows 7 mit 8 GB RAM läuft. Ich denke das dies auch für die Antwortzeiten verantwortlich ist.

Danke und Gruss
Stephan
Zum Vergrößern anklicken....

In solchen Fällen wirst Du eher nicht wirklich 44 Millionen Zeilen benötigen, um diese anzuzeigen. Da ist doch bestimmt eine Eingrenzung auf bestimmte Datensätze im Spiel, oder?

Ist das PG an die Hardware angepaßt? Shared Buffers etc.?
 
Hallo AKretschmer,

das kann ich nicht mit Bestimmtheit sagen! Vermutlich nein!

Auffällig ist halt, dass das BI Tool mit der Oracle DB 1A läuft, auch mit grossen Datenmengen. Selbst mit Microsoft SQL-Server und grossen Datenmengen kein Problem nur wenn ich auf die PostgreSQL z.B. auf das Datum Startzeit / Endzeit eingrenze, kann ich Kaffee trinken gehen.
Leider habe ich keinen Ansatz wie ich die Zeiten eingrenzen kann. Aber ich denke, jetzt ist das ein Fall für den Dienstleister der DB.

Wenn Du schreibst, dass es keine expliziten Indices gibt wie bei Oracle Tabellen sollen sich andere Gedanken über die Problematik machen.

Danke für Deine Unterstützung.

Gruss
Stephan
 
Dann fehlen sehr wahrscheinlich Indexe auf Start/Endzeit. Ohne Indexe Seq-Scan, mit Index oder Bitmap Index oder vergleichbar bessere Methoden.
45Millionen Rows ist eher peanuts.
 
Werbung:
Zeiten auf meinen Laptop, Tabelle mit 45 Millionen Zeilen

Code: 
test=# set max_parallel_workers_per_gather = 0;
SET
test=# explain analyse select count(1) from demo;
  QUERY PLAN   
--------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=805859.40..805859.41 rows=1 width=8) (actual time=5465.547..5465.547 rows=1 loops=1)
  ->  Seq Scan on demo  (cost=0.00..693359.32 rows=45000032 width=0) (actual time=0.082..2896.762 rows=45000000 loops=1)
 Planning time: 0.086 ms
 Execution time: 5465.595 ms
(4 rows)

test=# set max_parallel_workers_per_gather = 2;
SET
test=# explain analyse select count(1) from demo;
  QUERY PLAN   
-----------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=478734.38..478734.39 rows=1 width=8) (actual time=2191.090..2191.090 rows=1 loops=1)
  ->  Gather  (cost=478734.17..478734.38 rows=2 width=8) (actual time=2191.080..2191.084 rows=3 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Partial Aggregate  (cost=477734.17..477734.18 rows=1 width=8) (actual time=2186.443..2186.443 rows=1 loops=3)
  ->  Parallel Seq Scan on demo  (cost=0.00..430859.13 rows=18750013 width=0) (actual time=0.026..1210.494 rows=15000000 loops=3)
 Planning time: 0.114 ms
 Execution time: 2195.180 ms
(8 rows)

test=# set max_parallel_workers_per_gather = 4;
SET
test=# explain analyse select count(1) from demo;
  QUERY PLAN   
---------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=384984.52..384984.53 rows=1 width=8) (actual time=1682.465..1682.466 rows=1 loops=1)
  ->  Gather  (cost=384984.10..384984.51 rows=4 width=8) (actual time=1680.185..1682.458 rows=5 loops=1)
  Workers Planned: 4
  Workers Launched: 4
  ->  Partial Aggregate  (cost=383984.10..383984.11 rows=1 width=8) (actual time=1675.315..1675.315 rows=1 loops=5)
  ->  Parallel Seq Scan on demo  (cost=0.00..355859.08 rows=11250008 width=0) (actual time=0.024..935.583 rows=9000000 loops=5)
 Planning time: 0.130 ms
 Execution time: 1683.136 ms
(8 rows)

test=#

[code]


Gut zu sehen, daß mit mehr worker-Prozessen die Zeit besser wird.
 
Zurück
Oben