merkwürdiger Performanceeinbruch

Ich habe aber Anfragen, die ebenfalls das Partitionsattribut nicht beinhalten und da funktioniert es. Zumal es ja bei den ersten Druchläufen klappt und auf einmal kommt ein Wechsel von Index Scan auf Sequenziellen Scan.
Ich muss es erstmal so hinnehmen und entsprechend anpassen.
Unter Oracle gehts halt ohne das die Partition benannt werden muss, zumal es ja eigentlich auch unter PostgreSQL funktioniert, bloß mit dieser Ausnahme nicht.

mfg
Marcus

dass indexe erstmal verwendet werden, heisst ja nicht - dass dies aufgrund der partitionierung geschieht - sondern erstmal nur weil der optimizer das fuer effizienter haelt.

dieser constraint_exclusion schalter soll ja nun genau bewirken, dass der optimizer ueber seinen normalen horizont hinaus schaut und auch die partitions berücksichtigt. du koenntest ja mal testhalber constraint_exclusion abcshalten in der config - und dann nochmal diese abfrage nackt laufen lassen - wenn meine vermutung stimmt, dann wirst du den selben effekt haben - erst index scan und dann seqscan - weil ich meine, dass der indexscan mit partitioning nix zu tun hat.

(warum er hier allerdings die strategie wechselt weiss ich auch nicht)

und dass dinge in oracle anders funktionieren ist ja normal - ist halt oracle … und nicht postgres

Der selbige Fehler kommt auch bei ausgeschalteter Constraint_exclusion. Ich habe keine Ahnung warum und weshalb. Als ob ich bei der Indexerstellung irdendwas falsche mache. Mehrdimensionale Indizes werden ja auch unterstützt.

mfg
Marcus

ruhig bleiben durchathmen

bei ausgeschalteter constraint_exclusion:

  1. frage: am anfang nimmt er index-scan?
  2. frage: danach dann irgendwann seq scan?

wenn dem so ist - dann ist das erstmal gut - weil dann wissen wir etwas

geht klar,

Anfang Index Scan und irgendwann Seq. Scan. Je nach Abfragezeitraum, den ich mit der Anfrage betrachte dauer es mal länger bis zum Wechsel auf Seq. Scan und dann mal langsamer. Aber die Summe der abgefragten Zeiträume ist in etwa identisch.
Frage ich 10 mal verschiedene Zeiträume von einem Tag, dann kommt der Wechsel. Frage ich 3 mal jeweils für einen Zeitraum von 4 Tagen, dann kommt ebenfalls der Wechel.

mfg
Marcus

halten wir fest … bei abgeschalteter constraint_exclusion nimmt er auch mal den index-scan und dann den seq-scan

wir kombinieren: das verhalten hat erstmal nichts mit constraint-exclusion zu tun … die hat auch vorher - bei eingeschaltetem ce-flag nicht funktioniert

warum es nicht funktioniert habe ich schon weiter oben vermutet:
weil du in deinen statements die partitionen ueber konstanten in deiner query selektieren musst … also die range …

postgres kann nicht dynamisch beim ausfuehren der query die partitionen ueber einen subselekt oder join ermitteln. das musst du vor der abfrage machen und die datumsbereiche als konstanten in deine query einbauen. (das steht so in der doku unter table-paritioning - links findest du auch weiter oben in diesem thread)

(denke daran die constraint_exclusion wieder einzuschalten - sonst wirds wieder nicht funzen wenn du das umbauen wirst)

Dann würde ich dir gerne mal eine andere Anfrage auf eine partitionierte Tabelle zeigen:

Tabellendefinition:

-- Table: tabrddata

-- DROP TABLE tabrddata;

CREATE TABLE tabrddata
(
  nhid bigint NOT NULL,
  cpartition character(4) NOT NULL,
  nmpoid bigint NOT NULL,
  ntoloid bigint NOT NULL,
  nrdrstatus smallint,
  nresult double precision,
  vcresult character varying(255),
  nclassid smallint,
  CONSTRAINT ckrddatahid CHECK (nhid IS NOT NULL),
  CONSTRAINT ckrddatampoid CHECK (nmpoid IS NOT NULL),
  CONSTRAINT ckrddatapartition CHECK (cpartition IS NOT NULL),
  CONSTRAINT ckrddatatoloid CHECK (ntoloid IS NOT NULL)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE tabrddata OWNER TO hs2k;

-- Index: id_nhid_tabrddata

-- DROP INDEX id_nhid_tabrddata;

CREATE INDEX id_nhid_tabrddata
  ON tabrddata
  USING btree
  (nhid);

-- Index: id_tabrddata

-- DROP INDEX id_tabrddata;

CREATE INDEX id_tabrddata
  ON tabrddata
  USING btree
  (cpartition);


-- Rule: "tabrddata_0107_insert ON tabrddata"

-- DROP RULE tabrddata_0107_insert ON tabrddata;

CREATE OR REPLACE RULE tabrddata_0107_insert AS
    ON INSERT TO tabrddata
   WHERE new.cpartition = '0107'::bpchar DO INSTEAD  INSERT INTO tabrddata_0107 (nhid, cpartition, nmpoid, ntoloid, nrdrstatus, nresult, vcresult, nclassid) 
  VALUES (new.nhid, new.cpartition, new.nmpoid, new.ntoloid, new.nrdrstatus, new.nresult, new.vcresult, new.nclassid);

passende Anfrage:

insert into tmpEZP3
  select 
       rd.nHid ,
       rd.nResult,
       rd.vcResult,
       rd.nRDRStatus,
       rd.nMPOid,
       rd.nTolOid
  from tabRDData rd
  where (nHid,cPartition) in (select nHid,cPartition from tmpEZP2);

Dabei wird doch auch dynamisch die Partition ermittelt. Läuft immer per Index Scan. Die Partitionen sind allerdings um ca. Faktor 30 größer.

mfg
Marcus

wie gesagt - da du versuchst die patitionen dynamisch zu selektieren wird das nicht funktionieren

die wahl index-scan oder seq-scan in deinem beispiel ist die klassische entscheidung des optimiziers die nichts mit partitioning zu tun hat

ich versteh da gerade nicht den Unterschied zwischen der 1. Query und der 2. Query. Da werden doch in beiden dynamisch die Partitionen selektiert, oder?
Bei der einen per funktioniert das per Index Scan, bei der anderen nicht.

mfg
Marcus

Hier noch mal die Query:

explain insert into tmpEZP6
    select
      ezp2.nHid,
      rdtid.nTraceId,
      rdtid.vcid1,
      rdtid.vcid2,
      rdtid.vcid3,
      rdtid.vcid4,
      rdtid.nHid
    from tabRDTraceId rdtid, tmpEZP2 ezp2
    where 
    rdtid.vcSetupDate = ezp2.vcSetupDate;
    and rdtid.vcLogicalId = ezp2.vcLogicalId
    and rdtid.vcLine = ezp2.vcLine;

Momentan besteht ein zweidimensionaler Index auf (vcSetupDate,vcLogicalId). Dadurch das in der Where Klausel noch ein join über vcLine erfolgt, muss man dann nicht sequenziell durch die Tabelle (in dem Fall halt alle Kindertabellen) gehen?



nun habe ich mal 2 Test vorgenommen.
1.

  • den Index habe ich von mehrdimensional auf eindimensional (nur vcSetupaDate) geändert
  • die Query habe ich so angepasst:
explain insert into tmpEZP6
    select
      ezp2.nHid,
      rdtid.nTraceId,
      rdtid.vcid1,
      rdtid.vcid2,
      rdtid.vcid3,
      rdtid.vcid4,
      rdtid.nHid
    from tabRDTraceId rdtid, tmpEZP2 ezp2
    where 
    rdtid.vcSetupDate = ezp2.vcSetupDate;
--    and rdtid.vcLogicalId = ezp2.vcLogicalId
--    and rdtid.vcLine = ezp2.vcLine;

Die Selektierung druch die Where Klausel erfolgt jetzt nur noch auf der Spalte, welche einen Index hat.
Ergebnis: immer Index Scan und immer schnell



2.

  • den Index habe ich von zweidimensionalen auf einen dreidimensional (vcSetupaDate, vcLogicalId,vcLine) geändert
  • die Query habe ich das auskommentierte wieder gestrichen:
explain insert into tmpEZP6
    select
      ezp2.nHid,
      rdtid.nTraceId,
      rdtid.vcid1,
      rdtid.vcid2,
      rdtid.vcid3,
      rdtid.vcid4,
      rdtid.nHid
    from tabRDTraceId rdtid, tmpEZP2 ezp2
    where 
    rdtid.vcSetupDate = ezp2.vcSetupDate;
    and rdtid.vcLogicalId = ezp2.vcLogicalId
    and rdtid.vcLine = ezp2.vcLine;

Ergebnis: wieder Wechsel von Index Scan auf Sequenziellen.



Liegt meiner Meinung nach am Mehrdimensionalen Index.

mfg
Marcus

nein nein nein

in keinem deiner beispiele wird eine partition selektiert, weil du die partition nicht als konstanten in deinen queries angibst

das funktioniert nur, wenn du z.B. so arbeitest

select
      ezp2.nHid,
      rdtid.nTraceId,
      rdtid.vcid1,
      rdtid.vcid2,
      rdtid.vcid3,
      rdtid.vcid4,
      rdtid.nHid
    from tabRDTraceId rdtid, tmpEZP2 ezp2
    where 
    rdtid.vcSetupDate in VALUES ('01-01-2009', ....)

du kannst auch BETWEEN nehmen oder =, >= <> usw.

Okay, aber was ist mit dieser Query:

insert into tmpEZP3
  select 
       rd.nHid ,
       rd.nResult,
       rd.vcResult,
       rd.nRDRStatus,
       rd.nMPOid,
       rd.nTolOid
  from tabRDData rd
  where (nHid,cPartition) in (select nHid,cPartition from tmpEZP2);

Wo liegt denn hier der Unterschied zur anderen hinsichtlich dynamischer Partitionsselektion?

mfg
Marcus

siehst du in dieser query irgendeine konstante?

ich nicht :wink:

ich weiss - das ist nicht besonders elegant … aber du musst vor dieser query die range selektieren … aus dieser baust du dir dann ein values (… ) konstrukt zusammen … und wendest das in einer weiteren query (der eigentlichen an)

du solltest dann in einem explain analyze feststellen, dass er garnichtmehr soviele partitionen verwendet … weil er vorher schon einige (nicht beteiligte) aussortieren kann.

im uebrigen finde ich dein(nHid cPartition) konstrukt nicht so recht zielfuehrend … das duftet danach, dass nHid in allen partitionen vorkommen kann oder sogar vorkommt … wenn natuerlich immer alle partitionen beteiligt sind waere das doof

Genau sehe ich auch nicht, und dennoch erfolgt kein sequenzieller Scan wie bei der anderen Query. Diese läuft fix per Index Scan durch.

mfg
Marcus

ja … 1. weil ohne konstanten -> keine constraint_exclusion
und 2. weil der optimizer es fuer guenstiger haelt den seq.scan zu waehlen (2. hat nix mit 1. zu tun)

mach doch mal was ich sage … du brauchst 2 queries

  1. die notwendigen paritionen berechnen …
    aus dem ergebnis baust du dir ein VALUES(wert1, wert2, … ) zusammen
  2. die abfrage ausfuehren mit partFeld = VALUES(wert1, wert2, …)

du kannst auch BETWEEN verwenden wenn du nen min max datum hast

wahrscheinlich wird das dann dein 3. problem mit dem seq. scan erledigen - weil weniger paritionen im spiel sind und somit die anderen indizes wieder “billiger” werden

Constraint Exclusion hat nix mit der Frage des Ob oder Ob nicht eines Indexscannes zu tun.

Irgendwie wird der Thread anstrengend…

Andreas

Ein bisschen kann ich noch :wink:

Sorry, für den anstregenden Thread und danke für eure Hilfe!

Ich glaube mir fehlt auch das Hintergrundwissen :frowning:

Verstehe nicht den Unterschied zwischen dieser Query 1:

insert into tmpEZP3
  select 
       rd.nHid ,
       rd.nResult,
       rd.vcResult,
       rd.nRDRStatus,
       rd.nMPOid,
       rd.nTolOid
  from tabRDData rd
  where (nHid,cPartition) in (select nHid,cPartition from tmpEZP2);

und dieser Query 2:

insert into tmpEZP6
    select
      ezp2.nHid,
      rdtid.nTraceId,
      rdtid.vcid1,
      rdtid.vcid2,
      rdtid.vcid3,
      rdtid.vcid4,
      rdtid.nHid
    from tabRDTraceId rdtid, tmpEZP2 ezp2
    where rdtid.vcSetupDate = ezp2.vcSetupDate
    and rdtid.vcLogicalId = ezp2.vcLogicalId
    and rdtid.vcLine = ezp2.vcLine;

hinsichtlich der dynamischen Paritionierung? Wo ist der Unterschied?

mfg
Marcus

:frowning: :frowning: :frowning: :frowning: :frowning:

sowohl die erste als auch die zweite query enthaelt keine konstanten … daher habe ich das als “dynamisch” bezeichnet … weil du die wahl der partitionen durch einen subselect oder join zur laufzeit der query festlegen willst

UND GENAU DAS FUNKTIONIERT NICHT :smiley:

N I M M K O N S T A N T E N !!!

du hast 2 probleme:

  1. constraint_exclusion funktioniet nicht - weil siehe dies hier -> N I M M K O N S T A N T E N !!!
  2. seq.scan … das duerfte sich sehr wahrscheinlich erledigen wenn du constraint exclusion gescheit bedienst, weil der optimizer dann evtl. andere werte erhaelt (vielleicht aber auch nicht - weil sich das nur auf die jeweilige paritiont bezieht) - wie auch immer - 2. eigentlich hat es sonst nix weiter mit 1. zu tun …

auch wenn constraint_exclusion funktioniert kann ein seq.scan in einzelnen partitionen sehr wahrscheinlich sein, wenn postgres sowieso jeweils die ganze partition lesen muss …

das kommt halt drauf an wie deine daten aussehn

Oh, da habe ich jemanden verärgert…Sorry!

Okay, dass mit den Konstanten verstehe ich. Der Grund warum der Planer mal den Index Scan in der einen Query verwendet und in der anderen den Seqenziellen Scan, obwoh in beiden die Partition dynamisch selektiert wird, bleibt offen.
Dabei belassen wir es. Die Zeit rennt mir auch davon.

Danke noch mal an euch!

mfg
Marcus