merkwürdiger Performanceeinbruch

Hallo,

ich habe heute einen merkwürdigen Performanceeinbruch bei einem Select-Statement auf die partitionierten Tabellen bemerkt. Merkwürde ist das aus folgendem Grund:
Meine Query besteht aus mehreren Einzelabfragen und ist über einen Datumswert begrenzt. Ich führe diese komplette Query aus (Betrachtungszeitrum 1 Tag). Das Ergebniss ist schnell da. Ich wiederhole den Test mit einem anderem Datumsbereich (mal mit 2 Tagen oder auch 3 Tagen als Betrachtungszeitraum) und die Query ist noch immer schnell. Übersteige ich allerdings einen bestimmten Betrachtungszeitraum (ergibt sich aus Addition der Betrachtungszeiträume, die ich bisher durch die Query ausgeführt habe) gibt es einen Performanceeinbruch um den Faktor 100. Führe ich darauffolgende die Query mit dem ersten Betrachtungszeitraum noch mal aus, benötigt sie ebenfalls 100 mal so lange. Zuvor ging das noch in 1 Sekunde und die identische Anfrage braucht jetzt 100 mal so lange. Nun habe ich die Anfrage zerlegt um den Schuldigen zu identifizieren und dabei Folgendes gefunden.
Der Select über die partitionierten Tabellen wird am Anfang mittels Index Scan über jede Partition ausgeführt --> siehe Datei tabrdtraceid_1 und nach dem Performanceeinbruch wird bei Ausführung der identischen Anfrage stattdessen ein sequenzieller Scan pro Partition ausgeführt --> siehe Datei tabrdtraceid_2. Warum nur? Ich finde nichts vergleichbares im Internet, dass das mal mit index scan und dann wieder mittels sequenziellem scan durchgeführt wird. Bei Ausführung der Queries werden die paritionierten Tabellen nicht gefüllt oder so. Die bleiben unberührt.

Den Performancetest und -vergleich kann ich mir so schenken. Ich verstehe das nicht. Ich habe es mit der ursprünglichen Konfiguration bei Installation und mit einer modifizierten probiert. Bei der modifizierten tritt der Effekt etwas später auf, aber genauso schlimm.
Hat jemand von euch eine Idee, was das sein könnte?

LG
Marcus

SET constraint_exclusion = on;

?

Ja ist an. Hier die 2 Konfigurationen mit denen ich es probiert habe.

LG
Marcus

Was mir erst mal auffällt: signifikante Unterschiede in der erwarteten Datenmenge. Ein explain ohne analyse liefert leider nur die schätzungen, nicht die realen Tatsachen.

Andreas

vielleicht kann man folgendes noch prüfen:

5.9.6. Caveats

The following caveats apply to partitioned tables:

  • There is no automatic way to verify that all of the CHECK constraints are mutually exclusive. It is safer to create code that generates partitions and creates and/or modifies associated objects than to write each by hand.
  • The schemes shown here assume that the partition key column(s) of a row never change, or at least do not change enough to require it to move to another partition. An UPDATE that attempts to do that will fail because of the CHECK constraints. If you need to handle such cases, you can put suitable update triggers on the partition tables, but it makes management of the structure much more complicated.
  • If you are using manual VACUUM or ANALYZE commands, don’t forget that you need to run them on each partition individually. A command like:
    ANALYZE measurement; will only process the master table.

The following caveats apply to constraint exclusion:

  • Constraint exclusion only works when the query’s WHERE clause contains constants. A parameterized query will not be optimized, since the planner cannot know which partitions the parameter value might select at run time. For the same reason, “stable” functions such as CURRENT_DATE must be avoided.
  • Keep the partitioning constraints simple, else the planner may not be able to prove that partitions don’t need to be visited. Use simple equality conditions for list partitioning, or simple range tests for range partitioning, as illustrated in the preceding examples. A good rule of thumb is that partitioning constraints should contain only comparisons of the partitioning column(s) to constants using B-tree-indexable operators.
  • All constraints on all partitions of the master table are examined during constraint exclusion, so large numbers of partitions are likely to increase query planning time considerably. Partitioning using these techniques will work well with up to perhaps a hundred partitions; don’t try to use many thousands of partitions.

@akretschmer

meinst eine explain analyze select…?

@symbiont

ich gehe das gerade durch. Die Tabellen mit Check und Rule werden über PL/pgSQL Skript erzeugt und überschneiden sich nicht.

Rest prüfe ich

LG
Marcus

Meß mal nach, wieviel Speicher der CPU nach den einzelnen Durchläufen wieder zur Verfügung steht.



mfg

fristo

Hat an der Stelle keinen Einfluß auf den generierten Plan. Sprich, es ist egal, ob die Kiste z.B. swappt oder noch massig RAM frei ist, dies tangiert den Planner nicht in seiner Entscheidung ob Index-Scan oder Seq-Scan.

Falt ist, daß er im Falle Seq-Scan um z.T. mehrere Zehnerstellen mehr an Daten erwartet als beim Index-Scan.

Was mich noch wundert, warum überhaupt alle Tabellen abgefragt werden, wenn doch constraint exclusion an ist. Passen die Abfragen den eigentlich überhaupt zur Partitionierung?

Andreas

ich tippe auf die fehlenden konstanten in der abfrage die der constraint exclusion auf die spruenge helfen soll (oder konstante ranges)

der index-scan hat vielleicht vorher nur geklappt, weil die indices auch durch die statistiken bereits eine guenstigere wahl sind und nicht weil constraint-exclusion griff

der optimizer muss ja VORHER wissen was er excludieren kann - und nicht erst step-by-step wenn er die abfrage durchfuehrt.

Constraint exclusion only works when the query’s WHERE clause contains constants. A parameterized query will not be optimized, since the planner cannot know which partitions the parameter value might select at run time. For the same reason, “stable” functions such as CURRENT_DATE must be avoided.

Was meinst du damit genau? Inwiefern muss die Query umgestellt werden? Ich habe den Effekt nicht, wenn ich die Parition direkt anspreche…was aber auch zuerwarten war.

Die Abfrage habe ich ja mit angehangen. Was muss ich da speziell für die Partitionierung beachten bzw. anpassen?

LG
Marcus

du musst vorher mt einer abfrage die constraint-werte der gewuenschten partitionen berechnen

diese verwendest du dann als konstante werte zb. mittels IN VALUES( …) oder BETWEEN oder constraint = … etc. in der query auf deine partitionierten “master”

offenbar funktioniert constraint exclusion nicht, wenn die einschraenkung erst bei der query selbst ermittelt werden muss - z.B. durch ne subquery oder nen join

Dann wird es ähnlich schnell sein, als wenn ich die Partition direkt benenne. Aber da ist doch der Vorteil der Partitionierung hinfällig.
Habe auch andere selects auf partitionierte Tabellen, die das 50 fache an Daten haben. Und da funktioniert es.

Nach dem die Datenbank einmal in den Knien war, sind auch sämtliche anderen Operationen langsamer. Das Löschen der temporären Tabellen dauert statt 0,070 Sekunden plötzlich 4,8 Sekunden. Das kann es doch nicht sein. Als ob irgendein Speicher / Cache zu ist und dann alles nur noch über Platte oder so geht.

Im Notfall bin ich gezwungen die Partitionen zu ermitteln.

LG
Marcus

nun wenn das ergebnis ist, dass sowieso alle partitionen gebraucht werden, dann solltest du deine partitionierung ueberdenken …

also ich führe jetzt nur das select auf die Tabelle bzw. die Partitionen aus ohne Verschachtelung oder so und habe den gleichen Effekt. In einer neuen Session ist alles wieder okay und die Queries sind so fix wie am Anfang.

Das Partitioning ist schon nicht überflüssig, aber regt mich gerade ein wenig auf :frowning:

LG
Marcus

würde mich auch aufregen … aber ich habe gerade keine andere idee dazu …

Du hast zwar die Anfrage mitgeschickt, aber ich vermisse die Tabellendefinition(en) samt Constraints, Rules und Trigger.

Ich bin jetzt momentan nicht auf Arbeit, erst am Montag wieder. Da kann ich das noch schicken. Es gibt bisher eine Insert-Rule pro Partition und Trigger existieren nicht. Sind die Rules denn entscheidend bei einem Select?

mfg
Marcus

Wenn sie den SELECT nicht betreffen: nein.

Andreas

Hier mal die Tabellendefinition mit dem was dazu gehört, so wie ich es in Erinnerung zu glauben habe.

CREATE TABLE "tabrdtraceid" (
    "vcsetupdate" timestamp NOT NULL,
    "vclogicalid" varchar(30) NOT NULL,
    "cpartition" char(4) NOT NULL,
    "ntraceid" bigint NOT NULL,
    "vcid1" varchar(30),
    "vcid2" varchar(30),
    "vcid3" varchar(30),
    "vcid4" varchar(30),
    "nhid" bigint NOT NULL,
    "vcline" varchar(80)
);

-- CREATE INDEX idxrdtraceidkey ON "tabrdtraceid" ("vcsetupdate","vclogicalid");

CREATE TABLE TABRDTRACEID_0101 (
    CHECK (CPARTITION = '0101')
    ) INHERITS (TABRDTRACEID);

create index idxcpartition_0101 ON "tabrdtraceid" ("cpartition");
create index idxrdtraceidkey_0101 ON "tabrdtraceid" ("vcsetupdate","vclogicalid");


CREATE TABLE TABRDTRACEID_0102 (
    CHECK (CPARTITION = '0102')
    ) INHERITS (TABRDTRACEID);

create index idxcpartition_0102 ON "tabrdtraceid" ("cpartition");
create index idxrdtraceidkey_0102 ON "tabrdtraceid" ("vcsetupdate","vclogicalid");

-- davon gibt es dann noch weitere Partitionen mit Check über cpartition
--....
--....
--....


-- hier mal ein Beispiel für eine Insert Rule

CREATE OR REPLACE RULE TABRDTRACEID_INSERT_0101 AS ON INSERT TO TABRDTRACEID WHERE (
CPARTITION = '0101') DO INSTEAD INSERT INTO TABRDTRACEID VALUES (NEW.*);

exakt diese art von bedingungen - incl. der konstanten (die du vorher berechnen mussst) :

 CPARTITION = '0102'

müssen in deinen abfragen auftauchen … damit die paritions ausgeählt werden koennnen.

auch

 CPARTITION BETWEEN '0102' ...

oder

 CPARTITION iN ('0102' ... )

sind denkbar