Under udviklingen af QuestGame har jeg arbejdet med datamodellering i Entity Framework Core og undersøgt, hvordan PostgreSQL anvender databaseindekser til at optimere forespørgsler.
I den forbindelse blev jeg opmærksom på, at EF Core automatisk opretter en række indeks, særligt på fremmednøgler, mens andre skal konfigureres eksplicit.
Eksempelvis har vores QuestItems-tabel et B-tree-indeks på QuestId, som understøtter opslag efter de QuestItems, der tilhører en bestemt Quest. Vi har desuden et unikt indeks på QuestSessions.JoinCode, som både understøtter opslag og sikrer, at to spilsessioner ikke får samme kode.
Jeg har også undersøgt anvendelsen af GiST-indekser til geografiske forespørgsler gennem PostGIS.
Arbejdet har givet mig en teoretisk forståelse for, hvorfor indeksering kan forbedre databaseperformance. Men det har samtidig rejst et nyt spørgsmål:
Hvordan undersøger jeg, om PostgreSQL faktisk anvender vores indeks, og om de giver en målbar forbedring?
Det førte mig til EXPLAIN ANALYZE, som er et værktøj til databaser. Kort fortalt kører værktøjet en SQL-forespørgsel og viser, hvordan databasen rent faktisk udfører den, og returnerer målinger af tid og ressourcer. Det er altså en måde at undersøge om databasen bruger indeks som forventet, og hvad det betyder for ydeevnen.
Refleksion – et indeks garanterer ikke bedre performance
Min første antagelse var, at et indeks som udgangspunkt ville gøre en forespørgsel hurtigere.
Det viste sig dog at være en forenkling.
PostgreSQL benytter en såkaldt query planner, som vurderer forskellige måder at udføre en forespørgsel på. Beslutningen afhænger blandt andet af tabellens størrelse, databasens statistikker og hvor mange rækker forespørgslen forventes at returnere.
Det betyder, at PostgreSQL ikke nødvendigvis anvender et eksisterende indeks.
Hvis en tabel eksempelvis kun indeholder 20 QuestItems, kan det være mere effektivt at gennemlæse hele tabellen end at slå rækkerne op gennem et B-tree-indeks.
Dette kaldes et Sequential Scan.
Hvis tabellen derimod indeholder flere hundredtusinde QuestItems, hvor kun ganske få tilhører den ønskede Quest, vil et indeks ofte være mere fordelagtigt.
Det interessante er derfor ikke blot, om et indeks eksisterer, men om PostgreSQL vælger at anvende det under de konkrete forhold.
Jeg blev samtidig opmærksom på, at databaseoptimering ikke bør baseres på antagelser alene. Uden konkrete målinger risikerer man at tilføje indeks eller ændre forespørgsler uden at vide, om det faktisk forbedrer systemets ydeevne.
Teoretisk forståelse – EXPLAIN og EXPLAIN ANALYZE
PostgreSQL stiller to nært beslægtede muligheder til rådighed.
EXPLAIN viser den eksekveringsplan, som PostgreSQL forventer at anvende, uden at udføre forespørgslen.
EXPLAIN ANALYZE udfører derimod forespørgslen og sammenholder den planlagte eksekvering med faktiske målinger.
Jeg kan eksempelvis undersøge et opslag i vores QuestItems-tabel med følgende SQL:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM "QuestItems"
WHERE "QuestId" = 42;Code language: SQL (Structured Query Language) (sql)Og jeg får et resultat i stil med dette:
Index Scan using "IX_QuestItems_QuestId" on "QuestItems"
(cost=0.15..8.17 rows=10 width=128)
(actual time=0.015..0.032 rows=12 loops=1)
Index Cond: ("QuestId" = 42)
Buffers: shared hit=3
Planning:
Buffers: shared hit=12
Planning Time: 0.180 ms
Execution Time: 0.050 msCode language: SQL (Structured Query Language) (sql)Resultatet viser blandt andet, hvordan PostgreSQL har tilgået tabellen.
Et Index Scan betyder, at databasen anvender et indeks til at finde relevante rækker, mens et Seq Scan betyder, at tabellen gennemgås sekventielt.
Resultatet indeholder også estimerede omkostninger (cost), forventede og faktiske antal rækker samt målinger af eksekveringstiden.
Ved at sammenligne rows med actual rows kan jeg undersøge, hvor præcist PostgreSQL har estimeret forespørgslens resultat.
Med BUFFERS får jeg desuden oplysninger om databaseblokke, der eksempelvis er fundet i cache eller læst ind i PostgreSQLs bufferområde.
Disse oplysninger kan hjælpe med at forklare, hvorfor én forespørgsel er mere ressourcekrævende end en anden.
Det er dog vigtigt at skelne mellem EXPLAIN ANALYZE og kommandoen ANALYZE, som alene indsamler statistik om databasens indhold til brug for query planneren.
Jeg har også undersøgt, hvordan dette kan kombineres med EF Core.
Metoden ToQueryString() gør det muligt at inspicere den SQL, som EF Core genererer fra en LINQ-forespørgsel. Denne kan efterfølgende tilpasses og analyseres direkte i PostgreSQL.
Dermed får jeg mulighed for at undersøge hele sammenhængen fra LINQ i backend til SQL-eksekvering i databasen.
Planlagt afprøvning i QuestGame
Mit næste skridt bliver at anvende EXPLAIN ANALYZE på udvalgte forespørgsler i QuestGame.
Jeg vil begynde med tre centrale operationer: opslag af QuestItems tilhørende en Quest, opslag af QuestSessions via spilkode og kontrol af, om en spiller allerede deltager i en bestemt QuestSession.
Disse operationer er interessante, fordi de benytter forskellige indeks, herunder almindelige B-tree-indeks og unikke, sammensatte indeks.
Først vil jeg undersøge de SQL-forespørgsler, som EF Core genererer. Derefter vil jeg udføre dem med EXPLAIN (ANALYZE, BUFFERS) i et isoleret testmiljø.
Jeg vil registrere eksekveringstid, valgte scan-metoder, bufferforbrug og forskelle mellem estimerede og faktiske antal rækker.
For at skabe et mere realistisk grundlag vil jeg også undersøge mulighederne for at indsætte større mængder testdata. Da vores database stadig er relativt lille, forventer jeg ikke nødvendigvis, at indeks vil give nogen nævneværdig performanceforbedring på nuværende tidspunkt.
Med et større datasæt kan jeg sammenligne målinger før og efter eventuelle ændringer i indekseringen. Målingerne skal gentages under sammenlignelige forhold, så tilfældige udsving og caching ikke bliver afgørende for vurderingen.
Jeg vil desuden undersøge, hvordan vores PostGIS-forespørgsler håndteres, særligt om geografisk validering af kendte QuestSessionItems fortsat fungerer hensigtsmæssigt uden et GiST-indeks på sessionernes koordinater.
Formålet er ikke nødvendigvis at ændre den eksisterende implementering, men at få erfaring med systematisk måling og evaluering af databaseperformance.
Resultaterne vil jeg dokumentere som en del af min portefølje, så jeg efterfølgende kan vurdere, om vores nuværende indeksering er hensigtsmæssig, og hvilke optimeringer der eventuelt bliver relevante, efterhånden som QuestGame udvikles.
