J. Correia Lopes

FEUP/DEI & INESC TEC

User Tools

Site Tools


teach:lbaw:lectures:05

T: 15/03/2019

Master in Informatics and Computing Engineering
Database and Web Applications Laboratory
Instance: 2018/2019


Lecture #5 :: 15/03/2019

Goals

By the end of this class, the student should be able to:

  • Describe the workload of the information system and the performance goals.
  • Develop a good Database Physical Scheme.
  • Describe possible adjustments to the database to improve performance.
  • Describe PostgreSQL indexing.
  • Describe the possible adjustments to the logical schema of the database to improve performance.

Content

  1. Introduction
    • Query optimisation
    • EXPLAIN query plans
  2. Physical Database Schema
    • The workload
    • Queries
    • Updates
    • Performance
    • Physical Schema
  3. Select indexes
    • Indexes
    • Clustering
    • Examples
    • Index-only plans
  4. PostgreSQL
    • Indexes.
    • Full text search
  5. Database tuning
    • Tuning the Relational Schema
    • Denormalisation
    • Tuning queries and views
    • Horizontal decomposition

Bibliography

  • R. Ramakrishnan, J. Gehrke. Database Management Systems. McGRAW-Hill International Editions, 3rd Edition, 2003, ISBN=0-07-246563-8 (chapters 8, 12, 16, 20)
  • The PostgreSQL Global Development Group. PostgreSQL 9.4 Documentation. Online in http://www.postgresql.org/docs/9.4/static/index.html, last accessed on March 2019

Materials

Summary

  • [By André Restivo.] Database Physical Schema. Indexes. PostgreSQL. Tuning the Conceptual Schema: denormalisation, vertical decomposition, tuning queries and views. Database tuning.1)

LBAW, 2018/19

« Previous | Index | Next »

1)
Esquema Físico. Índices. PostgreSQL. Afinação do Esquema Lógico: desnormalização, decomposição horizontal, afinar interrogações. Afinação da base de dados.
teach/lbaw/lectures/05.txt · Last modified: 08/03/2019 18:02 by Correia Lopes