update performance

hallo ich bin neu hier und hoffe ihr könnt mir weiterhelfen

folgende Tables:

person:

[LEFT]id -> integer pk not null mit index
costumer_number -> varchar not null unique mit index
.... weitere spalten aber nicht so wichtig ....[/LEFT]

permission:

[LEFT]id -> integer pk not null mit index
person -> foreignkey person(id) unique mit index 
permissionA -> integer
permissionB -> integer[/LEFT]

in jeder tabelle befinden sich ca 2 mio datensätze.

jetzt ist meine aufgabe, ein monatliches update auf die tabelle “permission” durchzuführen. die daten kommen von extern, d.h. ich habe die felder mit neuen werten, aber ohne “id” sondern nur mit der “costumer_number”:

costumer_number, new-permissionA, new-PermissionB

das update schaut so aus:

[LEFT]update dwh_test.PERMISSION_EMAIL 
set PERMISSIONA=?, PERMISSIONB=?
 where id in 
      (select perm.id from PERMISSION perm, PERSON pers 
        where perm.PERSON=per.id and per.COSTUMER_NUMBER=?) [/LEFT]

da ich einen join von permission nach person brauche. leider ist dieses update bei einer so großen datenanzahl überhaupt nicht performant. die datenbank schafft ca 100 Datensätze pro Minute (was bei 20 mio datensätzen pi mal daumen 333 stunden dauern würde).

EXPLAIN liefert mir:

"Nested Loop  (cost=17.02..25.87 rows=1 width=22)"
"  ->  HashAggregate  (cost=17.02..17.03 rows=1 width=4)"
"        ->  Nested Loop  (cost=0.00..17.02 rows=1 width=4)"
"              ->  Index Scan using costumer_number_idx on person person2_  (cost=0.00..8.55 rows=1 width=4)"
"                    Index Cond: ((costumer_number)::text = '1041801'::text)"
"              ->  Index Scan using permissionemailperson_idx on permission_email permission1_  (cost=0.00..8.45 rows=1 width=8)"
"                    Index Cond: (permission1_.person = person2_.id)"
"  ->  Index Scan using permission_email_pkey on permission_email  (cost=0.00..8.83 rows=1 width=22)"
"        Index Cond: (permission_email.id = permission1_.id)"

jede hilfe ist mir sehr willkommen!

Hi,

das Nadelöhr scheint nicht das Subselect zu sein.
Wie sieht die Struktur der Tablelle “permission_email”, ist die Spalte “id” indiziert?

Laufen irgendwelche Trigger?

Klaup

ich war eigentlich der meinung postgres legt automatisch indizes für primary-keys an. jedenfalls hab ich (unter pgadmin3) keine indizes auf meine PKs gefunden. nach dem anlegen schaut explain folgendermaßen aus:

"Nested Loop  (cost=17.02..25.50 rows=1 width=22)"
"  ->  HashAggregate  (cost=17.02..17.03 rows=1 width=4)"
"        ->  Nested Loop  (cost=0.00..17.02 rows=1 width=4)"
"              ->  Index Scan using costumer_number_idx on person person2_  (cost=0.00..8.55 rows=1 width=4)"
"                    Index Cond: ((costumer_number)::text = '1036966'::text)"
"              ->  Index Scan using permissionemailperson_idx on permission_email permission1_  (cost=0.00..8.45 rows=1 width=8)"
"                    Index Cond: (permission1_.person = person2_.id)"
"  ->  Index Scan using pk_permission_email_idx on permission_email  (cost=0.00..8.45 rows=1 width=22)"
"        Index Cond: (permission_email.id = permission1_.id)"

also mMn immer noch gleich. allerdings weiß ich mit Dingen wie (cost=17.02…25.50 rows=1 width=22) nicht wirklich viel anzufangen :astonished: es laufen keine trigger…

die methadaten:

-- Table: dwh_test.permission_email

-- DROP TABLE dwh_test.permission_email;

CREATE TABLE dwh_test.permission_email
(
  id integer NOT NULL,
  usage integer,
  person integer NOT NULL,
  source integer NOT NULL,
  bounce_flag integer,
  permission_newsletter_flag integer,
  permission_standalone_email_flag integer,
  CONSTRAINT permission_email_pkey PRIMARY KEY (id),
  CONSTRAINT fk_permission_person91fd910c FOREIGN KEY (person)
      REFERENCES dwh_test.person (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_permission_source91fd910c FOREIGN KEY (source)
      REFERENCES dwh_test.datasource_descriptor (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=FALSE);
ALTER TABLE dwh_test.permission_email OWNER TO uboot;

-- Index: dwh_test.permissionemailperson_idx

-- DROP INDEX dwh_test.permissionemailperson_idx;

CREATE INDEX permissionemailperson_idx
  ON dwh_test.permission_email
  USING btree
  (person);

-- Index: dwh_test.pk_permission_email_idx

-- DROP INDEX dwh_test.pk_permission_email_idx;

CREATE INDEX pk_permission_email_idx
  ON dwh_test.permission_email
  USING btree
  (id);

wobei der foreignkey “source” zurzeit bei allen datensätzen identisch ist, ein index würde hier mMn eher weniger sinn machen…

Ja.

jedenfalls hab ich (unter pgadmin3) keine indizes auf meine PKs gefunden.

Falsches Tool. psql und \d gehen da besser.

allerdings weiß ich mit Dingen wie (cost=17.02…25.50 rows=1 width=22) nicht wirklich viel anzufangen > :astonished: > es laufen keine trigger…

Google → explaining explain, erster Treffer.


Andreas

ok ich werd mich mal tiefer in die materie einlesen.
in psql wurden die automatisch angelegten Indizes wirklich angezeigt, das doppelte Anlegen war also in der Tat fürn Hugo…

aber eine andere Frage:
wo genau liegt der Vorteil eines Updates aus einem temporären Table:

also anstatt dem bisherigen Workflow der so ausschaut:

  1. extrahiere Daten aus einer externen Datenquelle in den Arbeitsspeicher meiner Applikation
  2. führe direkt ein Update auf den Permission-Table aus (ich verwende natürlich prepared Statements…)

wurde mir in einem anderen Forum vorgeschlagen:

  1. extrahiere Daten aus einer externen Datenquelle in den Arbeitsspeicher meiner Applikation
  2. lege die Daten in einem Temporären Table ab (permission_temp). Also fallen hier nur inserts an.
  3. führe ein upate auf die Daten im Permission-Table aus:
UPDATE permission p
SET p.permissiona = pt.permissiona, p.permissionb = pt.permissionb
FROM permission_temp pt, person pe
WHERE pt.customer_number=pe.customer_number
AND pe.id = p.person;

wie profitiert die Postgres Datenbank vom Fakt, dass sich die Daten bereits in einer anderen Tabelle der Datenbank befinden. Sind die updates nicht ca gleich Zeitintensiv wie in der ursprünglichen Lösung?

indizes auf PK’s reichen nicht … du hast komplexe bedingungen → AND

deine indizes sollten dann guenstiger weise auch die entsprechenden felder beinhalten …

so sollte auf der table person ein index ueber id und customer_number existieren …

denn sonst waer es zwar huebsch dass er mal eben die 20 mio saetze uebe den PK mit person joinen kann - aber customer_number fuehrte trotzdem zu einem tablescan

das gleiche gilt natuerlich auch fuer die anderen involvierten tables … schau dir einfach mal deine clauseln an


zu der frage am ende … pauschal gibt es nicht DIE loesung … persönlich mag ich temp-tables garnicht :wink:
was bei imports meist ein problem ist, dass dauernd in versuchung geraet ids für relationen zu querien …

hallo, also ich hab indizes auf den spalten die in den where clauseln vorkommen.

was spricht gegen temp-tables? naja, sie liegen halt nur nutzlos rum solangen kein import stattfindet, aber wenn man sie mit einem aussagekräftigen Prefix versieht stören sie meiner Meinung nach nicht unbedingt… Ist halt nicht grad “schön” :p

Mit deinem letzten Satz kann ich leider nicht so viel anfangen g temp-tables führen in Versuchung oder deine Vorliebe diese zu vermeiden? :p und für joins ist man doch sowieso auf IDs angewiesen oder was meinst du?

also ich hoffe nicht einzelne indizes :slight_smile: sondern komplexe indizes … ueber mehrere spalten …

nein über einzelne spalten. wie gesagt nämlich über PERMISSION(person) sowie PERSON(ID) und PERSON(COSTUMER_NUMBER)

für einen index über mehrere spalten würd mir jetzt kein beispiel einfallen, in den where-clauseln des updates kommen ja nur die obigen spalten vor und ich wüsst ehrlich gesagt nicht ob ein CREATE INDEX PERSON_IDX on PERSON(id, costumer_number) sinnvoll ist wenn bereits einzelne Indizes auf ID und COSTUMER_NUMBER bestehen die ja beide implizit angelegt werden (costumer_number ist UNIQUE)… :confused:

dann wirst du sehen warum das sinnvoll - und v.a. auch richtig ist …

wenn du indizes einzeln fuer jede spalte anlegst - dann ist das ja schoen für diese spalte :slight_smile: aber nur wenn du einzeln nach diesen spalten selektierst

… diese indizes sind mehr oder minder nutzlos fuer komplexe abfragen denn die indexe auf die einzelnen spalten lassen sich nicht mal eben kombinieren … dafuer gibt es dann zusammengesetzte indexe

Falsch.

Andreas

na gottseidank gibt es dich … ich dachte schon combinierte indexe sind eine sinnvolle erscheinung lachwech

erklaer uns doch bitte mal wie ein DBMS aus mehreren indizes einen neuen index von allein herbeizaubert

Die einzelnen Indicies lassen sich unter Umständen auch nutzen.

Nur: ohne Wissen, ob diese Indicies auch genutzt werden ist das Anlegen völlig sinnfrei.

Zur Performance von Temp Tables: wenn die auf die Platte wandern, kosten sie I/O Last und sie sind wesentlich langsamer als alles im RAM.

Generell kann man keine Aussage zur Performance treffen, ohne das Gesamtsystem zu kennen, du müsstest dich also etwas in das Optimieren einer Datenbank hineinarbeiten.

ja sie lassen sich nutzen um zwischen-mengen zu erzeugen - aber die sind selten guenstig (viel zu gross) und werden dann doch wieder geloopt … wie du schon sagst - man sollte wissen wofuer der index gebraucht wird … einfach mal so irgend nen index erzeugen ist unschoen …

bei dem obigen beispiel aber koennte er glueck haben - wie ich gerade sehe :slight_smile:

Bitmap Index Scan.

Andreas

das mag zwar im Zuge der hier losgetretenen Diskussion etwas uninteressant sein, aber das Problem scheint gelöst. Ist zwar etwas peinlich, aber es lag (obwohl ich mir da 100%ig sicher war) daran, dass ich in einer Tabelle (es gibt mehrere Permission_xyz Tables) einen Index nicht angelegt hatte. Danach veringerte sich die Zeit von 1,5 Wochen auf 3,7 Stunden :astonished: Ist wirklich ein sehr blöder Fehler in Anbetracht der Zeit die dadurch verloren ging…

Aber die die Diskussion hier finde ich doch sehr interessant. Ein sinnvoller Index über mehrere Spalten wäre also quasi, wenn ich weiß dass regelmäßig z.B. ein Report läuft der Daten generell über 2 Spalten grob selektiert (z.B. Alter und Geschlecht), dann würde ich über diese einen Index legen und in der WHERE Klausel würden diese über WHERE birthdate between ? and ? and gender = ? angesprochen werden? Da die Reihenfolge der Spalten im Index auch wichtig ist, würde ich zuerst Alter und dann Geschlecht angeben, da so schneller die Auswahl der möglichen Datensätze verkleinert wird.

Ich bin mir immer noch nicht ganz sicher ob es sinn macht über 2 Spalten die bereits implizit von Postgres einen Index zugewiesen bekommen haben nochmals einen gemeinsamen Index zu legen. Vielleicht werde ich mal einen Testlauf machen wenn ich genügend Zeit dafür habe (ein Update von 4 Stunden zu reinen Testzwecken will doch geplant sein :wink: ) und dann hier ein Feedback geben…

In Versionen ab 8.1 dürfte der Gewinn eher bescheiden sein, da PG seit 8.1 über einen sog. Bitmap Index Scan verfügt, bei dem die Informationen aus 2 oder mehr Indexen schon logisch verknüpft werden (im Speicher), und erst dann die verbleibenden Records gelesen werden.

Aber probier ruhig mal, interessant ist die Frage allemal, und berichte hier.

Andreas

naaaaguuuuut … wenn dus index nennen willst … auch wenn das ergebnis des “bitmap-index” nicht sortiert ist … sondern ne hash-map … will ich das mal fast gelten lassen :slight_smile: