ich habe eine tabelle “names” mit u.a. zwei indizes:
idx_names_domain_id CREATE INDEX idx_names_domain_id ON “names” USING btree (domain_id)
idx_names_ipcount CREATE INDEX idx_names_ipcount ON “names” USING btree (ipcount)
Der Query
select id, hostname from names where domain_id is NULL AND ipcount>0 limit 1;
weigert sich allerdings, die Indizes zu benutzen und macht einen Sequential Scan:
EXPLAIN ANALYZE select id, hostname from names where domain_id is NULL AND ipcount>0 limit 1;
QUERY PLAN
Limit (cost=0.00…0.06 rows=1 width=29) (actual time=859.256…859.258 rows=1 loops=1)
-> Seq Scan on “names” (cost=0.00…530451.55 rows=9270694 width=29) (actual time=859.252…859.252 rows=1 loops=1)
Filter: ((domain_id IS NULL) AND (ipcount > 0))
Total runtime: 859.306 ms
(4 Zeilen)
Verbose:
domains=# EXPLAIN analyze verbose select id, hostname from names where domain_id is NULL AND ipcount>0 limit 1;
:initPlan <>
:extParam (b)
:allParam (b)
:nParamExec 0
:limitOffset Seq Scan on “names” (cost=0.00…530451.55 rows=9270694 width=29) (actual
time=4740.902…4740.902 rows=1 loops=1)
Filter: ((domain_id IS NULL) AND (ipcount > 0))
Total runtime: 4740.943 ms
(155 Zeilen)
Ich werd aus dem Output nicht recht schlau - vielleicht kann von den Experten jemand einen Tipp geben?
Wenn ich die die Bedingungen einzeln abfrage, sieht es aus, als ob das “domain_id IS NULL” so lange dauert. Wird NULL am Ende nicht indiziert???
Wenn ich die die Bedingungen einzeln abfrage, sieht es aus, als ob das “domain_id IS NULL” so lange dauert. Wird NULL am Ende nicht indiziert???
…Constructs equivalent to combinations of these operators, such as BETWEEN and IN, can also be implemented with a B-tree index search. (But note that IS NULL is not equivalent to = and is not indexable.)…
Um den Index tatsächlich zu nutzen empfiehlt es sich also die Spalte NOT NULL zu deklarieren und anstelle von Null Values “Blanks” (“”) einzufügen. Null Values haben ohnehin mehr Nachteile als Vorteile wie ich finde.
ich hatte sowas befürchtet…
domain_id ist ein integer, ich hab den jetzt als default auf 1 gestellt und setze per update alle NULLs auf ‘1’.
immerhin: dann funktioniert auch das lange geplante fk auf diese spalte endlich.
eine frage noch: die tabelle “names” hat ca. 20 millionen rows (bei 5 spalten mit vier einfachen indizes). etwa 12 millionen rows haben domain_id=NULL.
ein update nach dem muster
update names set domain_id=1 where domain_id is NULL
dauert ewigkeiten (viele stunden). kann man sowas beschleunigen? macht pgsql das in einzeltransaktionen und ich müsste ggf vorher die spalte locken und das in einer einzigen transaktion machen?
oder sollte man einfach den index vorher löschen?
Statt zu Fragen “Warum wird mein Index nicht verwendet?” solltest Du dich fragen, habe ich für den SELECT auch den richtigen INDEX zur Verfügung gestellt?
Wie Du in der Doku über CREATE INDEX nachlesen kannst, gibt es die Möglichkeit einen INDEX über mehrere Spalten zu erzeugen. Auch gibt es eine WHERE-Klausel, mit dem man einen PARTIAL INDEXES erzeugen kann. Wie man das ganze auch noch weiter beschleunigen kann, dafür gibt es auch noch einige Tips. Bei der Ausnutzung aller Möglickeiten dürfte sich der SELECT erheblich beschleunigen, und zwar ohne das Du die DEFAULT WERTE änderst.
Angesichts der Ausführungsdauer von Befehlen solltest Du mal ein VACUUM ausführen.
Leider hast Du keine Angaben zur Version der Datenbank gemacht, auch keine Angabe zum Betriebssystem. Hinsichtlich der Ausführungsdauer des UPDATE-Befehls ist auch nicht bekannt, ob irgendwelche TRIGGER eingeschaltet sind.
ok, der hint der fragestellung ist durchaus richtig - (ich hätte die aber vorher schon mit “Nein” beantworten können, ohne zu wissen, was ich besser machen kann.)
zur DB:
PostgreSQL 8.1.2, auf Linux, 2GB Ram, 2 SATA-Platten, DualCore-Intel-Prozessor
Bisher (nicht zuletzt aus Performance-Gründen) keinerlei Trigger, Funktionen, Views o.ä. definiert.
VACUUM ANALYZE wird jede zweite Nacht automatisch auf die wesentlichen (oft beschriebenen) Tabellen ausgeführt.
Ich war bewusst sehr sparsam mit den Indizes, weil auf viele Tabellen sehr viel (und in kleinen Portionen) geschrieben wird und ich das nicht zu sehr verlangsamen wollte.
Was ich durch ausgefeilte Indizes an lese-zeit gewinne, verliere ich sonst bei den vielen Updates.
Vor allem echten Performance-Tuning gehts mir erstmal darum, Querys zu finden, für die GAR KEIN Index verwendet wird, und der oben scheint einer gewesen zu sein.
Ist es aus deiner Sicht ein Problem, wenn ich als default “1” in die Spalte eintrage, oder ist das eher eine Stilfrage?
Ein Punkt der Performens sind mit Sicherheit die 2 SATA-Platten. Hier kannst Du mal einige Tips zur Hardware-Performenssteigerung nachlesen: http://www.powerpostgresql.com/PerfList .
Das ist für mich keine Stilfrage, aber ein Frage der Nachvollziehbarkeit. Der Wert NULL bedeutet bei mir immer gleich undefiniert oder auch real nicht vorhanden, alles andere sind echte Werte. Das Problem, welches hieraus sich ergibt, heißt dann, wie unterscheide ich zwischen undefinierten und einen echten Wert. Spätestens wenn Du nach längerer Zeit mal wieder an der Datenbank arbeitest und dich nicht mehr an diese Restriktion errinnerst oder mit mehreren an der Entwicklung der Datenbank arbeitest, kommt das Problem auf dem Tisch.
ein update nach dem muster
Zitat:
update names set domain_id=1 where domain_id is NULL
dauert ewigkeiten (viele stunden). kann man sowas beschleunigen? macht pgsql das in einzeltransaktionen und ich müsste ggf vorher die spalte locken und das in einer einzigen transaktion machen?
oder sollte man einfach den index vorher löschen?
Also ich kann nachvollziehen, dass dies etwas länger dauert, zumal er ja nicht den Index der domain_id nutzen kann, um schneller die zu updatenden Zeilen zu finden. Bei der von dir beschriebenen Hardware würde ich aber auch nicht erwarten, dass es “viele Stunden” dauert…
Was mir da so einfällt:
kannst du in der where condition noch andere bedingungen setzten, die möglicherweise einen Index nutzen?
ist die Datenbank nicht zerkonfiguriert?
Wie dem auch sei: ich bin mir ziemlich sicher, dass es keinen unterschied machst, ob du nochmal expliziet eine Transaktion einleitest (Begin… Commit), da für jede geupdatete Zeile ein xlog geschrieben wird. Commited wird eh erst am Schluss…
Was ich durch ausgefeilte Indizes an lese-zeit gewinne, verliere ich sonst bei den vielen Updates.
Ich würde auch versuchen, die Indizes auf die jeweils andere Platte abzulegen. Dann hast du das update Problem bestmöglich gelöst - denn dann können Daten parallel geschrieben werden (natürlich auch beim lesen parallel abgearbeitet werden). Ich mache im Übrigen jeden Tag einen VACUUM ANALYZE - das schadet nicht und hält vor allem die Statistiken up-to-date! Alle 2 Tage finde ich fast schon etwas wenig…
das “komplettupdate” hab ich abgeschrieben - war auch nach 24 stunden noch nicht erledigt, macht aber nix, kann auch langsamer gehen. läuft jetzt in einzelzeilen:
select id from table where x is NULL limit 1;
update table set x=1 where id=$id;
etwa eine zeile pro sekunde, das ist ok, weil ich an der stelle noch eine reihe weiterer requests fahre. in einigen millionen sekunden hab ich alles durch
Die schnellste alternative wäre wohl ein export gewesen: NULL ersetzen, und alles wieder importieren.
was ich gelernt habe ist, dass NULL ganz schlecht (weil nicht indizierbar) ist - ich hab das auch aus den anderen tabellen inzwischen überall rausgeschmissen.
ohne die null-querys läuft die db schnell genug, um mit den hintergrundprozessen und vor allem den vielen schreibzugriffen schritt zu halten.