JCL

FEUP/DEI & INESC TEC

User Tools

Site Tools


teach:lbaw:lectures:04

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

teach:lbaw:lectures:04 [28/02/2020 16:08]
teach:lbaw:lectures:04 [04/03/2021 21:56] (current)
Correia Lopes created
Line 1: Line 1:
 +====== L: 08/03/2021 ======
  
 +**Master in Informatics and Computing Engineering\\
 +Database and Web Applications Laboratory\\
 +Instance: 2020/21**
 +\\
 +---
 +\\
 +
 +====== Lecture #4 :: 08/03/2021 ======
 +
 +===== 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 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.
 +
 +===== Content =====
 +
 +  -  Introduction
 +    * Query optimisation
 +    * EXPLAIN query plans
 +  - Physical Database Schema
 +    * The workload
 +    * Queries
 +    * Updates
 +    * Performance
 +    * Physical Schema
 +  - Select indexes
 +    * Indexes
 +    * Clustering
 +    * Examples
 +    * Index-only plans
 +  - PostgreSQL
 +    * Indexes.
 +    * Full text search
 +  - 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 13.2 Documentation//. Online in http://www.postgresql.org/docs/13/static/index.html, last accessed on March 2021
 +
 +===== Materials =====
 +
 +  * Illustrations presented in class: {{05-tuningphysical.pdf|Database Indexes}}
 +  * J. Correia Lopes, Sérgio Nunes. [[..:artefacts/index#ii_database_specification_ebd_20|Database Specification (EBD)]], March 2021
 +  * PostgreSQL. [[http://www.postgresql.org/docs/13/static/indexes.html|Indexes]], last accessed on March 2021
 +  * PostgreSQL. [[http://www.postgresql.org/docs/13/static/indexes-examine.html|Examining Index Usage]], last accessed on March 2021
 +
 +===== Summary =====
 +
 +  * Database Physical Schema. Indexes. PostgreSQL. Tuning the Conceptual Schema: denormalisation, vertical decomposition, tuning queries and views. Database tuning. By André Restivo.
 +
 + --- //LBAW, 2020/21//
 +
 +[[03|« Previous]] | [[index|Index]] | [[05|Next »]]