Travailler avec des tables temporaires

Améliorer la performance des requêtes dans PostgreSQL

Amy McCarty

Instructor

À propos des tables temporaires

Quoi ?

Table éphémère

Pourquoi ?

Stockage transitoire
Session de base de données
Requêtes multiples
Spécifique à l'utilisateur
Tables lentes

Comment ?

CREATE TEMP TABLE name AS

Structure d'une table TEMP

holiday	holiday_type	country_code
Epiphany	religious	CZE
Epiphany	religious	FRA
Epiphany	religious	USA
Thanksgiving	secular	USA

CREATE TEMP TABLE usa_holidays AS
  SELECT holiday, holiday_type
  FROM world_holidays
  WHERE country_code = 'USA';

Jours fériés aux États‑Unis

holiday	holiday_type
Epiphany	religious
Thanksgiving	secular

Tables volumineuses et lentes

Lente en raison du grand nombre d'enregistrements

Statistiques	World Holidays	USA Holidays
Type	table	temp table
# de lignes	591 444	25

Vues complexes et lentes

Lente à cause de la logique de vue

Schéma montrant que les tables par pays alimentent la vue World Holidays

Statistiques	World Holidays	USA Holidays
Type	view	temp_table
# de lignes	591 444	25
Sources	195	1

Les tables contiennent des données
Les vues contiennent des instructions vers les données

Joindre plusieurs tables à une seule

CREATE TEMP TABLE usa_holidays AS
  SELECT holiday, holiday_type
  FROM world_holidays
  WHERE country_code = 'USA';

WITH religious AS 
(   SELECT usa.holiday, r.initial_yr
     , r.celebration_dt
    FROM religious r
    INNER JOIN usa_holidays usa 
      USING (holiday)  )
, secular AS 
(   SELECT usa.holiday, s.initial_yr
     , s.celebration_dt
    FROM secular s
    INNER JOIN usa_holidays usa
      USING (holiday)  )
, ...

ANALYZE

1 CREATE TEMP TABLE usa_holidays AS
2 SELECT holiday, holiday_type
3 FROM world_holidays
4 WHERE country_code = 'USA';
5 
6 ANALYZE usa_holidays;
7 
8 SELECT * FROM usa_holidays

Planificateur de requêtes (étapes d'exécution)

Plusieurs chefs autour d'un grand chaudron

Statistiques de pg_statistics
Estimations d'exécution

Passons à la pratique !

Améliorer la performance des requêtes dans PostgreSQL