Automatische Indexgenerierung bei Unique-Constraint etc.

Hallo zusammen,

ich bin neu hier und hab bisher keine Erfahrungen mit Postgresql. Mit Datenbanken beschäftige ich mich allerdings schon länger, komme allerdings von DB2 her.

Dem Handbuch zu Postgresql (ich nutze Version 8.1 unter Windows XP bzw. Windows Server 2003) entnehme ich nun, dass, sobald ich einen Unique-Constraint auf ein Feld setze, automatisch auch ein Index dafür generiert wird. Wenn ich mir meine Tabellen allerdings mit pgAdmin III anschaue, sind dort keinerlei Indizes verzeichnet. Setze ich einen Index von Hand, so wird dieser auch angezeigt. Das lässt mich doch vermuten, dass eine Unique-Constraint doch keine automatische Indexgenerierung zur Folge hat, oder? Wie siehts denn mit dem Primary Key aus, auch dafür müsste ja automatisch ein Index generiert werden, oder? Und wie siehts schließlich mit Foreign Keys aus? Hier wäre es ja nicht unbedingt nötig, einen Index zu generieren, aber meistens ist er doch von Vorteil.

Kurz: Wann generiert Postgresql automatisch Indizes und falls die bei Unique-Constraints der Fall sein sollte, warum zeigt pgAdmin III diese nicht an?

Vielen Dank im Voraus,
Bierstuebl (der aufgrund der wirklich schwer zu lesenden Bildchen beim Anmelden entnervt seinen Username falsch geschrieben hat :slight_smile:

Was ist PGAdminIII? Und was Windows? Okay, Spaß beiseite :wink:

test=# create table foobarbatz (id int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foobarbatz_pkey" for table "foobarbatz"
CREATE TABLE
test=# \d foobarbatz
  Table "public.foobarbatz"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer | not null
Indexes:
    "foobarbatz_pkey" PRIMARY KEY, btree (id)

test=#

Warum PGAdminIII die nicht anzeigt (ich kann es grad nicht prüfen, nutze es nicht), weiß
ich allerdings nicht. Ich würde, um das System zu erlenen, zu psql raten.

Ansonsten: viel Spaß mit PG.


Andreas

Hallo bierstuegbl,

postgres erstellt für Unqiue und Primary Key Constraints einen Index! Das merkt man spätestens dann, wenn man einen Index definiert der denselben Namen besitzt wie einer der automatisch erstellten Indizes. Bei PgAdmin gibt es in den Einstellungen eine Option mit der du Systemtabellen anzeigen lassen kannst. Die automatischen Indizes siehst du dann aber trotzdem nicht. Dafür mußt du auf die sogenannten System Catalogs zugreifen. Diese Tabellen befinden sich im Schema pg_catalog (nur sichtbar in pgAdmin wenn du die vorher genannte Einstellung aktivierst). Folgende Anfrage gibt dir alle definierten Indizes zurück:

SELECT * FROM pg_class WHERE relkind = ‘i’;

Hilfreich ist auch das in pgAdmin eingebaute Postgres Handbuch mit Suchfunktion.

Alles klar, danke für die schnelle Antwort.

Windows muss ich eben nutzen, bei meinem Arbeitgeber sind die Server eben nun mal Windows Server 2003 :slight_smile: Allerdings muss ich sagen, dass die Sachen sehr gut laufen. Selbst die Admins sagen inzwischen, dass die Windows Maschinen eigentlich viel weniger Probleme bereiten als die Unix-Kisten. Wir haben hier Win2000-Server, die seit zig Jahren ohne Absturz oder Fehler einwandfrei laufen.Privat nutz ich zwar trotzdem hauptsächlich Kubuntu (Früher FreeBSD, davor SUSE), aber Windows ist im Businessbereich gar nicht so schlecht. Wir haben hier ein Rechenzentrum, das seinesgleichen sucht (zumindest in Deutschland) und die Admins sagen mir, mit Windows haben sie weniger Ärger - also so schlecht kanns demnach net sein :slight_smile:

Das soll hier aber auf keinen Fall in einem Linux vs. Windows Thread ausarten. Über solche Nicklichkeiten sollten professionelle Nutzer (und wer eine Datenbank etwas intensiver nutz ist oft schon ein professioneller Nutzer) doch längst hinweg sein. Genommen wird, was zweckmässig ist und schnell und sauber zum Ziel führt. Das ist wie bei den Programmiersprachen. Wer meint, nur in C++ geschriebene Programme sind richtige Programme, der hat einfach nix verstanden. Wenn ich was systemnahes programmieren muss, nehm ich auch C++. Trotzdem erreiche ich bei einem Großteil meiner Anwendungsgebiete mit Java oder manchmal C# (Windows eben) deutlich schneller, einfacher und sauberer mein Ziel. Oder ich schreib eben mal schnell ein Perl- oder Ruby-Skript, wenn ich was hin und herparsen muss etc.

Aber jetzt auf in den Kampf mit Postgresql. Ich bin wirklich gespannt auf die Performance, da meine Datenbanken sehr groß werden. Bisher glaube ich nicht, dass Postgresql an DB2 rankommt, was Perfomance betrifft - aber man kann sich ja überraschen lassen :slight_smile:

Gruß,
Bierstuebl

Hallo Bierstuebl,

vielleicht helfen da einige Tips: http://www.powerpostgresql.com/PerfList.



mfg

fristo

Danke für den Hinweis Fristo. Hab die Seite beim Stöbern hier im Forum schon entdeckt. Allerdings bin ich Hardwaretechnisch im Moment fest an einen virtuellen Windows-Server gebunden. Ich weiss nichtmal, ob der ein RAID hat. Falls ja, weiss ich aber erst recht nicht, wie das dann mit dem virtuellen Server ausgenutzt werden kann. Da der Kunde im Moment nicht zusätzlich zahlt steht mir keinerlei andere Harware zur Verfügung. Das Ganze ist sozusagen eine unentgeldliche Zusatzleistung durch meinen Arbeitgeber für den Kunden, es steht also exakt ein Null-Budget zur Verfügung. Dass das Ganze aber gar nicht so trivial und klein ist, interessiert wieder niemanden. Ich werde schätzungsweise zu Beginn ca. 20-40 Millionen Datensätze im Monat haben (Produktionsdaten aus Fertigungsstraßen). Falls dann noch andere Werke angeschloßen werden deutlich mehr.

Deswegen habe ich beschlossen, dass jedes Werk seine eigene Datenbank bekommt und dann wahrscheinlich jeden Monat eine neue Datenbank angefangen wird. Ob das möglich ist, wird sich noch zeigen, da statistische Auswertungen über den Datenbankinhalt meist bis 1 Monat in die Vergangenheit erstellt werden sollen - aber eben von jedem beleibigen Zeitpunkt aus. Wie ich das löse, muss ich mir noch überlegen. Falls jemand vorschläge hat, immer her damit :slight_smile:

Grüße,
Bierstuebl

Klingt nach Betriebsdatenerfassung (wann hat welche Maschine was hergestellt), hab ich auch auf Arbeit…

Ähm, wenn Du jür jeden Monat eine neue DB erstellst, dann läufst Du ins Messer, wenn Du eine
Auswertung für ein Quartal machen willst. Ich würde davon abraten.
Ich würde für jeden Produktionsbetrieb ein eigenes Schema nehmen. Und falls die Tabellen wirklich zu groß werden, dann Daten in History-Tabellen auszulagern. Dann kannst Du immer noch auf mittels UNION-verbundene Gesamtmengen hantieren, falls das mal nötig ist.

Mit den Daten der einzelnen Produktionsbetriebe in einzelnen Schemas und bedarfsweise ausgelagerten Daten in History-Tabellen bist Du im Bedarfsfall noch immer in der Lage, eine Auswertung über alle Betriebe und das letzte Jahr zu machen. Falls sinngemäße Fragen eines Tages kommen. Und sie werden kommen.

Ach ja: Wenn Du Redundanz vermeidest und schön normalisierst, wirst Du so schnell nicht in Performance-Probleme kommen.

Interessanter Ansatz - wie genau funktioniert das denn mit History-Tabellen? Sagen wir mal ich habe nach 2 Monaten eine Tabelle mit 80 Millionen Einträgen und will die jetzt auslagern. Umbenennen der Tabelle dürfte ja nicht gehen denk ich mal (schon allein wegen bestimmten Foreign-Key Beziehungen etc. Also eine neue Tabelle anlegen und die Daten einfach von der alten in diese neueTabelle schieben? Dürfte viel zu lange dauern. Also wie geht man da ran?

Das mit den verschiedenen Datenbanken pro Werk ist laut Aussage der Betroffenen hier kein Problem, da angeblich keine Daten werksübergreifend verglichen werden sollen - wie du ja aber schon angedeutet hast, dürfte diese Aussage spätestens wenn das Ganze angelaufen ist passe sein :slight_smile:

Deshalb war meine Aussage mit den Werken nicht richtig - ich Teile nach Baugruppen auf, da macht es nämlich wirklich keinen Sinn, völlig verschiedene Teile zu vergleichen.

Soeben hab ich in der c’t gelesen, dass es auch von DB2 eine kostenlose Community-Edition gibt. Die ist beschränkt auf 2 Dual Core CPUs und 4 GB Ram - aber da ich in absehbarer Zeit eh keine Harware die diese Einschränkungen aushebelt zur Verfügung habe, macht mir das nichts. Schwierige Entscheidung jetzt, bisher find ich die Postgresql echt in Ordnung, aber ich hab noch keine Massendaten getestet. Die Geschwindigkeit von DB2 bei Auswertungen ist eben echt gut - selbst 10-fach Joins über große Tabellen hinweg waren da bei mir echt immer im guten Bereich. Der ganze große Vorteil wäre eben, dass bei Bedarf einfach auf eine “große” Db2 umgeschalten werden kann, ohne dass ich übehaupt was ändern muss (weder im Schema noch in meiner Anwendung).

Jedenfalls wäre es nett, wenn du mir die Sache mit den History-Tables näher erläutern könntest.

Viele Grüße,
Bierstuebl

Schau Dir das mal an:

http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html

Ansonsten kannst Du ja auch via CRON täglich schauen, welche Datensätze älter als z.B. 80 Tage sind und diese in eine andere Tabelle einfügen und in der Mastertable löschen. VACUUM Analyse nicht vergessen.

Ansonsten habe ich die Erfahrung gemacht, daß auch bei einigen Millionen Zeilen in einer Tabelle Suchzeiten bei passenden Indexen nur im Millisekundenbereich liegen (je nach Abfrage), ich habe daher mit Partitionierung etc. keine Erfahrungen.