langsames batch-update

hallo,

die folgende query läuft extremst langsam, d.h. nach 2 stunden habe ich das ganze abgebrochen :frowning: ablaufen müsste das ganze in 1/2h bis 1h, ich weiß nicht wo genau der haken liegt.

ich habe folgende tables:

PERSON (id, costumer_number,…)
PERMISSION_XYZ(id 1:1 ref PERMISSION(ID), person ref PERSON(ID),…)
PERMISSION(id, flag,…)
TEMP_TABLE(id, costumer_number, XYZ_flag, …);

indizes:
unique-constraint auf PERSON(costumer_number)
index auf PERMISSION_XYZ(person)
index auf TEMP_TABLE(costumer_number)

die tables enthalten zwischen 2 millionen und 10 millionen reihen. was ich versuche, ist ein batch-update der in PERMISSION enthaltenen flags vom TEMP_TABLE.

die query:

update PERMISSION
    set permission_flag = tmp.post_flag 
from PERMISSION_POST post, PERMISSION_TEMP tmp, PERSON pers 
where     post.id = PERMISSION.id  
              and post.person = pers.id  
              and pers.costumer_number = costumer_number;

hier auch ein explain der query:

"Hash Join  (cost=626966.65..1130850.79 rows=2107422 width=22)"
"  Hash Cond: ((pers.costumer_number)::text = (tmp.costumer_number)::text)"
"  ->  Hash Join  (cost=548438.67..973084.93 rows=2107422 width=29)"
"        Hash Cond: (permission.id = post.id)"
"        ->  Seq Scan on permission  (cost=0.00..184169.88 rows=10773488 width=18)"
"        ->  Hash  (cost=509746.90..509746.90 rows=2107422 width=19)"
"              ->  Merge Join  (cost=379210.79..509746.90 rows=2107422 width=19)"
"                    Merge Cond: (pers.id = post.person)"
"                    ->  Index Scan using person_pkey on person pers  (cost=0.00..93956.39 rows=2136130 width=15)"
"                    ->  Sort  (cost=379210.79..384479.35 rows=2107422 width=16)"
"                          Sort Key: post.person"
"                          ->  Seq Scan on permission_post post  (cost=0.00..38997.22 rows=2107422 width=16)"
"  ->  Hash  (cost=40311.99..40311.99 rows=2138399 width=15)"
"        ->  Seq Scan on permission_temp tmp  (cost=0.00..40311.99 rows=2138399 width=15)"

bin kein profi. was ich hier nicht verstehe ist warum postgres seq-scans macht obwohl doch indexe für die relevanten spalten zur verfügung stehen :frowning:

vlt kann jemand von euch ein mögliches problem ausmachen :astonished: thx!

ps: ich weiß ich könnte das ganze noch mit eine where-clausel “and tmp.post <> per.permisison_flag” verfeinern, aber das macht auch nicht den riesigen unterschied, da ich doch mit vielen änderungen rechne…

Dir fehlen Indexe auf den ID-Spalten und uns das EXPLAIN ANALYSE.

Andreas

die Indexe auf die ID Spalten werden implizit von Postgres erstellt da es primary keys sind.

Da wie gesagt das Statement mehrere Stunden läuft ist ein explain analyze schwer zu erzeugen… darum hier ein explain analyze der “verfeinerten” query, die bei 0 änderungen in wenigen minuten durchläuft (bei vielen änderungen IMHO aber auch so lange brauchen wird wie erstere…)
query:

explain analyze
update permission
    set permission_flag = tmp.post
from permission_post post, permission_temp tmp, person pers
where   post.id = dwh_test.permission.id   
           and post.person = pers.id   
           and pers.costumer_number = tmp.costumer_number
           and tmp.post <> permission.permission_flag

ergebnis:

"Hash Join  (cost=697972.53..1136351.35 rows=553198 width=22) (actual time=181692.582..181692.582 rows=0 loops=1)"
"  Hash Cond: (permission.id = post.id)"
"  Join Filter: (tmp.post <> permission.permission_flag)"
"  ->  Seq Scan on permission  (cost=0.00..184169.88 rows=10773488 width=22) (actual time=6.930..90412.795 rows=10693855 loops=1)"
"  ->  Hash  (cost=661338.76..661338.76 rows=2107422 width=12) (actual time=69027.409..69027.409 rows=2138771 loops=1)"
"        ->  Hash Join  (cost=457738.77..661338.76 rows=2107422 width=12) (actual time=29216.778..67037.482 rows=2138771 loops=1)"
"              Hash Cond: ((pers.costumer_number)::text = (tmp.costumer_number)::text)"
"              ->  Merge Join  (cost=379210.79..509746.90 rows=2107422 width=19) (actual time=13838.619..45217.096 rows=2138771 loops=1)"
"                    Merge Cond: (pers.id = post.person)"
"                    ->  Index Scan using person_pkey on person pers  (cost=0.00..93956.39 rows=2136130 width=15) (actual time=13.761..24606.023 rows=2138771 loops=1)"
"                    ->  Sort  (cost=379210.79..384479.35 rows=2107422 width=16) (actual time=13824.840..15570.072 rows=2138771 loops=1)"
"                          Sort Key: post.person"
"                          ->  Seq Scan on permission_post post  (cost=0.00..38997.22 rows=2107422 width=16) (actual time=9.179..7769.699 rows=2138771 loops=1)"
"              ->  Hash  (cost=40311.99..40311.99 rows=2138399 width=15) (actual time=15307.092..15307.092 rows=2138773 loops=1)"
"                    ->  Seq Scan on permission_temp tmp  (cost=0.00..40311.99 rows=2138399 width=15) (actual time=14.081..13092.178 rows=2138773 loops=1)"
"Total runtime: 181751.904 ms"

Schwer zu durchschauen, was Du da machst. Es wäre u.U. hilfreich, uns mal mit create table die Tabellen zu zeigen, zu füllen und dann zu zeigen, was wie upgedated werden soll (Beispieldaten).


Andreas

hmm schwer da sehr umfangreich, aber ok ich versuchs mal :wink:

create tables:

CREATE TABLE dwh_dev.person
(
  id bigint not null,
  costumer_number character varying(255) NOT NULL,
  firstname character varying(255),
  lastname character varying(255),
  gender integer,
  "language" character varying(255),
  --... HIER STEHT ZIEMLICH VIEL UNWICHTIGES..
  CONSTRAINT person_pkey PRIMARY KEY (id),
  CONSTRAINT person_costumer_number_key UNIQUE (costumer_number)
);

CREATE TABLE dwh_dev.permission
(
  id bigint NOT NULL,
  usage integer,
  permission_flag integer,
  CONSTRAINT permission_pkey PRIMARY KEY (id)
);


CREATE TABLE dwh_dev.permission_post
(
  id bigint NOT NULL,
  source bigint NOT NULL,
  person bigint NOT NULL,
  CONSTRAINT permission_post_pkey PRIMARY KEY (id),
  CONSTRAINT fk_permissionpost_person FOREIGN KEY (person)
      REFERENCES dwh_dev.person (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_permissionpost_source FOREIGN KEY (source)
      REFERENCES dwh_dev.datasource_descriptor (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_post_permission FOREIGN KEY (id)
      REFERENCES dwh_dev.permission (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT permission_post_person_key UNIQUE (person)
);

CREATE TABLE dwh_dev.permission_temp
(
  id bigint NOT NULL,
  costumer_number character varying(255),
  email integer,
  phone integer,
  newsletter integer,
  post integer,
  sms integer,
  CONSTRAINT permission_temp_pkey PRIMARY KEY (id)
);

-- ************ INDICES *************
create index permissionpostperson_idx on dwh_dev.permission_post(person);
create index ptemp_costumer_number_idx on dwh_dev.permission_temp(costumer_number);

-- ************ TRIGGER AND FUNCTIONS *************

-- insert record into permission-table and tracking when a new permission is added
create or replace function
dwh_dev.permission_insert() returns trigger as $$ BEGIN insert into dwh_dev.permission(id,usage,permission_flag) values(NEW.id,0,0);insert into dwh_dev.permission_tracking(id, changed,permission_flag,permission) select nextval('dwh_dev.hibernate_sequence'),st.last_change,0 as perm, NEW.id from dwh_dev.stats st where st.person = NEW.person; return NEW;END;$$ language plpgsql;

create trigger permission_post_insert
before insert on dwh_dev.permission_post
for each row execute procedure dwh_dev.permission_insert();

und ids kriegen wie gesagt implizit einen index von postgres zugewiesen


diese tables werden befüllt aus einer anderen datenbank. von zeit zu zeit werden von dort updates eingespielt. da zugriffe auf diese live-datenbank ziemlich kurz gehalten werden sollen, habe ich hier einen permission_temp table. dort werden die neuen daten reingespielt:

  • zuerst wird der inhalt von permission_temp gelöscht
  • dann die daten hineingeschrieben (ca 2 millionen datensätze)
  • dann die obige query ausgeführt


    was vlt auch noch interessant ist, ist das es wie in meinem 1. post angedeutet noch weitere permission_xyz tables gibt (z.b. permission_email, permission_phone, etc…) alle diese haben alle eine [0…1 : 1] beziehung auf permission.

das bedeutet wiederun, dass sich in allen “permission_xyz” jeweils tables ca 2 millionen datensätze befinden. im table “permission” sind dann ca 10 millionen datensätze

auf allen permission_xyz tables befindet sich jeweils ein insert-trigger für die 1:1 beziehung. dieser dürfte aber bei der update-query keine rolle spielen…

Zu der Verwendung von Indexes: wenn ich mir das EXPLAIN so anschaue, denkt PostgreSQL dass es ganz viele Zeilen (alle Zeilen) lesen muss. Also macht es keinen Sinn, den Index zu nutzen, weil das bloß zusätzlicher Overhead ist.

Du kannst das gern mal probiernen, indem du SET enable_seqscan = OFF setzt und schaust, ob sich der Query Plan verbessert - und auch, ob sich die Ausführungszeit verbessert.

Wie schaut es eigentlich mit ANALYZE auf den Tabellen aus? Ohne vernünftige Statistikdaten kann der Query Planer sowieso keinen Index einsetzen.


Zu der Laufzeit des Updates: kannst du das Update mal als SELECT umschreiben und schauen, welche Werte (wie oft) aktualisiert werden? Mich beschleicht das dumpfe Gefühl als ob deine Joins die Datensätze mehrfach liefern und damit mehrfach aktualisieren. Das dauert dann natürlich länger.

Das kann wohl sein. Ich hab mal die Tabellen so angelegt, mit je 10.000 Einträgen (via random() erzeugt). Das Explain zeigt mir dann:

test=# explain analyse select * from permission, permission_post post, permission_temp tmp, person pers where   post.id = permission.id and post.person = pers.id and pers.costumer_number = tmp.costumer_number and tmp.post <> permission.permission_flag;
                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=810.00..3209.59 rows=101357 width=32) (actual time=30.006..143.677 rows=101693 loops=1)
   Hash Cond: (pers.costumer_number = tmp.costumer_number)
   Join Filter: (tmp.post <> permission.permission_flag)
   ->  Hash Join  (cost=540.00..1185.00 rows=10000 width=24) (actual time=20.174..48.812 rows=9995 loops=1)
         Hash Cond: (post.person = pers.id)
         ->  Hash Join  (cost=270.00..665.00 rows=10000 width=16) (actual time=10.119..26.630 rows=10000 loops=1)
               Hash Cond: (permission.id = post.id)
               ->  Seq Scan on permission  (cost=0.00..145.00 rows=10000 width=8) (actual time=0.008..3.797 rows=10000 loops=1)
               ->  Hash  (cost=145.00..145.00 rows=10000 width=8) (actual time=10.096..10.096 rows=10000 loops=1)
                     ->  Seq Scan on permission_post post  (cost=0.00..145.00 rows=10000 width=8) (actual time=0.007..4.233 rows=10000 loops=1)
         ->  Hash  (cost=145.00..145.00 rows=10000 width=8) (actual time=10.042..10.042 rows=10000 loops=1)
               ->  Seq Scan on person pers  (cost=0.00..145.00 rows=10000 width=8) (actual time=0.007..4.233 rows=10000 loops=1)
   ->  Hash  (cost=145.00..145.00 rows=10000 width=8) (actual time=9.814..9.814 rows=10000 loops=1)
         ->  Seq Scan on permission_temp tmp  (cost=0.00..145.00 rows=10000 width=8) (actual time=0.010..4.190 rows=10000 loops=1)
 Total runtime: 171.086 ms
(15 rows)

Man beachte die

(actual time=30.006..143.677 rows=101693 loops=1)

in der ersten Zeile.

Weiterhin wundere ich mich etwas über die verwendeten Datentypen.

Andreas