Effiziente Historisierung

Zur effizienten Verwaltung größerer Datenmengen in History-Tabellen (Zeitreihen) verwenden wir aus einer Basistabelle abgeleitete Tabellen (per CREATE TABLE … ( CHECK ( expression )) INHERITS …) , die jeweils einen bestimmten Zeitbereich (z.B. 3 Monate) enthalten (die Zeitbereiche sind disjunkt). Über den Insert-Trigger der Basis-Tabelle wird beim Schreiben relativ flott das richtige Fragment angesteuert. Beim Lesen klappert postgres aber laut Analyze jedes Fragment der Tabelle ab, was sich bei größeren Datenmengen als extrem langsam erweist. Gibt es eine integrierte Möglichkeit postgres bei der direkten Auswahl der richtigen Tabellen zur Query zu helfen?

Ob die “constraint exclusion” (auch als “partition pruning” bekannt) funktioniert, liegt vor allem an den Check constraints der Tabellen. Bitte poste doch die komplette Definition der Tabellen (oder zumindest der Teile mit den check constraints).

Vielen Dank für die Antwort. Hier beispielhaft das Statement zum Anlegen einer Tabelle der Historie (Tabellenname und Spaltennamen anonymisiert). Die Zeitbereiche aller Tabellen die aus tablename abgeleitet erzeugt wurden sind diskunkt:

CREATE TABLE tablename_
(
  CONSTRAINT tablename__time__check CHECK (zeitpunkt >= '2013-05-15 00:00:00'::timestamp without time zone AND zeitpunkt < '2013-08-13 00:00:00'::timestamp without time zone)
)
INHERITS (tablename)
WITH (
  OIDS=FALSE
);
ALTER TABLE tablename_
  OWNER TO postgres;

CREATE INDEX tablename__index
  ON tablename_
  USING btree
  (zeitpunkt, pk1, pk2);

Beim nächsten Fragement der Tabelle sieht die Zeit dann so aus usw.:

zeitpunkt >= ‘2013-08-13 00:00:00’::timestamp without time zone AND zeitpunkt < ‘2013-11-11 00:00:00’::timestamp without time zone

auf was steht der Parameter constraint_exclusion?

Und zeige bitte einen Ausführungsplan.

Das eigentlich Statement (vor allem die where Bedingung) ist auch wichtig.

Was ist mit “eigentliches Statement” gemeint? Das ist das Statement zum Anlegen einer abgeleiteten Tabelle, die Spaltennamen der Basistabelle habe ich der Übersichtlichkeit halber entfernt. Der Parameter constraint_exclusion stand auf partition, ich habe ihn jetzt auf on geändert und den Server neu gestartet. Der Plan hat sich dadurch nicht verbessert (hier gekürzt):

“Aggregate (cost=124674.50…124674.51 rows=1 width=0)”
" Output: count(*)"
" -> Append (cost=0.00…124658.46 rows=6415 width=0)"
" -> Seq Scan on public.tablename_ (cost=0.00…0.00 rows=1 width=0)"
" Filter: ((tablename_.zeitpunkt > ‘2013-10-01 00:00:00+02’::timestamp with time zone) AND (tablename_.zeitpunkt < ‘2013-10-31 00:00:00+01’::timestamp with time zone) AND (tablename_.eaid = 2024303))"
" -> Index Only Scan using tablename__2015_02_04_00_00_00_index on public.tablename__2015_02_04_00_00_00 (cost=0.15…8.21 rows=1 width=0)"
" Index Cond: ((tablename__2015_02_04_00_00_00.zeitpunkt > ‘2013-10-01 00:00:00+02’::timestamp with time zone) AND (tablename__2015_02_04_00_00_00.zeitpunkt < ‘2013-10-31 00:00:00+01’::timestamp with time zone) AND (tablename__2015 (…)"
" -> Index Only Scan using tablename__2014_11_06_00_00_00_index on public.tablename__2014_11_06_00_00_00 (cost=0.15…8.21 rows=1 width=0)"
" Index Cond: ((tablename__2014_11_06_00_00_00.zeitpunkt > ‘2013-10-01 00:00:00+02’::timestamp with time zone) AND (tablename__2014_11_06_00_00_00.zeitpunkt < ‘2013-10-31 00:00:00+01’::timestamp with time zone) AND (tablename__2014 (…)"
" -> Index Only Scan using tablename__2014_08_08_00_00_00_index on public.tablename__2014_08_08_00_00_00 (cost=0.15…8.21 rows=1 width=0)"
" Index Cond: ((tablename__2014_08_08_00_00_00.zeitpunkt > ‘2013-10-01 00:00:00+02’::timestamp with time zone) AND (tablename__2014_08_08_00_00_00.zeitpunkt < ‘2013-10-31 00:00:00+01’::timestamp with time zone) AND (tablename__2014 (…)"
" -> Index Only Scan using tablename__2014_05_10_00_00_00_index on public.tablename__2014_05_10_00_00_00 (cost=0.43…8.45 rows=1 width=0)"
" Index Cond: ((tablename__2014_05_10_00_00_00.zeitpunkt > ‘2013-10-01 00:00:00+02’::timestamp with time zone) AND (tablename__2014_05_10_00_00_00.zeitpunkt < ‘2013-10-31 00:00:00+01’::timestamp with time zone) AND (tablename__2014 (…)"
" -> Index Only Scan using tablename__2014_02_09_00_00_00_index on public.tablename__2014_02_09_00_00_00 (cost=0.15…8.21 rows=1 width=0)"
" Index Cond: ((tablename__2014_02_09_00_00_00.zeitpunkt > ‘2013-10-01 00:00:00+02’::timestamp with time zone) AND (tablename__2014_02_09_00_00_00.zeitpunkt < ‘2013-10-31 00:00:00+01’::timestamp with time zone) AND (tablename__2014 (…)"
" -> Index Only Scan using tablename__2013_11_11_00_00_00_index on public.tablename__2013_11_11_00_00_00 (cost=0.15…8.21 rows=1 width=0)"

" Index Cond: ((tablename__2005_03_28_00_00_00.zeitpunkt > ‘2013-10-01 00:00:00+02’::timestamp with time zone) AND (tablename__2005_03_28_00_00_00.zeitpunkt < ‘2013-10-31 00:00:00+01’::timestamp with time zone) AND (tablename__2005 (…)"
" -> Index Only Scan using tablename__2004_12_28_00_00_00_index on public.tablename__2004_12_28_00_00_00 (cost=0.15…8.21 rows=1 width=0)"
" Index Cond: ((tablename__2004_12_28_00_00_00.zeitpunkt > ‘2013-10-01 00:00:00+02’::timestamp with time zone) AND (tablename__2004_12_28_00_00_00.zeitpunkt < ‘2013-10-31 00:00:00+01’::timestamp with time zone) AND (tablename__2004 (…)"
" -> Index Only Scan using tablename_remainder_index on public.tablename_remainder (cost=0.15…8.21 rows=1 width=0)"
" Index Cond: ((tablename_remainder.zeitpunkt > ‘2013-10-01 00:00:00+02’::timestamp with time zone) AND (tablename_remainder.zeitpunkt < ‘2013-10-31 00:00:00+01’::timestamp with time zone) AND (tablename_remainder.eaid = 2024303))"

Das SELECT Statement bei dem kein partition pruning erfolgt. Speziell die WHERE Bedingung ist interessant, da diese zum Eliminieren von Partitionen verwendet wird.

Nachtrag zu castorp: die Where-Condition muß zu den Constraint Exclusions passen.

Verstehe. Das Statement ist:

select count(*) from tablename where zeitpunkt > ‘2013-10-01 00:00:00’ and zeitpunkt < ‘2013-10-31 00:00:00’ and pk1 = 2024303;

Auch ohne den letzten Teil der Condition werden alle Fragmente der Basis-Tabelle herangezogen.

Index Cond: ((tablename__2015_02_04_00_00_00.zeitpunkt > ‘2013-10-01 00:00:00+02’::timestamp with time zone)

versus

‘2013-10-01 00:00:00’

Du hast unterschiedliche Datentypen, einmal timestamptz, einmal timestamp.

Wie muss die Query denn dann aussehen, damit sie zur Tabelle passt? Ich habe es jetzt einmal so probiert, aber das bringt keine sichtbare Verbesserung:

select count(*) from tablename_ where zeitpunkt > ‘2013-10-01 00:00:00’::timestamp without time zone and zeitpunkt < ‘2013-10-31 00:00:00’::timestamp without time zone and eaid = 2024303;

Du hast die Spalten aber als timestamptz, oder?


test=# create table x_master(ts timestamptz);
CREATE TABLE
test=*# create table x_2014 (constraint x_check check (ts >= '2014-01-01'::timestamptz and ts <= '2014-12-31 23:59:39'::timestamptz)) inherits (x_master);
CREATE TABLE
test=*# create table x_2015 (constraint x_check check (ts >= '2015-01-01'::timestamptz and ts <= '2015-12-31 23:59:39'::timestamptz)) inherits (x_master);
CREATE TABLE

test=*# explain select * from x_master where ts = '2015-03-30 16:37:00'::timestamptz;
                                QUERY PLAN
---------------------------------------------------------------------------
 Append  (cost=0.00..36.75 rows=12 width=8)
   ->  Seq Scan on x_master  (cost=0.00..0.00 rows=1 width=8)
         Filter: (ts = '2015-03-30 16:37:00+02'::timestamp with time zone)
   ->  Seq Scan on x_2015  (cost=0.00..36.75 rows=11 width=8)
         Filter: (ts = '2015-03-30 16:37:00+02'::timestamp with time zone)
(5 rows)

Er scannt die Master und die betroffene Slave-Table.

Durchgehend gleiche Datentypen verwenden!

Master und abgeleitete Tabellen haben den gleichen Type für den Zeitstempel, ohne timezone (siehe meine Antwort von 14:31 Uhr). In der Query war das nicht spezifiziert, das hat postgres auf mit timezone ergänzt. Wenn ich es in der Query explizit ohne timezone angebe funktioniert es auch nicht.

Hrm.

test=# create table x_master(ts timestamp);
CREATE TABLE
test=*# create table x_2014 (constraint x_check check (ts >= '2014-01-01'::timestamp and ts <= '2014-12-31 23:59:39'::timestamp)) inherits (x_master);
CREATE TABLE
test=*# create table x_2015 (constraint x_check check (ts >= '2015-01-01'::timestamp and ts <= '2015-12-31 23:59:39'::timestamp)) inherits (x_master);
CREATE TABLE
test=*# explain select * from x_master where ts = '2015-03-30 16:37:00'::timestamp;
                                QUERY PLAN
---------------------------------------------------------------------------
 Append  (cost=0.00..36.75 rows=12 width=8)
   ->  Seq Scan on x_master  (cost=0.00..0.00 rows=1 width=8)
         Filter: (ts = '2015-03-30 16:37:00'::timestamp without time zone)
   ->  Seq Scan on x_2015  (cost=0.00..36.75 rows=11 width=8)
         Filter: (ts = '2015-03-30 16:37:00'::timestamp without time zone)
(5 rows)

Kaum macht man es richtig… Es gab tatsächlich noch einen Unterschied zwischen der master-tabelle und der Condition in den abgeleiteten Tabellen. Nun funktioniert es wie erwartet, vielen Dank.

smile

Nachdem die direkte Selektion der richtigen Tabellen jetzt klappt habe ich jetzt diese Datenbank mit Test-Daten befüllt. Eine Basis-Tabelle hat 40 daraus abgeleitete Tabellen, jede dieser Tabellen deckt jeweils ein Quartal ab. Jedes Quartal enthält ca. 330 Mio. Datansätze, eine der Quartals-Tabellen ist voll (im Sinne der abgedeckten Zeit), die anderen sind leer. Was mir nun als erstes auffällt ist die Dauer von Abfragen, die postgres eigentlich recht schnell über den Index beantworten können müsste. Ein select count() from basistabelle dauert z.B. 12 Minuten. Ein select count() mit Zeiteinschränkung (ein Monat) dauert 10 Minuten. Wenn ich zusätzlich noch auf eine Id einschränke dauert es 7 Minuten. Währenddessen ist der IO dieses Servers auf 100%, der Test läuft hier exclusiv und es gibt keine konkurrierenden Zugriffe. Der selct count(*) ohne Bedingung führt laut analyze zu einem full table scan, bei den beiden anderen Varianten wird auch der Index verwendet.

Ja, ein select count() ohne where ist ein Seqscan. Und wenn Du das mit Where machst, wird es zwar u.U. ein Indexscan, aber dennoch ist in der Tabelle zu prüfen, ob der Record für die Transaktion sichtbar ist. Du siehst das bei explain analyse, recheck condition …

Vermeide select count(*). Wenn Du eine ungefähre Zahl wissen willst frage pg_class ab.

Also kann postgres die Mengen nicht allein mit dem Blick in den Index ermitteln. Der Index für eine Tabelle ist hier konkret aber auch schon 19 GB gross, den hält er sich dann auch nicht mal eben komplett im Hauptspeicher um weitere Anfragen zur Tabelle danach schneller beantworten zu können.

Korrekt. Es gibt ab 9.x (ich weiß x grad nicht) in bestimmten Konstellationen auch Index-Only-Scans, aber geh erst mal davon aus, daß grade bei select count() ein Blick in die Tabelle nötig ist.