User Tools

Site Tools


This is an old revision of the document!

LE05: 13/03/2020

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

Lecture #5 :: 13/03/2020


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

  • Describe the workload of the information system and the performance goals.
  • Develop the 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.


  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


  • 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 2020



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

LBAW, 2019/20

« Previous | Index | Next »

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. Por André Restivo.
teach/lbaw/lectures/05.1585338425.txt.gz · Last modified: 27/03/2020 19:47 by Correia Lopes