Wenn PostgreSQL Abfragen immer länger dauern
Zum Beispiel: eine einfache Abfrage ...
phpugs=# select anf_datum, adr_a_an_i_d, anfrager, chiffre from anfrage
where adr_i_an_a_d > '2007-03-15' and enddate is null;
anf_datum | adr_a_an_i_d | anfrager | chiffre
------------------------+------------------------+----------+---------
2007-03-16 20:36:15+01 | 2007-03-16 20:41:53+01 | 28997 | 27271
...
2007-03-14 18:40:37+01 | 2007-03-15 09:05:01+01 | 27532 | 28935
55,173 ms
... auf einer einfachen Tabelle
phpugs=# \d anfrage
Table "public.anfrage"
Column | Type | Modifiers
----------------+--------------------------+---------------
anf_datum | timestamp with time zone |
anfrager | integer |
chiffre | integer |
...
nfrg | boolean | default false
Indices:
"i_anenddate" btree (enddate)
"i_anf_datum" btree (anf_datum)
"i_ankidanin" btree (anfrager, inhaber )
"i_anstatabschl" btree (projektchiffre)
"i_chiffre" btree (chiffre)
"i_anfrager" btree (anfrager)
"i_inhaber " btree (inhaber )
Wieviel Speicher belegt eine Tabelle?
PostgreSQL benutzt zur Speicherung und Wiederverwendung von Abfrageergebnissen seinen shared-buffers-Cache. Die shared-buffers werden in der postgresql.conf angegeben und beim Start des Servers allokiert. Wie groß dieser Cache gewählt wird, hängt davon ab, wieviel RAM zur Verfügung steht und wieviele Verbindungen maximal möglich sind. *General Wisdom* empfiehlt ca 25% des verfügbaren Speichers.
Wir fragen den Systemkatalog pg_class ab. In diesem Systemkatalog verwaltet PostgreSQL alle Datenbankobjekte, egal, ob sie vom System oder vom Benutzer erzeugt wurden: Tabellen r, Views v, Indices i und TOAST-Tabellen t.
- reltuples gibt die Anzahl der Datensätze an
- relpages gibt die Anzahl der Speicherseiten an, die dieses Datenbankobjekt belegt, in Blöcken zu 8 KB. In unserem Fall hat die Tabelle *anfrage* eine Grösse von knapp 6 MB. Zusammen mit ihren Indexen belegt diese Tabelle 1413 Speicherblöcke, das sind gut 11 MB. Allerdings ist die Tabelle neu erzeugt worden, so dass die Daten wohlgeordnet und lückenlos im Speicher liegen.
phpugs=# select relname, relkind, reltuples, relpages from pg_class
where relname in ('anfrage', 'i_anenddate', 'i_anf_datum',
'i_ankidanin', 'i_anstatabschl', 'i_chiffre', 'i_anfrager', 'i_inhaber ');
relname | relkind | reltuples | relpages
----------------+---------+-----------+----------
anfrage | r | 39653 | 729
i_anenddate | i | 39653 | 106
i_anf_datum | i | 39653 | 111
i_ankidanin | i | 39653 | 111
i_anstatabschl | i | 39653 | 89
i_chiffre | i | 39653 | 89
i_anfrager | i | 39653 | 89
i_inhaber | i | 39653 | 89
(8 rows)
phpugs=# select sum(relpages) from pg_class where relname in ('anfrage', 'i_anenddate',
'i_anf_datum', 'i_ankidanin', 'i_anstatabschl', 'i_chiffre', 'i_anfrager', 'i_inhaber ');
sum
------
1413
Viele UPDATEs später
Dieselbe Abfrage, dieselbe Tabelle ...
phpugs=# select anf_datum, adr_a_an_i_d, anfrager, chiffre from anfrage
where adr_i_an_a_d > '2007-03-15' and enddate is null;
anf_datum | adr_a_an_i_d | anfrager | chiffre
------------------------+------------------------+----------+---------
2007-03-16 20:36:15+01 | 2007-03-16 20:41:53+01 | 28997 | 27271
...
2007-03-14 18:40:37+01 | 2007-03-15 09:05:01+01 | 27532 | 28935
Time: 263,307 ms
... aber es dauert viel länger und die Tabelle belegt viel mehr Platz.
phpugs=# select relname, relkind, reltuples, relpages
from pg_class where relname in ('anfrage', 'i_anenddate', 'i_anf_datum',
'i_ankidanin', 'i_anstatabschl', 'i_chiffre', 'i_anfrager', 'i_inhaber ');
relname | relkind | reltuples | relpages
----------------+---------+-----------+----------
anfrage | r | 39598 | 3106
i_anenddate | i | 39598 | 593
i_anf_datum | i | 39598 | 581
i_ankidanin | i | 39598 | 582
i_anstatabschl | i | 39598 | 461
i_chiffre | i | 39598 | 473
i_anfrager | i | 39598 | 482
i_inhaber | i | 39598 | 487 --> 6765
Die Ausführungszeit wächst von 55 ms auf 263 ms und die Tabelle belegt nun 6765 Speicherseiten anstatt der anfänglichen 1413 Seiten, obwohl keine neuen Datensätze eingefügt wurden.
Woher kommt dieser Zuwachs?
"Schuld" daran ist MVCC, Multiversion Concurrency Control, die Strategie für das Transaktionsmanagement.