Geschiedenis: PostgreSQL gebruikt een kosten-gebaseerde optimizer die abstracte monetaire eenheden toekent aan I/O operaties. Vroege databasesystemen richtten zich voornamelijk op draaiende schijven, waar zoekstraffen willekeurige I/O ongeveer 40 keer duurder maakten dan sequentiële reads. Om deze asymmetrie te verminderen, werden Bitmap Index Scans geïntroduceerd om willekeurige paginabestellingen te verlichten door een bitmap in het geheugen te construeren van overeenkomende tuple-locaties en de heap in een ongeveer fysieke volgorde te benaderen.
Probleem: Het kernprobleem doet zich voor bij het filteren van gematigd selectieve predikaten die duizenden rijen die verspreid zijn over veel datapagina's begrijpen. Een Index Scan voert één willekeurige I/O uit voor elke bijbehorende tuple-pointer, wat leidt tot mechanische schijfthrashing of buitensporige I/O-aanvragen op SSD's. Omgekeerd brengt een Bitmap Index Scan overhead met zich mee om de bitmapstructuur te bouwen en kan irrelevante rijen verwerken als de bitmap verliesgevend wordt door work_mem beperkingen.
Oplossing: De beslissingsgrens ligt binnen de functies cost_index() en cost_bitmap_heap_scan(). De planner schat het aantal verschillende heap-pagina's (pages_fetched) dat nodig is om de query te voldoen. Wanneer pages_fetched de ratio random_page_cost / seq_page_cost overschrijdt, geeft de optimizer de voorkeur aan de bitmapbenadering omdat de kosten van gesorteerde paginatoegang de willekeurige toegangskosten overstijgen. Het verlagen van random_page_cost (bijv. van 4.0 naar 1.1 voor SSD-opslag) verlaagt de waargenomen straf van willekeurige I/O, waardoor de planner weer terugkeert naar standaard Index Scans voor selectiviteit die eerder de bitmapcreatie activeerde.
Een financieel rapportageplatform had ernstige vertragingen bij een dashboardquery die transacties per account_id voor het huidige fiscale kwartaal aggregeerde. De tabel bevatte 500 miljoen rijen op een legacy SAN met draaiende schijven. De predicate account_id = 12345 kwam overeen met ongeveer 12% van de rijen die willekeurig over de heap verspreid waren. Het uitvoeringsplan toonde een standaard Index Scan die 14 seconden verbruikte vanwege willekeurige I/O-stormen over duizenden bladspecifieke pagina's.
Het verhogen van random_page_cost van 4.0 naar 8.0 gaf de optimizer expliciet aan dat willekeurige schijfzoekingen prohibitief duur waren. Deze directe wijziging dwong de planner om te kiezen voor een Bitmap Index Scan, waardoor de uitvoeringstijd werd teruggebracht tot 1.8 seconden door paginaverzoeken in gesorteerde bereiken te batchen. Deze globale instelling benadeelde echter OLTP-puntzoekqueries elders in de applicatie, waardoor ze overstapten op minder efficiënte sequentiële scans die de lock-concurrentie tijdens drukke handelsuren verhoogden.
Het maken van een dekkende index op (account_id, transaction_date, amount) maakte een Index Only Scan mogelijk die de heap volledig omzeilde en 80 ms-responstijden opleverde. Hoewel dit optimaal was voor reads, bloatte de samengestelde index de tafelgrootte met 35% en verminderde de opnamecapaciteit met 22%, omdat elke invoeging nu het onderhouden van twee grote B-tree-structuren vereiste, wat in strijd was met de strikte SLA voor real-time handelsregistratie.
We hebben gekozen voor tabelpartitionering op basis van bereik op created_at in combinatie met een gematigde random_page_cost van 6.0. Deze hybride benadering beperkte de query tot de partitionering van het huidige kwartaal, waardoor het absolute paginatal onder drempel van bitmap bleef, terwijl de verhoogde kostenparameter ervoor zorgde dat cross-partition historische queries nog steeds bitmaps gebruikten om willekeurige I/O-saturatie te voorkomen. Deze oplossing hield rekening met de vereisten voor schrijperfomance van het handelssysteem, terwijl het het leesintensievere rapportagepad optimaliseerde.
Resultaat: De dashboardquery stabiliseerde op 400 ms zonder de OLTP-insertprestaties te verlagen, en de disk I/O-utilisatie op de rapportagenode daalde van 95% naar 30% tijdens kantooruren.
Hoe interageert effective_cache_size met random_page_cost in het kostenmodel van de planner, en waarom kan het verlagen van random_page_cost op een systeem met een grote cache daadwerkelijk de prestaties voor bepaalde join-typen verslechteren?
effective_cache_size kwantificeert het geheugen dat beschikbaar is voor schijf caching. Wanneer het hoog is ingesteld, gaat de planner ervan uit dat veel pagina's in RAM zijn, waardoor I/O-kosten effectief worden afgewaardeerd, ongeacht de instelling van random_page_cost. Als je random_page_cost agressief verlaagt tot 1.1 (typisch voor NVMe SSD's) terwijl je een grote effective_cache_size behoudt, kan de optimizer irrationeel de voorkeur geven aan Nested Loop-joins met behulp van Index Scans boven Hash Joins. Het model gaat ervan uit dat de indexprobes van de binnenste relatie bijna gratis zijn omdat willekeurige I/O goedkoop en gecached is, negerend dat enorme binnenlussen de CPU nog steeds verzadigen met tuple-verwerking en cache-afvoer veroorzaken, wat leidt tot slechtere wall-clock tijd dan een enkele bulk hash operatie die de binnenste tabel eenmaal doorzoekt.
Op welke manier verschilt de Bitmap Index Scan van een Bitmap Heap Scan, en waarom kiest de planner voor BitmapOr bewerkingen over meerdere indexen in plaats van een enkele samengestelde index te gebruiken?
Een Bitmap Index Scan doorloopt de indexstructuur om een bitmap van overeenkomende tuple-pointers (of pagina-reeksen als verliesgevend) te construeren. Een Bitmap Heap Scan haalt vervolgens de werkelijke rijgegevens uit de tabel op met behulp van die bitmap om pagina's sequentieel te benaderen. BitmapOr (of BitmapAnd) doet zich voor wanneer een query filtert op voorwaarden zoals WHERE status = 'active' OR priority = 'high', die afzonderlijke indexen matchen. Aangezien PostgreSQL niet tegelijkertijd twee B-bomen efficiënt kan doorlopen in één doorgang, genereert het bitmaps van elke index onafhankelijk en combineert ze met bitwise-bewerkingen. Deze techniek heeft de voorkeur boven een samengestelde index (status, priority) wanneer queries filteren op status alleen, priority alleen, of beide variabel, omdat het onderhouden van twee afzonderlijke indexen aanzienlijk lagere schrijfactivering met zich meebrengt dan meerdere dekkende samengestelde varianten.
Wanneer een query een LIMIT clausule gebruikt, waarom zou PostgreSQL toch een Bitmap Index Scan kiezen ondanks dat vroegtijdige beëindiging een standaard Index Scan favoriseert, en hoe beïnvloeden verouderde statistieken deze verkeerde berekening?
Een standaard Index Scan kan onmiddellijk beëindigen nadat LIMIT N rijen zijn opgehaald als de index de noodzakelijke ordening ondersteunt, wat I/O minimaliseert. Als de planner echter het aantal rijen dat voldoet aan de predicate onderschat—vanwege verouderde ANALYZE statistieken of gecorreleerde kolommen—gaat hij ervan uit dat de Index Scan een buitensporig aantal bladspecifieke pagina's zou doorlopen voordat overeenkomsten worden gevonden. Daarom selecteert hij Bitmap Index Scan om de I/O-kosten te spreiden. Omdat bitmaps volledig moeten worden geactualiseerd voordat de heap toegankelijk is, kan de executor niet vroegtijdig stoppen; het bouwt een bitmap die duizenden rijen bevat, alleen om alle behalve de eerste tien te verwerpen, met als gevolg catastrofale vertraging in vergelijking met de optimistische schatting van de planner.