Verständnisfragen zum Planer

Ich arbeite im Bereich Anfragenobtimierung auf codierten XML Daten.
Dadurch sind meine Daten absolut statisch, ich lebe also davon das der Planer die richtigen Indices aus einer relativ großen Menge wählt(deren Erstellung mir durch die statischen Daten nicht weh tun)

Ideal wäre natürlich wenn alle Informationen aus Indices gewonnen werden könnten, hier dann auch meine erste Frage:
weder explain noch explain analyze liefert explizite Aussagen über disc reads. explain verbose beinhaltet solche Informationen ebenfals nicht (soweit ich das übersehe).
Gibt es eine möglichkeit dem Planer genauer auf den Zahn zu fühlen?

Ich verwende sehr viele multi-attribute Indices, an manchen mir nicht ganz vorhersehbaren Stellen verwendet postgres ein Konstrukt namens Bitmap Index Scan
Bsp.:
" …
“-> Bitmap Heap Scan on xmldoc a0005 (cost=63.42…1877.71 rows=2607 width=34)”
“Recheck Cond: ((a0005.level = (a0004.level + 1)) AND (a0005.kind = 3) AND (a0005.pre >= (a0004.pre + 1)) AND (a0005.pre <= (a0004.pre + a0004.size)))”
" -> Bitmap Index Scan on lkp (cost=0.00…62.77 rows=2607 width=0)"
“Index Cond: ((a0005.level = (a0004.level + 1)) AND (a0005.kind = 3) AND (a0005.pre >= (a0004.pre + 1)) AND (a0005.pre <= (a0004.pre + a0004.size)))”
"…

Laut Documentation werden Bitmap Indices verwendet um mehrere Indices miteinander (eben über eine Bitmap und die dazugehörigen Operationen) zu verbinden.
Das macht über einem Index wenig Sinn und ist auch nicht wirklich schnell, deswegen die nächste Frage: Was genau geschieht bei solch einer Operation und warum an einer solchen Stelle?

Füer die 3te Frage sind hier 2 Ausschnitte aus Quieries:
q1

AND a0003.parent = a0008.pre
AND a0003.name = ‘site’
AND a0003.uri = ‘’
AND a0003.kind = 1
AND a0004.parent = a0003.pre
AND a0004.name = ‘closed_auctions’
AND a0004.uri = ‘’
AND a0004.kind = 1
AND a0005.parent = a0004.pre
AND a0005.name = ‘closed_auction’
AND a0005.uri = ‘’
AND a0005.kind = 1
AND a0006.parent = a0005.pre

und
Q2

AND a0003.parent = a0008.pre
AND a0003.name = ‘site’
AND a0003.uri = ‘’
AND a0003.kind = 1
AND a0003.level = 1
AND a0004.parent = a0003.pre
AND a0004.name = ‘closed_auctions’
AND a0004.uri = ‘’
AND a0004.kind = 1
AND a0004.level = 2
AND a0005.parent = a0004.pre
AND a0005.name = ‘closed_auction’
AND a0005.uri = ‘’
AND a0005.kind = 1
AND a0005.level = 3
AND a0006.parent = a0005.pre

Der Planer macht folgendes:
Q1(Verbunden durch nested loop joins)

" -> Index Scan using knuparp on xmldoc a0003 (cost=0.00…8.38 rows=1 width=8)"
“Index Cond: ((a0003.kind = 1) AND ((a0003.name)::text = ‘site’::text) AND ((a0003.uri)::text = ‘’::text) AND (a0003.parent = a0008.pre))”
“-> Index Scan using xmldoc_pkey on xmldoc a0005 (cost=0.00…7.75 rows=1 width=12)”
“Index Cond: (a0005.pre = a0006.parent)”
“Filter: (((a0005.uri)::text = ‘’::text) AND (a0005.kind = 1) AND ((a0005.name)::text = ‘closed_auction’::text))”
“-> Index Scan using xmldoc_pkey on xmldoc a0004 (cost=0.00…5.98 rows=1 width=8)”
“Index Cond: (a0004.pre = a0005.parent)”
“Filter: (((a0004.uri)::text = ‘’::text) AND (a0004.kind = 1) AND ((a0004.name)::text = ‘closed_auctions’::text))”

Q2(Verbunden durch nested loop joins mit filter level)

“-> Index Scan using knulpar on xmldoc a0003 (cost=0.00…8.37 rows=1 width=8)”
“Index Cond: ((a0003.kind = 1) AND ((a0003.name)::text = ‘site’::text) AND ((a0003.uri)::text = ‘’::text) AND (a0003.level = 1) AND (a0003.parent = a0008.pre))”
“-> Index Scan using knulpar on xmldoc a0004 (cost=0.00…8.37 rows=1 width=8)”
“Index Cond: ((a0004.kind = 1) AND ((a0004.name)::text = ‘closed_auctions’::text) AND ((a0004.uri)::text = ‘’::text) AND (a0004.level = 2) AND (a0004.parent = a0003.pre))”
“-> Index Scan using knulpar on xmldoc a0005 (cost=0.00…8.37 rows=1 width=12)”
“Index Cond: ((a0005.kind = 1) AND ((a0005.name)::text = ‘closed_auction’::text) AND ((a0005.uri)::text = ‘’::text) AND (a0005.level = 3) AND (a0005.parent = a0004.pre))”

Der Plan für Q2 entspricht der Erwartung, die Verwendung der PKey`s in Q1 nicht. Die Indices mit entsprechenden Atributen existieren. Ist das der Ausdruck von postgres dafür das der Planer sich verrannt hat?

Explain Analyse gibt Dir mehr Informationen, ansonsten kannst Du auch pg_stat_user_tables und pg_stat_user_indexes befragen.


Ich verwende sehr viele multi-attribute Indices, an manchen mir nicht ganz vorhersehbaren Stellen verwendet postgres ein Konstrukt namens Bitmap Index Scan


Laut Documentation werden Bitmap Indices verwendet um mehrere Indices miteinander (eben über eine Bitmap und die dazugehörigen Operationen) zu verbinden.
Das macht über einem Index wenig Sinn und ist auch nicht wirklich schnell, deswegen die nächste Frage: Was genau geschieht bei solch einer Operation und warum an einer solchen Stelle?

Meine Erfahrungen sind da anders, der Sprung von 8.0 zu 8.1, als das reinkam, war z.T. sehr heftig im Sinne einer sehr deutlichen Performance-Verbesserung.

Probier doch mal Deine Abfragen mit set enable_bitmapscan=off und schau, was passiert.

Füer die 3te Frage sind hier 2 Ausschnitte aus Quieries:


Der Plan für Q2 entspricht der Erwartung, die Verwendung der PKey`s in Q1 nicht. Die Indices mit entsprechenden Atributen existieren. Ist das der Ausdruck von postgres dafür das der Planer sich verrannt hat?

Aus solchen Fragmenten heraus nachzuvollziehen was passiert hab ich grad weder Lust noch Zeit. Für sowas bräuchte man die Tabellendefinition, das komplette Select sowie Explain Analyse in einer lesbaren Form, also innerhalb von Code-Tags.

Andreas

Explain Analyse gibt Dir mehr Informationen, ansonsten kannst Du auch pg_stat_user_tables und pg_stat_user_indexes befragen.

Explain analyze und explain analyse scheint wohl das selbe zu sein, und hilft mir in dieser Hinsicht nicht weiter da im Ausführungsplan disc-reads nicht erscheinen.
Typische Fragen für mich wären: An welcher Stelle werden Tupel von der Platte geholt? Welche Joins können auf index only Basis ausgeführt werden oder wie muss das Encoding verändert werden um diskreads zu minimieren/optimieren.

Meine Erfahrungen sind da anders, der Sprung von 8.0 zu 8.1, als das reinkam, war z.T. sehr heftig im Sinne einer sehr deutlichen Performance-Verbesserung.

Probier doch mal Deine Abfragen mit set enable_bitmapscan=off und schau, was passiert.

Verwendet er leider immernoch, ich konnte das bisher nur verhindern indem ich sehr spezielle Indices nur für die Query angelegt habe, was diese zwar beschleunigt hat, jedoch bei anderen zu Fehlplanungen geführt hat, was allerdings noch bei den meisten Dbms aufgetreten ist.
Ich vermute ein Verständnisproblem bei mir über was dabei genau geschieht, damit man darauf aufbauen kann und dem Planer helfen.

Aus solchen Fragmenten heraus nachzuvollziehen was passiert hab ich grad weder Lust noch Zeit. Für sowas bräuchte man die Tabellendefinition, das komplette Select sowie Explain Analyse in einer lesbaren Form, also innerhalb von Code-Tags.

ich hoffte die Snipets würden ausreichen… aber dann eben Komplett, ist leider die einfachste Quiery bei der das aufgetreten ist.

table xmldoc(pre int not null primary key,
size int not null,
level smallint not null,
kind smallint,
name varchar(32),
value varchar(100),
uri varchar(100),
parent int)

Anzahl Tupel ca 500 000 (gleicher Plan auch bei 5 000 000 Tupeln)
Indices existieren alle auch nur ansatzweise sinvoll Vorstellbaren in dieser Testinstanz. (ein Index kv bedeutet (kind,value))


(SELECT DISTINCT a0013.pre,
a0013.size,
a0013.kind,
a0013.value,
a0013.name, a0013.uri, a0011.pre AS dist01, a0005.pre AS dist00
FROM xmldoc AS a0003,
xmldoc AS a0004,
xmldoc AS a0005,
xmldoc AS a0006,
xmldoc AS a0007,
xmldoc AS a0008,
xmldoc AS a0009,
xmldoc AS a0010,
xmldoc AS a0011,
xmldoc AS a0012,
xmldoc AS a0013
WHERE a0003.parent = a0008.pre
AND a0003.name = ‘site’
AND a0003.uri = ‘’
AND a0003.kind = 1
AND a0004.parent = a0003.pre
AND a0004.name = ‘closed_auctions’
AND a0004.uri = ‘’
AND a0004.kind = 1
AND a0005.parent = a0004.pre
AND a0005.name = ‘closed_auction’
AND a0005.uri = ‘’
AND a0005.kind = 1
AND a0006.parent = a0005.pre
AND a0006.name = ‘buyer’
AND a0006.uri = ‘’
AND a0006.kind = 1
AND a0007.parent = a0006.pre
AND a0007.name = ‘person’
AND a0007.uri = ‘’
AND a0007.kind = 2
AND a0008.kind = 6
AND a0008.value = ‘auction.xml’
AND a0009.parent = a0008.pre
AND a0009.name = ‘site’
AND a0009.uri = ‘’
AND a0009.kind = 1
AND a0010.parent = a0009.pre
AND a0010.name = ‘people’
AND a0010.uri = ‘’
AND a0010.kind = 1
AND a0011.parent = a0010.pre
AND a0011.name = ‘person’
AND a0011.uri = ‘’
AND a0011.kind = 1
AND a0012.parent = a0011.pre
AND a0012.name = ‘id’
AND a0012.uri = ‘’
AND a0012.kind = 2
AND a0007.value = a0012.value
AND a0013.pre BETWEEN a0005.pre AND (a0005.pre + a0005.size)
ORDER BY a0011.pre ASC, a0005.pre ASC, a0013.pre ASC);

"Unique  (cost=4200.94..4200.96 rows=1 width=40)"
"  ->  Sort  (cost=4200.94..4200.94 rows=1 width=40)"
"        Sort Key: a0011.pre, a0005.pre, a0013.pre, a0013.size, a0013.kind, a0013.value, a0013.name, a0013.uri"
"        ->  Nested Loop  (cost=0.00..4200.93 rows=1 width=40)"
"              ->  Nested Loop  (cost=0.00..78.38 rows=1 width=12)"
"                    Join Filter: (a0003.pre = a0004.parent)"
"                    ->  Nested Loop  (cost=0.00..72.38 rows=1 width=20)"
"                          ->  Nested Loop  (cost=0.00..64.61 rows=1 width=12)"
"                                ->  Nested Loop  (cost=0.00..56.22 rows=1 width=16)"
"                                      ->  Nested Loop  (cost=0.00..48.32 rows=1 width=16)"
"                                            ->  Nested Loop  (cost=0.00..39.90 rows=1 width=26)"
"                                                  ->  Nested Loop  (cost=0.00..32.73 rows=1 width=12)"
"                                                        ->  Nested Loop  (cost=0.00..24.74 rows=1 width=12)"
"                                                              ->  Nested Loop  (cost=0.00..16.74 rows=1 width=12)"
"                                                                    ->  Index Scan using kpp on xmldoc a0008  (cost=0.00..8.35 rows=1 width=4)"
"                                                                          Index Cond: (kind = 6)"
"                                                                          Filter: ((value)::text = 'auction.xml'::text)"
"                                                                    ->  Index Scan using knuparp on xmldoc a0009  (cost=0.00..8.38 rows=1 width=8)"
"                                                                          Index Cond: ((a0009.kind = 1) AND ((a0009.name)::text = 'site'::text) AND ((a0009.uri)::text = ''::text) AND (a0009.parent = a0008.pre))"
"                                                              ->  Index Scan using knuparp on xmldoc a0010  (cost=0.00..7.98 rows=1 width=8)"
"                                                                    Index Cond: ((a0010.kind = 1) AND ((a0010.name)::text = 'people'::text) AND ((a0010.uri)::text = ''::text) AND (a0010.parent = a0009.pre))"
"                                                        ->  Index Scan using knuparp on xmldoc a0011  (cost=0.00..7.98 rows=1 width=8)"
"                                                              Index Cond: ((a0011.kind = 1) AND ((a0011.name)::text = 'person'::text) AND ((a0011.uri)::text = ''::text) AND (a0011.parent = a0010.pre))"
"                                                  ->  Index Scan using knuparp on xmldoc a0012  (cost=0.00..7.15 rows=1 width=18)"
"                                                        Index Cond: ((a0012.kind = 2) AND ((a0012.name)::text = 'id'::text) AND ((a0012.uri)::text = ''::text) AND (a0012.parent = a0011.pre))"
"                                            ->  Index Scan using knuvpp on xmldoc a0007  (cost=0.00..8.41 rows=1 width=18)"
"                                                  Index Cond: ((a0007.kind = 2) AND ((a0007.name)::text = 'person'::text) AND ((a0007.uri)::text = ''::text) AND ((a0007.value)::text = (a0012.value)::text))"
"                                      ->  Index Scan using xmldoc_pkey on xmldoc a0006  (cost=0.00..7.88 rows=1 width=8)"
"                                            Index Cond: (a0006.pre = a0007.parent)"
"                                            Filter: (((a0006.uri)::text = ''::text) AND (a0006.kind = 1) AND ((a0006.name)::text = 'buyer'::text))"
"                                ->  Index Scan using knuparp on xmldoc a0003  (cost=0.00..8.38 rows=1 width=8)"
"                                      Index Cond: ((a0003.kind = 1) AND ((a0003.name)::text = 'site'::text) AND ((a0003.uri)::text = ''::text) AND (a0003.parent = a0008.pre))"
"                          ->  Index Scan using xmldoc_pkey on xmldoc a0005  (cost=0.00..7.75 rows=1 width=12)"
"                                Index Cond: (a0005.pre = a0006.parent)"
"                                Filter: (((a0005.uri)::text = ''::text) AND (a0005.kind = 1) AND ((a0005.name)::text = 'closed_auction'::text))"
"                    ->  Index Scan using xmldoc_pkey on xmldoc a0004  (cost=0.00..5.98 rows=1 width=8)"
"                          Index Cond: (a0004.pre = a0005.parent)"
"                          Filter: (((a0004.uri)::text = ''::text) AND (a0004.kind = 1) AND ((a0004.name)::text = 'closed_auctions'::text))"
"              ->  Index Scan using xmldoc_pkey on xmldoc a0013  (cost=0.00..3130.37 rows=56696 width=32)"
"                    Index Cond: ((a0013.pre >= a0005.pre) AND (a0013.pre <= (a0005.pre + a0005.size)))"

Das Erstaunliche ist wie gesagt die Verwendung der PKey`s.

Ohne daß ich jetzt verstehe, was da abgeht, aber: was genau hast Du gegen die Verwendung der PK’s?

Im übrigen ist das noch immer kein Explain Analyse.

Andreas

Was willst du eigentlich? Der Primary Key ist auch nur ein Index wie jeder andere und bei dir wird genau dieser verwendet.

Disk Reads kann dir PG nicht mitteilen, da die Datenbank selbst nicht weiß, ob die Daten aus den Dateisystem Cache des Betriebssystems kommen oder von der Platte gelesen werden. Allerdings steht in den Statistik Tabellen sehr wohl drin wie oft welche Tabelle und welcher Index angesprochen wird, außerdem kann das Betriebssystem über die eigenen Statistiken mitteilen, wie oft effektiv die Platte angesprochen wurde.


Das ganze System kann man sicherlich optimieren - aber dafür braucht es wesentlich tiefere Einsichten und mehr Zeit. Nur die kleine Teilmenge an Informationen reicht - wie akretschmer schon gesagt hat - keineswegs aus.



Wie schaut es denn mit ganz grundlegenden Optimierungen aus, die man überall anwenden kann? Ist da schon was passiert?

Offensichtlich besteht hier meinerseits ein kleines Begriffsproblem, explain analyze oder explain verbose schreibe ich vor meine Quiery um die Pläne des Planers zu bekommen… wie verfahre ich dann mit

Explain Analyse

wenn ich das vor die quiery schreibe bekomme das obige Ergebnis.


Ok möglicherweise kam nicht ganz durch worauf ich hinaus will, es geht mir darum an welchen Stellen die Datenbank Daten aus einem Index oder aus den Rohen Daten holt. der Index liegt(hoffentlich) auf regional nahen Pages auf der Platte und so sortiert(nach reihenfolge der Attribute) das man nur diese oder eine Teilmenge davon anfassen muss, was die Zugriffsmenge gering hält. Wenn man also einen Join mit mehreren constraints über multi attribute Indices beantworten würde, würde man die Anzahl der Lesezugriffe möglichst gering halten. Wenn man den Join mit nur Pkey`s ausführt müssen mehr Daten zur Verifizierung aus den Rohen Daten geholt werden -> deutlich mehr pages müssten angefasst werden
Soweit zur Theorie. Aus den Rückgaben des Planers kann ich leider nicht sehen was Index-only Operationen sind oder wo auf die Rohdaten herunter gegangen werden muss.
Es geht mir nicht um statistische Werte, es kann je nach “wünschen” des Planers am encoding der Quieries gedreht werden um ihm das planen einfacher zu machen…

Ich befürchte jedoch das ich mich um das zu klären mit dem source code des optimizers beschäftigen müsste.


Wie schaut es denn mit ganz grundlegenden Optimierungen aus, die man überall anwenden kann? Ist da schon was passiert?

Was läuft alles darunter? die statistics sind auf 1000 gesetzt, vacuumiert und analysiert ist auch, geclustert und defragmentiert ist ebenfalls

EXPLAIN zeigt dir den Plan an, EXPLAIN ANALYZE (oder ANALYSE, funktioniert beides für die Amerikaner und Briten) füht die Anfrage zusätzlich aus und misst die Laufzeiten.




Ok möglicherweise kam nicht ganz durch worauf ich hinaus will, es geht mir darum an welchen Stellen die Datenbank Daten aus einem Index oder aus den Rohen Daten holt. der Index liegt(hoffentlich) auf regional nahen Pages auf der Platte und so sortiert(nach reihenfolge der Attribute) das man nur diese oder eine Teilmenge davon anfassen muss, was die Zugriffsmenge gering hält.

Der Index liegt hoffentlich nicht nahe den Daten sondern befindet sich im besten Fall auf einer anderen Festplatte.

Außerdem stehen im Index ggf. mehrere Werte für einen sichtbaren Wert, PostgreSQL muss also (bisher, Index only Scans kommen irgendwann in der Zukunft) den Datenblock sowieso lesen und prüfen, welcher Datensatz nun genau für die Transaktion sichtbar ist. Diese Frage kann nicht aus dem Index beantwortet werden.



Soweit zur Theorie. Aus den Rückgaben des Planers kann ich leider nicht sehen was Index-only Operationen sind oder wo auf die Rohdaten herunter gegangen werden muss.

Es gibt in PG keine Index-Only Scans, jedenfalls bisher nicht.
Ein paar Schritte in diese Richtung sind bereits implementiert. Mal sehen, wie all die Probleme im Zusammenhang mit MVCC gelöst werden.


Was läuft alles darunter? die statistics sind auf 1000 gesetzt, vacuumiert und analysiert ist auch, geclustert und defragmentiert ist ebenfalls

Es gibt wesentlich profanere - und erfolgreiche - Schritte, um eine Datenbank richtig schnell zu bekommen.

Mit “Statistics auf 1000” hast du dir womöglich deine pg_stats Tabelle sehr schön aufgebläht. Ob das auch wirklich etwas bringt - wage ich mal zu bezweifeln.

Explain schätzt, analyse führt aus. Nur explain zeigt auch nur die Schätzwerte, während mit analyse die realen Zahlen angezeigt werden. Du hast nur explain, ohne analyse, gemacht, erkennbar daran, daß nur die Schätzwerte sichtbar sind.


Ok möglicherweise kam nicht ganz durch worauf ich hinaus will, es geht mir darum an welchen Stellen die Datenbank Daten aus einem Index oder aus den Rohen Daten holt. der Index liegt(hoffentlich) auf regional nahen Pages auf der Platte und so sortiert(nach reihenfolge der Attribute)

Siehe ads. Die Indexe haben keine Information darübe, welche Tupel in welcher Transaktion sichtbar sind, daher ist immer auch die Tabelle zu befragen.

Was läuft alles darunter? die statistics sind auf 1000 gesetzt, vacuumiert und analysiert ist auch, geclustert und defragmentiert ist ebenfalls

Hast Du auch die Config angepaßt?


Andreas

Außerdem stehen im Index ggf. mehrere Werte für einen sichtbaren Wert, PostgreSQL muss also (bisher, Index only Scans kommen irgendwann in der Zukunft) den Datenblock sowieso lesen und prüfen, welcher Datensatz nun genau für die Transaktion sichtbar ist. Diese Frage kann nicht aus dem Index beantwortet werden.

Es gibt in PG keine Index-Only Scans, jedenfalls bisher nicht.
Ein paar Schritte in diese Richtung sind bereits implementiert. Mal sehen, wie all die Probleme im Zusammenhang mit MVCC gelöst werden.

Schade, somit wäre Frage 1 wohl erschlagen.

Mit “Statistics auf 1000” hast du dir womöglich deine pg_stats Tabelle sehr schön aufgebläht. Ob das auch wirklich etwas bringt - wage ich mal zu bezweifeln

Bringt im Schnitt ca 10%, bei einigen quieries jedoch auch sehr enorme Gewinne.

Hast Du auch die Config angepaßt?

Teilweise, Änderungen bei werten wie cpu-Kosten fand ich zu bedenklich.

Es gibt wesentlich profanere - und erfolgreiche - Schritte, um eine Datenbank richtig schnell zu bekommen.

Zum Beispiel? Ich bin mir gerade nicht sicher auf wie elementare Schritte du abzielst.

Bliebe noch die Frage was bei einem Bitmap Index genau geschieht und warum dieser ab und an einem scan über einem einzelnen Index vorgezogen wird.
Ist der bitmap index scan/table scan performanter wie der Zugriff über einen “normalen” Index? Warum wird er dann nicht immer verwendet? Eine Abhängigkeit von den Kardinalitäten konte ich bishernicht festtellen.

PostgreSQL erstellt für jede Anfrage einen neuen Plan - basierend auf den aktuellen Daten. Die gleiche Anfrage kann also 2 Minuten später in einen ganz anderen Plan resultieren.

Auf welche Änderungen und Optimierungen ich sonst abziele? Konfiguration anpassen, Speicherwerte anpassen, Tabellen ev. partitionieren, mehrere Festplatten einsetzen, BG Writer anpassen, Checkpoints anpassen, Indexes überprüfen, … die Liste lässt sich noch eine Weile fortsetzen.

Meine Daten sind wie schon gesagt absolut statisch, sie ändern sich also nie, also solten(und sind bisher) die Pläne reproduzierbar sein.

Ich habe also nur lesende Zugriffe und bewege mich auf einem knapp 20mb Datensatz da ich hier dank Komplexität der Anfragen schon im Minutenbereich bin.
Einige der Optimierungen fallen dadurch weg. Die Indices sind der spannende Punkt, aber da index only Operationen nicht gehen wird einiges klarer in der Hinsicht.

Let me google that for you

Andreas



Lange Verkettungen von AND und OR stellen viele Datenbanken immer wieder vor gr��ere Probleme. Zum Einen macht das Absch�tzen der Ergebnismenge in solchen Konstrukten Probleme - zum Anderen ist das Finden der korrekten Indizierungsmethode nicht ganz trivial. In vielen Datenbanken ist es weiters nicht m�glich, zum Abfragen einer Tabelle mehr als nur einen Index zu verwenden.
Seit PostgreSQL 8.1 gibt es sogenannte Bitmap Indices, die es erm�glichen, Indices miteinander zu verbinden. In der Praxis sieht das wie folgt aus:

die restlichen Links beinahlten exakt die gleiche Information… das erklärt leider in keinster Weise warum man das mit einem einzigen index verwenden solte… deswegen Frage ich das ja hier… Ein dynamisch erzeugter bitmap Index über einen einzelnen B+Index(und nur genau dessen spalten) macht wenig Sinn wenn da nicht noch etwas anderes passiert.

Selbst wenn die Datenbank hier alles sequentiell lesen müsste, würden sich die Ergebnisse bei insgesamt 20 MB Daten nicht im Minuten- sondern eher im Sekundenbereich bewegen.

Noch mal: wie schaut der Rest deiner Konfiguration aus? Du hast dich so sehr auf die Indexsachen konzentriert das ich fürchte, für den Rest der Datenbankkonfiguration ist nichts übrig geblieben.


Kannst du das auch verständlich formulieren?


Alles in allem verweise ich mal auf das Ende meiner Signatur, vielleicht wird dann einiges klarer.

also nochmal … tu uns/Dir den gefallen und führe mal ein

EXPLAIN ANALYSE ;

aus …

und poste hier das ergebnis … dann wird uns der plan nämlich auch sagen wo er wieviele milliarden sekunden vertroedelt - oder wo man nicht viel optimieren muss

gruss

also nochmal … tu uns/Dir den Gefallen und führe mal ein

EXPLAIN ANALYSE ;

aus …

und poste hier das Ergebnis … dann wird uns der plan nämlich auch sagen wo er wieviele milliarden Sekunden vertroedelt - oder wo man nicht viel optimieren muss

Was mir sofort auffällt:

Du benutzt mehrfach Conditions der Form:
a0003.parent = a0008.pre
AND a0003.name = ‘site’
AND a0003.uri = ‘’
AND a0003.kind = 1

Dein analyse zeigt mir aber, dass er bei manchen Scans nur die Primary-Key Indizes verwendet … da hast keine kombinierten Indizes über die relevanten Felder - die (meines Erachtens) für viele andere Abfragen auch Sinnvoll wären.

D.h. deine Granularität ist mies. Du brauchst einen kombinierten Index wenigstens über parent + name + kind. URI wird wahrscheinlich nicht sehr sinnvoll sein.

Du wirst sehen - dann klappt die query in null komma nix.

Gruss

Genau das ist das mir unverständliche, die Indices sind da.
Ich hab mal Spaßeshalber alle permutationen der Indices angelegt, was so in gut 200 Stück ausartet aber leider dazu führt das sich PG etwas in den Indices vergreift und deutlich langsamer wird.
Mit einem reduzierten Set von Indices (so ca 25) gehts besser, bis eben auf diese Ausreisser mit den PKeys.


Ok nochmal, wie habe ich den bitmap index verstanden: Es wird zur Laufzeit der Quiery eine Bitmap über mehrere indices(attribute) angelegt um Selektionen über mehrere Constraints schneller abarbeiten zu können. An sich eine super Idee, ABER warum solte man das über einem einzelnen B+baum Index machen(und eben genau nur über diesen Index, wenn ja noch nen anderes Attribut dazu genommen werden würde…)? Der Zugriff auf einzelne Tupel eines B+Index ist nicht so viel langsamer das es die Erstellung der bitmap rechtferitgen würde(v.a. da dazu ja auch der ganze Baum gelesen werden muss)




explain analyse kommt Morgen da ich hier kein stabieles Netz hab und sie über 10 mins läuft…