Geschichte: PostgreSQL verwendet einen kostenbasierten Optimierer, der abstrakte monetäre Einheiten I/O-Operationen zuweist. Frühere Datenbanksysteme richteten sich hauptsächlich an drehende Festplatten, bei denen Suchkosten zufällige I/O ungefähr 40 Mal teurer machten als sequenzielle Lesevorgänge. Um diese Asymmetrie zu mildern, wurden Bitmap Index Scans eingeführt, um zufällige Seitenabrufe zu amortisieren, indem ein in-memory Bitmap der übereinstimmenden Tupelstandorte erstellt und der Heap in annähernd physikalischer Reihenfolge abgerufen wird.
Problem: Das Kernproblem tritt auf, wenn moderate Selektivitätsprädikate gefiltert werden, die Tausende von Zeilen abgleichen, die über viele Datenblätter verstreut sind. Ein Index Scan führt eine zufällige I/O für jeden passenden Tupelzeiger aus, was zu mechanischem Festplattendurchsatz oder übermäßigen I/O-Anfragen auf SSDs führen kann. Im Gegensatz dazu verursacht ein Bitmap Index Scan Overhead, um die Bitmap-Struktur aufzubauen, und kann irrelevante Zeilen verarbeiten, wenn die Bitmap aufgrund von work_mem-Einschränkungen verlustbehaftet wird.
Lösung: Der Entscheidungsschwellenwert liegt innerhalb der Funktionen cost_index() und cost_bitmap_heap_scan(). Der Planer schätzt die Anzahl der erforderlichen unterschiedlichen Heap-Seiten (pages_fetched), um die Abfrage zu erfüllen. Wenn pages_fetched das Verhältnis random_page_cost / seq_page_cost überschreitet, bevorzugt der Optimierer den Bitmap-Ansatz, da die Kosten der sortierten Seitenabfrage die zufälligen Zugriffsgebühren überwiegen. Eine Reduzierung von random_page_cost (z.B. von 4.0 auf 1.1 für SSD-Speicher) senkt die wahrgenommene Strafe für zufällige I/O und drängt den Planer zurück zu regulären Index Scans für Selektivitäten, die zuvor die Erstellung von Bitmaps ausgelöst hatten.
Eine Finanzberichterstattungsplattform litt unter erheblicher Latenz bei einer Dashboard-Abfrage, die transactions nach account_id für das aktuelle Geschäftsjahr aggregierte. Die Tabelle enthielt 500 Millionen Zeilen auf einem veralteten SAN mit drehenden Festplatten. Das Prädikat account_id = 12345 stimmte mit ungefähr 12% der Zeilen überein, die zufällig über den Heap verstreut waren. Der Ausführungsplan zeigte einen Standard-Index Scan, der aufgrund zufälliger I/O-Stürme über Tausende von Blattseiten 14 Sekunden in Anspruch nahm.
Die Erhöhung von random_page_cost von 4.0 auf 8.0 signalisierte dem Optimierer ausdrücklich, dass zufällige Festplattensuchen prohibitv teuer waren. Diese unmittelbare Änderung zwang den Planer, einen Bitmap Index Scan auszuwählen, was die Ausführungszeit auf 1,8 Sekunden reduzierte, indem Seitenanforderungen in sortierte Bereiche gruppiert wurden. Allerdings bestrafte diese globale Einstellung OLTP-Punktabfrageanfragen anderswo in der Anwendung, was dazu führte, dass sie zu weniger effizienten sequenziellen Scans wechselten, die die Lock-Konkurrenz während der Spitzenhandelszeiten erhöhten.
Die Erstellung eines abdeckenden Indexes auf (account_id, transaction_date, amount) ermöglichte einen Index Only Scan, der den Heap vollständig umging und Antwortzeiten von 80 ms ergab. Während dies für Lesevorgänge optimal war, blähte der zusammengesetzte Index die Tabellengröße um 35% auf und verringerte den Durchsatz bei der Eingabe um 22%, da jeder Insert nun erforderte, zwei große B-Baum-Strukturen zu warten, was die strenge SLA für die Echtzeit-Aufzeichnung von Trades verletzte.
Wir haben uns entschieden, die Tabellenpartitionierung nach Bereich auf created_at kombiniert mit einem moderaten random_page_cost von 6.0 umzusetzen. Dieser hybride Ansatz beschränkte die Abfrage auf die Partition des aktuellen Quartals und reduzierte die absolute Seitenanzahl unterhalb der Bitmap-Schwelle, während der erhöhte Kostenparameter sicherstellte, dass Abfragen über Partitionen hinweg immer noch Bitmaps verwendeten, um eine zufällige I/O-Sättigung zu verhindern. Diese Lösung respektierte die Anforderungen an die Schreibleistung des Handelssystems, während sie den leseintensiven Berichtspfad optimierte.
Ergebnis: Die Dashboard-Abfrage stabilisierte sich bei 400 ms, ohne die OLTP-Einfügeleistung zu beeinträchtigen, und die Festplattenspeicherauslastung auf dem Reporting-Knoten sank während der Geschäftszeiten von 95% auf 30%.
Wie interagiert effective_cache_size mit random_page_cost im Kostenmodell des Planers und warum könnte eine Senkung von random_page_cost in einem System mit großem Cache tatsächlich die Leistung für bestimmte Join-Typen verschlechtern?
effective_cache_size quantifiziert den verfügbaren Speicher für die Festplattencache. Wenn es hoch eingestellt ist, nimmt der Planer an, dass viele Seiten im RAM vorhanden sind, wodurch die I/O-Kosten unabhängig von der Einstellung von random_page_cost effektiv abgezogen werden. Wenn Sie random_page_cost aggressiv auf 1.1 (typisch für NVMe SSDs) senken, während effective_cache_size hoch bleibt, könnte der Optimierer irrational Nested Loop-Joins mit Index Scans über Hash Joins bevorzugen. Das Modell nimmt an, dass die Indexabfragen der inneren Beziehung nahezu kostenlos sind, da zufällige I/O billig und zwischengespeichert ist, und ignoriert, dass massive innere Schleifen dennoch die CPU mit Tupelverarbeitung überlasten und Cache-Entleerung auslösen, was zu einer schlechteren Zeit als eine einzige Bulk-Hash-Operation führt, die die innere Tabelle einmal scannt.
Inwiefern unterscheidet sich der Bitmap Index Scan von einem Bitmap Heap Scan, und warum wählt der Planer BitmapOr Operationen über mehrere Indizes hinweg, anstatt einen einzigen zusammengesetzten Index zu verwenden?
Ein Bitmap Index Scan durchläuft die Indexstruktur, um ein Bitmap von übereinstimmenden Tupelzeigern (oder Seitenbereichen, falls verlustbehaftet) zu erstellen. Ein Bitmap Heap Scan ruft anschließend die tatsächlichen Zeilendaten aus der Tabelle mithilfe dieses Bitmaps ab, um Seiten sequenziell zuzugreifen. BitmapOr (oder BitmapAnd) tritt auf, wenn eine Abfrage auf Bedingungen wie WHERE status = 'active' OR priority = 'high' filtert, die separate Indizes abgleichen. Da PostgreSQL nicht gleichzeitig zwei B-Bäume effizient in einem einzigen Durchgang durchlaufen kann, erstellt es Bitmaps von jedem Index unabhängig und kombiniert sie mit bitweise Operationen. Diese Technik wird bevorzugt gegenüber einem zusammengesetzten Index (status, priority), wenn Abfragen nur auf status, nur auf priority oder variabel auf beide filtern, da die Pflege zweier separater Indizes geringere Schreibverstärkung verursacht als mehrere abdeckende zusammengesetzte Varianten.
Wann könnte eine Abfrage eine LIMIT Klausel verwenden, und warum könnte PostgreSQL trotz der frühen Beendigung, die einen Standard- Index Scan bevorzugt, dennoch einen Bitmap Index Scan wählen, und wie beeinflussen veraltete Statistiken diese Fehlkalkulation?
Ein standardmäßiger Index Scan kann sofort beenden, nachdem er LIMIT N Zeilen abgerufen hat, wenn der Index die erforderliche Sortierung unterstützt, um I/O zu minimieren. Wenn der Planer jedoch die Anzahl der Zeilen, die das Prädikat erfüllen, unterschätzt—aufgrund von veralteten ANALYZE-Statistiken oder korrelierten Spalten—geht er davon aus, dass der Index Scan eine übermäßige Anzahl von Blattseiten durchlaufen würde, bevor Übereinstimmungen gefunden werden. Daher wählt er den Bitmap Index Scan, um die I/O-Kosten zu amortisieren. Da Bitmaps vollständig materialisiert werden müssen, bevor auf den Heap zugegriffen wird, kann der Executor nicht frühzeitig anhalten; er erstellt ein Bitmap, das Tausende von Zeilen enthält, nur um alle bis auf die ersten zehn abzulehnen, was im Vergleich zur optimistischen Schätzung des Planers zu katastrophaler Latenz führt.