Plans d'exécution

Dans ce TP nous allons manipuler la commande EXPLAIN pour comprendre le plan d'exécution de plusieurs requêtes

Si besoin, réimporter le jeu de données Pagila

Une requête simple

Commençons par quelque chose de simple :

EXPLAIN SELECT * FROM film;

Combien de lignes PostgreSQL a-t-il prévu de récupérer ? Quelle est la taille en octet de chaque ligne ?
Exécuter effectivement la commande avec un EXPLAIN ANALYSE
Combien de lignes sont effectivement récupérées ? Pourquoi ?
Mettre à jour les statistiques de la base

Affichage des buffers

Avant d'exécuter les commandes suivantes, nous avons besoin de vider le cache de PostgreSQL

Exécuter les commandes suivantes pour vider le cache :

sudo su -
service postgresql stop
sync
echo 3 > /proc/sys/vm/drop_caches
service postgresql start

Exécuter à nouveau l'EXPLAIN précédent, en ajoutant l'option BUFFERS (ne rien exécuter d'autre avant)

EXPLAIN (ANALYSE, BUFFERS) SELECT * FROM film;

Combien de temps a duré la requête ? Que s'est-il passé au niveau de la mémoire partagée ?
Exécuter à nouveau la requête précédente à l'identique
Qu'est-ce qui a changé ?

Exemples plus complexes :

Expliquer les plans d'exécution des requêtes suivantes :

1.

EXPLAIN (ANALYSE, BUFFERS) SELECT s.staff_id AS id,
       a.address,
       a.postal_code AS "zip code",
       a.phone,
       s.store_id AS sid
FROM staff s
         JOIN address a ON s.address_id = a.address_id;

2.

EXPLAIN (ANALYSE, BUFFERS)
SELECT f.title, f.length, a.first_name, a.last_name, c.name
FROM film f
         LEFT JOIN film_actor fa on f.film_id = fa.film_id
         LEFT JOIN actor a on fa.actor_id = a.actor_id
         LEFT JOIN film_category fc on f.film_id = fc.film_id
         LEFT JOIN category c on fc.category_id = c.category_id
WHERE length > 100
ORDER BY title
LIMIT 200;

Cas particulier, film_list est une vue (SELECT pg_get_viewdef('film_list', true);)

EXPLAIN (ANALYSE, BUFFERS) SELECT * FROM film_list;

Cas particulier, payment est partitionnée

EXPLAIN (ANALYSE, BUFFERS) SELECT * FROM payment;
EXPLAIN (ANALYSE, BUFFERS) SELECT * FROM payment WHERE payment_date >= '2007-01-01' AND payment_date <= '2007-01-31';