TP : Optimisation de requêtes

On considère le schéma conceptuel suivant.

---

mocodo_notebook/sandbox_mld.md

• clients (numc, nomc, adressec)
• commandes (#numc, datecom, commentaire)
• concerne (#numc, #datecom, #nump)
• livraisons (#numc, #datecom, dateliv, prestataire)
• produits (nump, nomp, descriptif)

On souhaite prévoir, avant sa mise en production, le comportement des requêtes sur cette base de données.

Une base de données synthétique a été créée dans votre espace dans le schéma "optimisation". Observez les tables créées, et notamment leur nombre de tuples.

Puis lancez la commande suivante pour mettre à jour les statistiques

analyse optimisation.clients, optimisation.commandes, optimisation.produits, optimisation.concerne,optimisation.livraisons;

Partie 1 : Observation des plans d'exécution

Pour chaque question suivante, rédigez une requête SQL qui répond à la question, et demandez à Postgresql une analyse de la requête de la façon suivante :

EXPLAIN (analyse,buffers) 
select ... 
from...
etc...

La commande EXPLAIN a pour effet de vous afficher le plan d'exécution choisi par l'optimiseur de requêtes. L'option "analyse" entre parenthèses va entrainer l'exécution effective de la requête, ce qui permet de comparer le coût du plan avec le temps réel, ainsi que le nombre réel de tuples générés à chaque étape. Enfin, "buffers" permet d'afficher le nombre de blocs lus, soit dans le cache (shared hits) soit sur le disque (shared read).

Dans chaque cas, observez et reportez en commentaire dans votre TP, sous la requête effectuée :

• Le coût total estimé pour le plan d'exécution et le temps réel de l'exécution
• Le nombre de tuples estimé en sortie et la taille en octets de chaque tuple
• Le nombre de blocs lus
• Les chemins d'accès utilisés vers les tables
• Les algorithmes utilisés
• Le noeud le plus coûteux de l'arbre

Pour la visualisation des plans d'exécution, vous pourrez vous aider du site Dalibo.

1. Listez toutes les informations sur les produits.

2. Listez le numéro et le nom de tous les produits.

3. Idem, en ajoutant la clause "distinct".

4. Même question en ordonnant le résultat selon le nom des produits.

5. Listez les produits dont le nom est 'nomp_327'.

6. Donnez le nombre de commandes par client (numc, nombre).

7. Donnez les infos sur chaque commande, sous la forme ('nom du client', 'date de la commande'). Quelle est la méthode de jointure utilisée ? Pourquoi les relations sont elles considérées dans cet ordre dans le plan d'exécution ? Pourquoi la table commande n'est pas utilisée en fait ? Vous pouvez écrire différentes variations syntaxiques de la jointure pour observer ce qui se passe.

8. On souhaite afficher les produits commandés par les clients. étudiez le plan d'exécution de la requête suivante, observez le coût et le volume de tuples attendu, commentez. Ré-écrivez la requête pour l'améliorer.

explain -- Attention, pas de mot clé "analyse" ici car la requête ne termine pas !
select distinct nomc, nomp
from clients, produits, concerne, commandes, livraisons
where	clients.numC = commandes.numc
	and commandes.datecom = concerne.datecom
	and commandes.numc=concerne.numc
	and produits.nump=concerne.nump

Partie 2 : Optimiser par des index et des ré-écritures

Dans cet exercice, on tentera des stratégies de rédaction de requêtes et d'indexation de façon à optimiser chaque question. Afin de ne pas avoir d'interactions entre les index de chaque question, ils seront supprimés. Pour cela, vous utiliserez des transactions annulées à la fin sur le modèle suivant :

dans un premier temps, désactiver la validation automatique du JUPYSQL :

%config SqlMagic.autocommit = False #commit automatique après chaque requête

Puis créez et testez des stratégies d'index :

begin;
  create index ... ;
  create index ... ;
  ... 

  Explain analyse
  <Requête à expliciter>

Enfin, dans la cellule suivante, annulez la transaction en cours :

rollback; -- supprime les index créés dans la transaction, sans effet de bord

1. Observez dans les deux requêtes suivantes l'utilisation de l'index de clé primaires, et trouvez dans la documentation de PostgreSQL à quoi correspondent les chemins d'accès.

Select *
    from commandes
    where	numC='109000'
    	    and	datecom='2020-03-31';

Select *
    from commandes
    where dateCom='2020-03-31';

Même question pour la requête suivante. Que constatez-vous ? Comment l'expliquer ?

Select *
from commandes
where numC='109000';

En conclusion de la question, que faire si dans les requêtes sur "commandes", la recherche par numéro de client est beaucoup plus fréquente que la recherche par date de commande ?

2. Optimisez par deux index la requête suivante.

-- Noms de produits commandés par les clients qui portent le nom : 'nomc_1287'

Select nomp
from optimisation.produits p 
    join optimisation.concerne co using(nump) 
    join optimisation.clients c using(numc)
where nomc='nomc_1287';

3. Des limites du SQL déclaratif... comparer les plans d'exécution des requêtes ci-dessous.

-- Liste des numéros de clients qui n'ont aucune livraison qui les concerne.
  
   
  Select numc
  from clients 
  where numc not in (select numc from livraisons); 
    
   
  Select numc
  from clients 
      except
  select numc 
  from livraisons; 
   
   
  Select numc
  from clients 
  where not exists (select numc from livraisons
                    where livraisons.numc=clients.numc);

4. Optimiser la requête suivante, qui est souvent utilisée pour éviter de manquer des recherches pour un problème de casse.

-- Recherche et affichage des clients avec passage en majuscules
    
explain analyse
select upper(nomc), adressec
from clients
where upper(nomc)='NOMC_1725';

5. Dans cette question, la clé primaire est-elle utilisée ? Pourquoi ? Plutôt que de créer un nouvel index coûteux, proposez une ré-écriture de la requête qui utilisera l'index existant via la clé primaire.

SELECT COUNT(*)
  FROM commandes
  WHERE EXTRACT(YEAR FROM datecom) = 2017

Partie 3 : les mises à jour

Créer un index sur une clé étrangère n'est pas toujours une évidence. En effet, la clé étrangère pointe vers des tuples d'une table distante qui possède une clé UNIQUE dessus, donc rapidement retrouvables. Mais cela peut être justifié dans des environnements qui feraient des mises à jour incessantes en cascade.

1. La requête suivante efface les 200 commandes les plus anciennes. Observez d'abord le plan d'exécution (EXPLAIN mais sans ANALYSE). Puis, dans un deuxième temps, remplacez "explain" par "explain analyse".

begin;
  explain
  delete from commandes
  where datecom in (  select datecom	
  				            from commandes
  			            	order by datecom
  			            	limit 200);
  rollback;

Comment expliquer finalement le temps qui est consacré à ces suppressions ? Créez l'index qui optimisera l'exécution de cette tâche.

Partie 4 - Réalisez un partitionnement de la relation "concerne" selon la date de commande, en trois groupes :

• Les lignes qui datent de plus de 3 ans ;
• les lignes qui portent sur les années n-1 et n-2 ;
• Les lignes de l'année en cours.

Vous repartirez du script de création en refaisant un nouveau schéma "optimisationpartition". Une fois la base partitionnée construite, effectuez des recopies des relations "optimisation" dans "optimisationpartition".

Testez quelques requêtes pour observer l'utilisation des partitions.