Aufgabe 07 - IMDB Index Optimierung

Aufgabe 07 - IMDB Index Optimierung

Worum geht es?

In dieser Übung wird die Datenbank aus Aufgabe 06 gezielt beschleunigt: Abfragen werden ohne und mit Indizes gemessen, der Speed-Up berechnet und mit EXPLAIN ANALYZE erklärt, warum ein Index wirkt oder eben nicht (siehe Kapitel 3 - Performance).

Was Sie dafür brauchen

• Die importierte imdb-Datenbank aus Aufgabe 06.
• psql mit \timing und EXPLAIN ANALYZE.

Welche Kompetenzen Sie erwerben und zeigen

• Sie messen Laufzeiten reproduzierbar.
• Sie entwerfen sinnvolle einfache und zusammengesetzte Indizes.
• Sie berechnen den Speed-Up und vergleichen systematisch.
• Sie lesen Abfragepläne und erklären, warum ein Index greift oder ignoriert wird.

Pädagogische Einordnung

• Reproduktion: Abfragen ohne Indizes messen (Teile A und B).
• Reorganisation und Transfer: Indizes entwerfen und anlegen (Teil C).
• Reflexion, Problemlösung und Urteilsbildung: Pläne analysieren und die Wirkung beurteilen (Teil D).

Arbeitsaufträge

Die Übung ist auf etwa zwei Stunden ausgelegt. Teil D ist der Expertenteil.

Teil A - Ausgangslage

1. Sicherstellen, dass die IMDB-Daten importiert sind (sonst wie in Aufgabe 06 mit COPY laden) und die Struktur der beteiligten Tabellen kennen (title_basics, title_ratings, title_principals, name_basics).

Teil B - Abfragen ohne Indizes messen

1. \timing on aktivieren und folgende Abfragen ohne zusätzliche Indizes ausführen und die Zeiten notieren: Anzahl Produktionen; Anzahl Personen; Tätigkeitskategorien in title_principals; Produktionen mit mehr als 100.000 Wertungen; die 10 besten Produktionen bzw. Filme mit mehr als 100.000 Wertungen; eine Person über den Namen suchen; deren Produktionen; deren Titel je Jahr als Schauspieler; deren Tätigkeiten.

Teil C - Indizes entwerfen und anlegen

1. Überlegen, welche Spalten die obigen Abfragen beschleunigen, und die geplanten Indizes anlegen. Kandidaten: title_ratings(numVotes), title_ratings(averageRating), title_basics(titleType), title_basics(startYear), name_basics(primaryName), title_principals(nconst), title_principals(tconst) sowie zusammengesetzte wie:

   CREATE INDEX idx_title_ratings_votes_rating
     ON title_ratings (numVotes DESC, averageRating DESC);

Teil D - Expertenteil: Speed-Up und Abfragepläne verstehen

1. Mit Indizes messen und Speed-Up: Alle Abfragen aus Teil B mit den Indizes erneut messen und je Abfrage den Speed-Up berechnen (Zeit ohne / Zeit mit). Ergebnisse in einer Tabelle festhalten.

   Abfrage	ohne Indizes	mit Indizes	Speed-Up
   z. B. Top-Filme	12,8 s	0,9 s	14,2×

2. Pläne lesen: Für mindestens drei Abfragen EXPLAIN ANALYZE einmal ohne und einmal mit Index ausführen und im Plan zeigen, wo aus einem Seq Scan ein Index Scan (oder Bitmap Index Scan) wird.

3. Wo Indizes nicht helfen: Mindestens eine Abfrage finden, die trotz Index kaum schneller wird, und begründen, warum (etwa: sehr geringe Selektivität, eine Funktion auf der Spalte wie LOWER(primaryName) verhindert die Indexnutzung, oder die Abfrage liest ohnehin fast die ganze Tabelle). Für den Funktionsfall zeigen, dass ein funktionaler Index (CREATE INDEX ... ON name_basics (LOWER(primaryName))) das Problem löst.

4. Reflexion: In vier bis fünf Sätzen zusammenfassen, welche Indizes am meisten gebracht haben, welche man im Rückblick weglassen würde und warum Indizes zwar Lesezugriffe beschleunigen, aber Schreibzugriffe und Speicher kosten.

Wissenscheck

1. Wie berechnet man den Speed-Up einer Abfrage?
2. Woran erkennt man in EXPLAIN ANALYZE, dass ein Index genutzt wird?
3. Warum bringt ein Index bei sehr geringer Selektivität wenig?
4. Warum verhindert WHERE LOWER(name) = ... einen gewöhnlichen Index, und was hilft?
5. Welchen Preis haben Indizes trotz schnellerer Lesezugriffe?

Abgabe

• Eine Dokumentation mit der Begründung der Indexwahl, allen Laufzeiten (ohne und mit Indizes), dem berechneten Speed-Up je Abfrage, mindestens drei EXPLAIN ANALYZE-Vergleichen und der Reflexion aus Teil D.

---

HTL Villach, 2025-2026,
https://www.htl-villach.at