Databanken 2 - ISB W5

Wat betekent “performantie” in een database?

Het is hoe snel een database werkt. Bijvoorbeeld: een database die 1000 vragen per seconde beantwoordt heeft een hoge performantie.

Waarom is I/O traag?

Lezen van schijf is traag. RAM is sneller. Bijvoorbeeld: data uit geheugen = 1 seconde, van schijf = 100 seconden.

Wat probeert men te vermijden voor goede performantie?

Men probeert I/O te vermijden. Bijvoorbeeld: data liever in RAM houden dan steeds van harde schijf lezen.

Wat doet het bestand postgresql.conf?

Het is het configuratiebestand van PostgreSQL. Bijvoorbeeld: je zet erin hoeveel geheugen de database mag gebruiken.

Wat doet de parameter shared_buffers?

Ze bepaalt hoeveel geheugen voor cache is. Bijvoorbeeld: als je 25% van RAM gebruikt, werkt de database sneller.

Wat doet work_mem?

Dat is geheugen voor sorteren. Bijvoorbeeld: als je grote lijsten sorteert, gebruik je meer work_mem.

Wat doet synchronous_commit?

Ze bepaalt of de database wacht tot data op schijf staat. Bijvoorbeeld: snel maar minder veilig = uit; trager maar veilig = aan.

Wie is verantwoordelijk voor performantie?

De DBA (beheerder) en de ontwikkelaar. Bijvoorbeeld: DBA stelt geheugen in, ontwikkelaar schrijft goede queries.

Wat kan een DBA doen voor betere performantie?

De DBA verdeelt data over schijven. Bijvoorbeeld: logbestanden op snelle schijf, data op andere.

Wat kan een ontwikkelaar doen voor betere performantie?

Goede SQL schrijven. Bijvoorbeeld: SELECT naam FROM klanten WHERE stad='Gent'; in plaats van alles op te halen.

Wat doet de Parser?

Ze kijkt of de SQL juist is. Bijvoorbeeld: ze ziet dat SELEC * FROM klant fout is, want “SELECT” is verkeerd geschreven.

Wat doet de Analyzer?

Ze controleert de betekenis. Bijvoorbeeld: kijkt of de kolom stad echt bestaat.

Wat doet de Rewriter?

Ze herschrijft een query. Bijvoorbeeld: SELECT * FROM view_klanten verandert naar de echte tabellen onder die view.

Wat doet de Planner (Optimizer)?

Ze zoekt de snelste manier. Bijvoorbeeld: kiest tussen index gebruiken of hele tabel lezen.

Wat doet de Executor?

Ze voert het plan uit. Bijvoorbeeld: ze haalt de juiste rijen op volgens het gekozen plan.

Wat is een index?

Een lijst om snel iets te vinden. Bijvoorbeeld: in een boek zoek je met de index “Hoofdstuk 3 - pagina 45”.

Wanneer gebruikt de database een index?

Als de kolom in de WHERE-clausule staat. Bijvoorbeeld: WHERE naam='Piet'.

Wat is het voordeel van een index?

Het zoeken is sneller. Bijvoorbeeld: van 1 minuut naar 1 seconde.

Wat is het nadeel van een index?

Schrijven wordt trager. Bijvoorbeeld: bij elke nieuwe rij moet de index ook worden aangepast.

Wat is een B-tree index?

Een boomstructuur om snel te zoeken. Bijvoorbeeld: namen staan gesorteerd A–Z, dus “Maria” vind je snel.

Wat is een Hash index?

Maakt een code voor elke waarde. Bijvoorbeeld: “Jan” → 23214, “Piet” → 99870.

Wat doet een GIN-index?

Zoekt in kolommen met meerdere waarden. Bijvoorbeeld: kolom met lijst van tags (['sport', 'muziek']).

Wat is een samengestelde index?

Een index op meer dan één kolom. Bijvoorbeeld: (stad, postcode).

Wanneer gebruik je een samengestelde index?

Als je vaak samen zoekt. Bijvoorbeeld: WHERE stad='Gent' AND postcode=9000.

Wanneer is een index niet nuttig?

Bij kleine tabellen. Bijvoorbeeld: als er maar 10 rijen zijn, is index zoeken trager dan alles lezen.

Wat doet REINDEX?

Maakt een index opnieuw. Bijvoorbeeld: als de oude index beschadigd is of traag werkt.

Wat is een index op een expressie?

Index op een berekening. Bijvoorbeeld: UPPER(naam) voor hoofdletters zoeken.

Wat is een geclusterde index?

De rijen in de tabel staan fysiek in indexvolgorde. Bijvoorbeeld: klanten staan echt op schijf gesorteerd per stad.

Wat doet de opdracht EXPLAIN?

Toont hoe PostgreSQL een query zal uitvoeren. Bijvoorbeeld: laat zien of een index wordt gebruikt.

Wat is het verschil tussen EXPLAIN en EXPLAIN ANALYZE?

EXPLAIN toont plan zonder uitvoeren; ANALYZE voert echt uit en toont tijden.

Wat is een sequentiële scan?

De database leest alles rij per rij. Bijvoorbeeld: 1.000.000 rijen controleren.

Wat is een index scan?

De database zoekt via de index. Bijvoorbeeld: enkel rijen met naam='Eva'.

Wat is een index-only scan?

Leest enkel de index, niet de tabel. Bijvoorbeeld: query vraagt enkel kolommen die in de index staan.

Wat is een bitmap scan?

De database combineert meerdere indexen. Bijvoorbeeld: zoekt “stad = Gent” én “leeftijd > 30”.

Wat is een nested loop join?

Elke rij uit tabel A wordt vergeleken met elke rij uit tabel B. Bijvoorbeeld: 10 rijen × 100 rijen = 1000 vergelijkingen.

Wat is een hash join?

Maakt een hashtabel voor snellere join. Bijvoorbeeld: emp.deptNo = dept.deptNo.

Wat is een merge join?

Voegt twee gesorteerde lijsten samen. Bijvoorbeeld: sorteert eerst beide tabellen op deptNo.

Wat doet de optimizer?

Ze kiest het goedkoopste en snelste uitvoeringsplan. Bijvoorbeeld: kiest tussen hash join of merge join.

Wat toont EXPLAIN (ANALYZE, BUFFERS)?

Het toont hoeveel blokken van schijf of geheugen elk deel van de query gebruikt.

Wat doet de Dalibo EXPLAIN-tool?

Ze maakt een visuele boom van het plan. Bijvoorbeeld: kleuren tonen welke stap traag is.

Hoe kun je de optimizer beïnvloeden?

Je kunt functies aan/uit zetten. Bijvoorbeeld: SET enable_indexscan = off; dwingt een sequentiële scan af.

Wat doet pgstatstatements?

Het houdt statistieken bij over uitgevoerde queries. Bijvoorbeeld: welke query’s het langst duren.

Wat is de taak van de Optimizer?

Ze kiest het snelste plan om een SQL-query uit te voeren. Bijvoorbeeld: kiest tussen “hele tabel lezen” of “index gebruiken”.

Wat betekent “execution plan”?

Het is een plan dat toont hoe de database de query uitvoert. Bijvoorbeeld: met welke volgorde en welke index.

Wat doet de opdracht EXPLAIN?

Ze toont het uitvoeringsplan van een query. Bijvoorbeeld: laat zien of de database een index gebruikt.

Wat doet EXPLAIN ANALYZE?

Ze voert de query echt uit en toont de echte tijden. Bijvoorbeeld: hoeveel milliseconden elke stap duurde.

Wat doet de optie BUFFERS bij EXPLAIN?

Ze toont hoeveel blokken uit geheugen of schijf zijn gelezen. Bijvoorbeeld: “read 200 blocks”.

Wat is een “plan tree”?

Het is een boom van stappen die PostgreSQL volgt om een query uit te voeren. Bijvoorbeeld: scan → join → sort.

Wat is een knooppunt (node) in een plan?

Het is één stap van het plan. Bijvoorbeeld: “Seq Scan” of “Hash Join”.

Wat is een sequentiële scan (Seq Scan)?

De database leest alle rijen één voor één. Bijvoorbeeld: controleert 1 miljoen rijen om 3 resultaten te vinden.

Wanneer gebruikt PostgreSQL een sequentiële scan?

Als er geen index is of bijna alle rijen worden gelezen. Bijvoorbeeld: SELECT * FROM klanten.

Wat is een index scan?

De database zoekt via een index. Bijvoorbeeld: SELECT * FROM klanten WHERE naam='Piet'.

Wat is een index-only scan?

De database gebruikt alleen de index, niet de tabel. Bijvoorbeeld: als alle gevraagde kolommen in de index staan.

Wat is een bitmap index scan?

De database haalt eerst alle index-resultaten op, en leest daarna enkel de juiste pagina’s. Bijvoorbeeld: combineert “stad=Gent” en “leeftijd>30”.

Wat is een TID scan?

De database zoekt direct op ID van een rij (tuple ID). Bijvoorbeeld: WHERE ctid='(1,5)'.

Wat zijn aggregatiefuncties in SQL?

COUNT, SUM, AVG, MIN, MAX. Bijvoorbeeld: SELECT COUNT(*) FROM klanten.

Welke twee manieren gebruikt PostgreSQL om aggregatie te doen?

Met sorteren of met hashing.

Wat doet “sort-based aggregatie”?

De database sorteert eerst de data. Bijvoorbeeld: sorteert per stad en telt daarna klanten per stad.

Wat doet “hash-based aggregatie”?

De database maakt een hashtabel met groepen. Bijvoorbeeld: groep “Gent” → 200 klanten.

Wat is een join in SQL?

Een manier om data van twee tabellen samen te brengen. Bijvoorbeeld: SELECT * FROM klanten JOIN bestellingen.

Wat doet een Nested Loop Join?

Elke rij uit de eerste tabel wordt vergeleken met elke rij uit de tweede tabel. Bijvoorbeeld: 10 × 100 = 1000 vergelijkingen.

Wanneer is een Nested Loop Join nuttig?

Als de eerste tabel klein is. Bijvoorbeeld: 5 klanten vergelijken met 10.000 bestellingen.

Wat doet een Hash Join?

De database maakt een hashtabel van één tabel om sneller overeenkomsten te vinden. Bijvoorbeeld: emp JOIN dept ON deptNo.

Wanneer gebruikt PostgreSQL een Hash Join?

Als de join-voorwaarde “=” is en er genoeg geheugen is. Bijvoorbeeld: WHERE a.id = b.id.

Wat doet een Merge Join?

De database sorteert beide tabellen en voegt ze samen. Bijvoorbeeld: twee lijsten met deptNo 1,2,3 worden samengevoegd.

Wanneer gebruikt PostgreSQL een Merge Join?

Als beide tabellen al gesorteerd zijn. Bijvoorbeeld: bij grote tabellen met indexen.

Wat is het voordeel van een Hash Join?

Het is snel bij grote tabellen. Bijvoorbeeld: miljoenen rijen met een eenvoudige join.

Wat is het voordeel van een Merge Join?

Het werkt goed als data al gesorteerd is. Bijvoorbeeld: met een index op de join-kolom.

Wat is het nadeel van een Nested Loop Join?

Het wordt traag als beide tabellen groot zijn. Bijvoorbeeld: 1 miljoen × 1 miljoen = heel traag.

Wat is een slechte “cost estimate” in EXPLAIN?

Als PostgreSQL denkt dat er weinig rijen zijn, maar het zijn er veel. Bijvoorbeeld: verwacht 10, maar krijgt 10.000.

Hoe herken je een probleem in EXPLAIN ANALYZE?

Als “rows” (werkelijk) veel hoger is dan “rows” (geschat). Bijvoorbeeld: 100x verschil.

Waarom is dat een probleem?

De optimizer kiest dan een verkeerd plan, wat de query traag maakt.

Wat kun je doen bij een trage scan met veel filtering?

Een index aanmaken op de kolom van de WHERE-clausule. Bijvoorbeeld: CREATE INDEX ON klanten(stad).

Wat is de Dalibo EXPLAIN-tool?

Een website die het uitvoeringsplan visueel toont. Bijvoorbeeld: gekleurde blokken met tijden.

Wat toont de Dalibo-tool nog meer?

Waar de query traag is of te veel blokken leest. Bijvoorbeeld: rode balk = veel tijd.

Kun je de optimizer beïnvloeden?

Ja, door functies aan of uit te zetten. Bijvoorbeeld: SET enable_indexscan = off;.

Wat betekent enable_seqscan?

Het laat of verbiedt sequentiële scans. Bijvoorbeeld: SET enable_seqscan = off; dwingt indexgebruik af.

Wat doet de parameter randompagecost?

Ze zegt hoe duur een willekeurige schijflezing is. Bijvoorbeeld: lagere waarde als data in RAM zit.

Wat doet cputuplecost?

Ze zegt hoe duur het is om één rij te verwerken. Bijvoorbeeld: 0.01 betekent snel CPU-werk.

Wat doet GEQO (Genetic Query Optimizer)?

Ze zoekt met een “genetisch algoritme” een goed plan bij complexe joins. Bijvoorbeeld: bij 10 of meer tabellen.

Wat doet de extensie pgstatstatements?

Ze toont welke queries het traagst zijn. Bijvoorbeeld: lijst van top 25 langzame queries.

Hoe activeer je pgstatstatements?

In het bestand postgresql.conf voeg je toe: sharedpreloadlibraries = 'pgstatstatements', daarna restart.