Master in Informatics and Computing Engineering
Database and Web Applications Laboratory
Instance: 2019/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

• Illustrations presented in class: Database Indexes
• Video recording of the class: Shared Drive da UP
• J. Correia Lopes, Sérgio Nunes. Database Specification (EBD), March 2020
• PostgreSQL. Indexes, last accessed on March 2020
• PostgreSQL. Examining Index Usage, 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.¹⁾

— LBAW, 2019/20

« Previous | Index | Next »