Ergebnis 1 bis 4 von 4

Thema: Performanceeinbruch: Indexbenutzung bei Partitionen?

  1. #1
    Kenny82 ist offline Neuer Benutzer
    Registriert seit
    11.05.2011
    Beiträge
    2

    Standard Performanceeinbruch: Indexbenutzung bei Partitionen?

    Hallo,

    ich habe eine Tabelle mit ~5Millionen Zeilen. Es gibt drei relevante Felder, nach denen gesucht wird, nennen wir sie mal x1, x2, x3. Weil aber zu einem einzelnen Wert von x1 etwa 100000 Zeilen gehören, habe ich die Tabelle in Partitionen unterteilt, so dass in jeder Partition x1 einen konstanten wert hat. Das ist mittels einer "check"-Bedingung auch verankert. Die Partitionierung war notwendig, weil sonst "update"-Kommandos unsäglich langsam waren.

    ich habe auch "SET constraint_exclusion = on;"
    Die Unter-Tabellen heißen jetzt mal tabelle_ux1
    Wenn ich eine Abfrage "SELECT irgendwas FROM tabelle where x1 = XX and x2 > ... and x3 < ... limit ..." mache, braucht das _wesentlich_ länger, als wenn ich das auf die Untertabelle direkt mache "SELECT irgendwas FROM tabelle_ux1 where ..." und exakt die gleichen Bedingungen mache.
    Es gibt aber nur ca. 100 Untertabellen - die Suche nach der richtigen Untertabelle kann es also nicht sein.

    Ein ähnlicher Beitrag, der mir aber nicht weitergeholfen hat, ist auch
    http://www.pg-forum.de/sql/4014-merk...eeinbruch.html

    Wie kann ich die Ausgabe entsprechend beschleunigen - kann ich sagen "suche zuerst die Tabelle raus, und dann mache den rest?"

    Hier mal die Ausgaben von Explain:

    Die große Tabelle heißt kin_data, die untertabelle kin_data_21, und trial_id ist "x1", also konstant in kin_data_21 (nämlich: 21)

    Zuerst: auf der großen Tabelle:
    Limit (cost=14751.37..14751.39 rows=10 width=8)
    -> Sort (cost=14751.37..14813.78 rows=24964 width=8)
    Sort Key: public.kin_data.kin_time
    -> Result (cost=0.00..14211.90 rows=24964 width=8)
    -> Append (cost=0.00..14211.90 rows=24964 width=8)
    -> Seq Scan on kin_data (cost=0.00..11.22 rows=1 width=8)
    Filter: ((kin_frame > 100) AND (kin_time < 100::double precision) AND (trial_id = 21))
    -> Index Scan using idx_kin_time_21 on kin_data_21 kin_data (cost=0.00..14200.68 rows=24963 width=8)
    Index Cond: (kin_time < 100::double precision)
    Filter: ((kin_frame > 100) AND (trial_id = 21))


    Jetzt: auf der "kleinen" Tabelle
    Limit (cost=0.00..5.69 rows=10 width=8)
    -> Index Scan using idx_kin_time_21 on kin_data_21 (cost=0.00..14191.53 rows=24963 width=8)
    Index Cond: (kin_time < 100::double precision)
    Filter: ((kin_frame > 100) AND (trial_id = 21))

  2. #2
    ads
    ads ist offline Super-Moderator
    Registriert seit
    08.02.2006
    Ort
    MD
    Beiträge
    4.055

    Standard

    Bei der kleinen Tabelle fehlen so Kleinigkeiten wie das Sortieren im Plan.
    Bist du dir sicher, das du in beiden Fällen die gleiche Anfrage ausführst?

    Verwende bitte außerdem [ code ] ... [ / code ] zum Darstellen deiner Ausgaben. Das macht die Sache lesbarer.
    Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören

    Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler

    PostgreSQL Service & Support

  3. #3
    Kenny82 ist offline Neuer Benutzer
    Registriert seit
    11.05.2011
    Beiträge
    2

    Standard

    Hallo und danke für die Antwort (und ggf. sorry für einen Doppel-Post dieser Antwort??)

    also, ich habe mittlerweile folgende Erkenntnisse:
    (1) Das Problem existiert nur, wenn man ein "Limit" in die Query einbaut. Ich schätze, dass liegt an einer unterschiedlichen Indexbenutzung.
    (2) Das Problem liegt auch - wie du schon vermutet hast - am sortieren, wenn hier auch nur zu einem kleinen Teil: Die Daten sind in der kleinen Tabelle schon entsprechend sortiert, so dass die Abfrage auf der Unter-Tabelle darauf verzichten kann. Hier wäre es aber schön, wenn der Query-Planer das checkt: Wenn er die Daten nur aus einer Tabelle holt (ist doch oft der Fall: check-constraints!), und die dort bereits sortiert sind, dann kann er auf's sortieren Verzichten.
    (3) Das Haupt-Problem waren wohl arrays! ich hatte 30 Felder mit real[]-arrays (länge: immer 4). Die hab' ich jetzt auf einzelne Felder verteilt. Das hat (a) die Tabellengröße von ~78MB auf ~42MB verkleinert (jeweils nach vacuum), und komischerweise auch die indizes von ~8MB auf ~3MB. Und die Abfragen sind jetzt um ein mehrfaches schneller.

    In der Summe ist eine Abfrage auf der gesamten Tabelle ohne sortieren jetzt nur "ein bisschen" (~faktor 2) langsamer: 270 vs. 150 msec (Daten im Cache).

    Mein Fazit: Arrays vermeiden wo es geht!

    Ich hab' jetzt auch einige meiner Abfragen so gebaut, dass sie oft direkt auf die Unter-tabellen zugreifen.

  4. #4
    ads
    ads ist offline Super-Moderator
    Registriert seit
    08.02.2006
    Ort
    MD
    Beiträge
    4.055

    Standard

    Also, Daten in einer Tabelle liegen nie sortiert vor. Das ist schlicht nicht der Fall.
    Durch Änderungen an den Daten werden Tupel in der Tabelle hin und her geschoben, so das diese physikalisch nicht mehr in einer auf- oder absteigende Reihenfolge vorliegen.

    Selbst wenn du für eine bestimmte Tabelle sicherstellst, das diese Daten derzeit sortiert vorliegen: PostgreSQL verwendet synchronisierte Scans.
    Das bedeutet: zwei oder mehr gleichzeitige Scans auf der gleichen Tabelle laufen nur einmalig zur gleichen Zeit. Alle Queries hängen sich quasi beim ersten Query ein und lassen den die Daten mitlesen. Sobald der fertig ist, wird bloß noch die bisher nicht erledigte Arbeit erledigt. Das sorgt dafür, das deine Daten dann unsortiert aus deiner eigentlich physikalisch sortierten Tabelle kommen.

    Also immer ein ORDER BY verwenden, wenn du eine Sortierung erreichen möchtest.



    Ach ja: Arrays sind böse ;-)
    Aber das hast du ja schon herausgefunden.
    Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören

    Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler

    PostgreSQL Service & Support

Ähnliche Themen

  1. merkwürdiger Performanceeinbruch
    Von drooper im Forum SQL
    Antworten: 42
    Letzter Beitrag: 03.12.2009, 14:38
  2. Partitionen
    Von klimaschreck im Forum Allgemeines
    Antworten: 9
    Letzter Beitrag: 02.12.2009, 10:12

Stichworte

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •  

Links: Tobias Bauer - IT- und Funkdienstleistungen   -   Bilderrahmen-kaufen.de - Der Onlineshop für Bilderrahmen & Fotorahmen   -   Funkforum