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-merkw-rdiger-performanceeinbruch.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))

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.

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.

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 :wink:
Aber das hast du ja schon herausgefunden.