🔝 Retour au Sommaire

18.4.3. Pushdown d'Opérations et Performance

Introduction

Lorsque vous utilisez des Foreign Data Wrappers (FDW) pour accéder à des données distantes, les performances deviennent un enjeu crucial. Le pushdown d'opérations (ou "query pushdown") est le mécanisme qui détermine si PostgreSQL est intelligent ou non dans la manière dont il traite vos requêtes sur des tables distantes.

Analogie simple : Le restaurant et la cuisine

Imaginez que vous êtes dans un restaurant (PostgreSQL local) et que vous commandez un plat qui nécessite des ingrédients stockés dans un entrepôt distant (serveur distant via FDW).

Scénario 1 : Sans pushdown (inefficace) 🐌

Le serveur demande à l'entrepôt : "Envoyez-moi TOUS vos ingrédients"
L'entrepôt envoie 10 000 boîtes d'ingrédients
Le restaurant trie, filtre et prépare uniquement ce dont il a besoin
Les 9 990 autres boîtes sont jetées

Scénario 2 : Avec pushdown (efficace) 🚀

Le serveur demande à l'entrepôt : "Envoyez-moi uniquement 10 boîtes de tomates italiennes"
L'entrepôt trie et filtre sur place
L'entrepôt envoie exactement 10 boîtes
Le restaurant reçoit seulement ce dont il a besoin

Le pushdown consiste à envoyer le travail de filtrage, tri et agrégation directement vers le serveur distant, plutôt que de tout rapatrier localement.

Qu'est-ce que le Pushdown d'Opérations ?

Définition

Le pushdown d'opérations (query pushdown) est la capacité de PostgreSQL à déléguer certaines parties d'une requête SQL au serveur distant, plutôt que de rapatrier toutes les données pour les traiter localement.

Schéma conceptuel

Sans Pushdown (Inefficace)
═══════════════════════════════════════════════════════════════

┌─────────────────────────────────────┐
│  PostgreSQL Local                   │
│                                     │
│  SELECT nom, prix                   │
│  FROM produits_distants             │
│  WHERE categorie = 'Électronique'   │
│  ORDER BY prix DESC                 │
│  LIMIT 10;                          │
└─────────────┬───────────────────────┘
              │ Requête FDW simple:
              │ "SELECT * FROM produits"
              │
┌─────────────▼───────────────────────┐
│  Serveur Distant                    │
│                                     │
│  Envoie 1 000 000 lignes ────────┐  │
│  (toute la table)                │  │
└──────────────────────────────────┼──┘
                                   │
        ┌──────────────────────────┘
        │ Transfert réseau : 1 000 000 lignes
        │ (très lent !)
        │
┌───────▼──────────────────────────────┐
│  PostgreSQL Local                    │
│                                      │
│  Filtre WHERE (localement)           │
│  Tri ORDER BY (localement)           │
│  LIMIT 10 (localement)               │
│                                      │
│  Résultat final : 10 lignes          │
└──────────────────────────────────────┘

═══════════════════════════════════════════════════════════════

Avec Pushdown (Efficace)
═══════════════════════════════════════════════════════════════

┌─────────────────────────────────────┐
│  PostgreSQL Local                   │
│                                     │
│  SELECT nom, prix                   │
│  FROM produits_distants             │
│  WHERE categorie = 'Électronique'   │
│  ORDER BY prix DESC                 │
│  LIMIT 10;                          │
└─────────────┬───────────────────────┘
              │ Requête FDW intelligente:
              │ "SELECT nom, prix FROM produits
              │  WHERE categorie = 'Électronique'
              │  ORDER BY prix DESC
              │  LIMIT 10"
              │
┌─────────────▼───────────────────────┐
│  Serveur Distant                    │
│                                     │
│  Filtre WHERE (distant)             │
│  Tri ORDER BY (distant)             │
│  LIMIT 10 (distant)                 │
│                                     │
│  Résultat : 10 lignes ──────────┐   │
└─────────────────────────────────┼───┘
                                  │
        ┌─────────────────────────┘
        │ Transfert réseau : 10 lignes seulement
        │ (très rapide !)
        │
┌───────▼──────────────────────────────┐
│  PostgreSQL Local                    │
│                                      │
│  Reçoit directement le résultat      │
│  Résultat final : 10 lignes          │
└──────────────────────────────────────┘

Impact sur les performances

Exemple concret :

-- Table distante avec 10 millions de lignes
SELECT nom, prix  
FROM produits_distants  
WHERE categorie = 'Électronique'  
  AND prix > 100
LIMIT 10;

Méthode	Lignes transférées	Temps d'exécution	Bande passante
Sans pushdown	10 000 000 lignes	~120 secondes	~2 Go
Avec pushdown	10 lignes	~0.05 secondes	~1 Ko

Différence : 2400× plus rapide ! 🚀

Types d'Opérations Poussables

Classification des opérations

1. Sélection de colonnes (Projection)

Poussable : ✅ Oui (tous les FDW)

-- PostgreSQL envoie seulement les colonnes demandées
SELECT id, nom, prix FROM produits_distants;

-- Au lieu de
SELECT * FROM produits_distants;

Bénéfice : Réduit la taille des données transférées.

2. Filtrage (WHERE clause)

Poussable : ✅ Oui (dépend de l'opérateur et du FDW)

SELECT * FROM clients_distants  
WHERE pays = 'France'  
  AND age >= 18;

Opérateurs généralement poussables :

Opérateur	postgres_fdw	oracle_fdw	mysql_fdw	file_fdw
`=`, `<>`	✅	✅	✅	❌
`<`, `>`, `<=`, `>=`	✅	✅	✅	❌
`IN`, `NOT IN`	✅	✅	✅	❌
`LIKE`, `ILIKE`	✅	✅	⚠️ Partiel	❌
`BETWEEN`	✅	✅	✅	❌
`IS NULL`, `IS NOT NULL`	✅	✅	✅	❌
`AND`, `OR`, `NOT`	✅	✅	✅	❌

Fonctions NON poussables :

Fonctions personnalisées (UDF)
Fonctions volatiles (comme random())
Fonctions PostgreSQL spécifiques non supportées par la base distante

3. Tri (ORDER BY)

Poussable : ✅ Oui (postgres_fdw, oracle_fdw, mysql_fdw)

SELECT * FROM commandes_distantes  
ORDER BY date_commande DESC;

Bénéfice : Le tri se fait sur le serveur distant, évitant le transfert puis le tri local.

4. Limitation (LIMIT / OFFSET)

Poussable : ✅ Oui (postgres_fdw, oracle_fdw, mysql_fdw)

SELECT * FROM produits_distants  
ORDER BY prix DESC  
LIMIT 10;

Impact énorme : Au lieu de transférer toute la table triée, seules les 10 premières lignes sont envoyées.

5. Agrégations (GROUP BY, COUNT, SUM, etc.)

Poussable : ✅ Oui (depuis PostgreSQL 10+ pour postgres_fdw)

SELECT
    categorie,
    COUNT(*) AS nb_produits,
    AVG(prix) AS prix_moyen
FROM produits_distants  
GROUP BY categorie;

Fonctions d'agrégation poussables :

Fonction	postgres_fdw	oracle_fdw	mysql_fdw
`COUNT(*)`	✅	✅	✅
`COUNT(DISTINCT)`	✅	✅	✅
`SUM`, `AVG`	✅	✅	✅
`MIN`, `MAX`	✅	✅	✅
`STDDEV`, `VARIANCE`	✅	⚠️ Dépend	⚠️ Dépend
Agrégations personnalisées	❌	❌	❌

6. Jointures

Poussable : ✅ Oui (sous conditions)

Cas 1 : Jointure entre deux foreign tables du MÊME serveur distant

-- Les deux tables sont sur le même serveur Oracle
SELECT
    c.nom,
    o.montant
FROM clients_oracle c  
INNER JOIN commandes_oracle o ON c.id = o.client_id;

Résultat : La jointure est effectuée sur le serveur Oracle distant. ✅

Cas 2 : Jointure entre foreign table et table locale

-- clients_distants (distant) + ventes_locales (local)
SELECT
    c.nom,
    v.montant
FROM clients_distants c  
INNER JOIN ventes_locales v ON c.id = v.client_id;

Résultat : La foreign table est rapatriée localement, puis la jointure est effectuée sur PostgreSQL local. ❌

Cas 3 : Jointure entre foreign tables de serveurs DIFFÉRENTS

-- clients_oracle (serveur Oracle) + produits_mysql (serveur MySQL)
SELECT
    c.nom,
    p.nom_produit
FROM clients_oracle c  
INNER JOIN produits_mysql p ON c.produit_favori = p.id;

Résultat : Les deux tables sont rapatriées localement, puis jointure locale. ❌

7. Sous-requêtes

Poussable : ⚠️ Partiel (selon la complexité)

-- Sous-requête simple : peut être poussée
SELECT * FROM produits_distants  
WHERE prix > (SELECT AVG(prix) FROM produits_distants);  

-- Sous-requête corrélée : généralement NON poussée
SELECT * FROM commandes_distantes c  
WHERE montant > (  
    SELECT AVG(montant)
    FROM commandes_distantes
    WHERE client_id = c.client_id
);

8. CTE (Common Table Expressions)

Poussable : ⚠️ Variable

WITH clients_actifs AS (
    SELECT * FROM clients_distants
    WHERE derniere_commande > NOW() - INTERVAL '1 year'
)
SELECT nom FROM clients_actifs;

Le comportement dépend du planificateur PostgreSQL et de la complexité de la CTE.

Vérifier le Pushdown avec EXPLAIN

La commande EXPLAIN

EXPLAIN est l'outil essentiel pour comprendre ce qui est poussé vers le serveur distant.

Syntaxe de base

EXPLAIN (VERBOSE, ANALYZE)  
SELECT * FROM produits_distants WHERE categorie = 'Électronique';

Options importantes :

VERBOSE : Affiche le SQL distant (Remote SQL)
ANALYZE : Exécute réellement la requête et affiche les temps
BUFFERS : Affiche l'utilisation des buffers (utile pour les performances)

Exemple d'analyse

Requête avec bon pushdown :

EXPLAIN (VERBOSE)  
SELECT nom, prix  
FROM produits_distants  
WHERE categorie = 'Électronique'  
  AND prix > 100
ORDER BY prix DESC  
LIMIT 10;

Résultat :

Foreign Scan on public.produits_distants
  Output: nom, prix
  Remote SQL: SELECT nom, prix
              FROM public.produits
              WHERE ((categorie = 'Électronique'::text))
                AND ((prix > '100'::numeric))
              ORDER BY prix DESC
              LIMIT 10

Analyse : ✅ Le WHERE est poussé (categorie = 'Électronique' AND prix > 100)
✅ L'ORDER BY est poussé
✅ Le LIMIT est poussé
✅ Seules les colonnes nécessaires sont sélectionnées (nom, prix)

Requête avec mauvais pushdown :

EXPLAIN (VERBOSE)  
SELECT nom, upper(description)  
FROM produits_distants  
WHERE extract_custom_field(description) = 'valeur';

Résultat :

Foreign Scan on public.produits_distants
  Output: nom, upper(description)
  Filter: (extract_custom_field(description) = 'valeur'::text)
  Remote SQL: SELECT nom, description FROM public.produits

Analyse : ❌ Le WHERE n'est pas poussé (fonction personnalisée extract_custom_field)
❌ Le filtrage se fait localement (Filter: dans le plan)
⚠️ Toute la table produits est transférée

Lire le plan d'exécution

Mots-clés à surveiller

Mot-clé	Signification	Bon/Mauvais
`Remote SQL`	Le SQL exécuté à distance	ℹ️ Info
`Filter:`	Filtrage LOCAL (après transfert)	❌ Mauvais
`Sort` (local)	Tri LOCAL	❌ Mauvais
`Aggregate` (local)	Agrégation LOCALE	❌ Mauvais
Tout dans `Remote SQL`	Tout est poussé	✅ Excellent

Exemple complet avec ANALYZE

EXPLAIN (ANALYZE, VERBOSE, BUFFERS)  
SELECT pays, COUNT(*)  
FROM clients_distants  
WHERE date_inscription > '2024-01-01'  
GROUP BY pays;

Résultat idéal (bon pushdown) :

Foreign Scan on public.clients_distants
  Output: pays, (count(*))
  Remote SQL: SELECT pays, count(*)
              FROM public.clients
              WHERE (date_inscription > '2024-01-01'::date)
              GROUP BY pays
Planning Time: 0.523 ms  
Execution Time: 45.234 ms

Temps : 45 ms → très rapide

Résultat problématique (mauvais pushdown) :

Aggregate
  Output: pays, count(*)
  Group Key: clients_distants.pays
  ->  Foreign Scan on public.clients_distants
        Output: pays, id, nom, date_inscription
        Filter: (date_inscription > '2024-01-01'::date)
        Remote SQL: SELECT pays, id, nom, date_inscription
                    FROM public.clients
Planning Time: 0.612 ms  
Execution Time: 12453.789 ms

Temps : 12 453 ms → très lent (12 secondes !)

Problème :

Le WHERE n'est pas poussé (local Filter:)
Le GROUP BY n'est pas poussé (local Aggregate)
Toutes les lignes sont transférées puis agrégées localement

Optimisation des Performances

Stratégies d'optimisation

1. Maximiser le pushdown

❌ Mauvais : Fonction PostgreSQL non poussable

SELECT * FROM clients_distants  
WHERE to_char(date_inscription, 'YYYY') = '2024';

✅ Bon : Opérateur standard poussable

SELECT * FROM clients_distants  
WHERE date_inscription >= '2024-01-01'  
  AND date_inscription < '2025-01-01';

❌ Mauvais : Expression complexe

SELECT * FROM produits_distants  
WHERE CASE WHEN prix > 100 THEN 'Cher' ELSE 'Pas cher' END = 'Cher';

✅ Bon : Condition simple

SELECT * FROM produits_distants  
WHERE prix > 100;

2. Limiter les colonnes sélectionnées

❌ Mauvais : Sélection de toutes les colonnes

SELECT * FROM clients_distants WHERE pays = 'France';

Si la table a 50 colonnes, vous transférez 50 colonnes.

✅ Bon : Sélection ciblée

SELECT id, nom, email FROM clients_distants WHERE pays = 'France';

Vous transférez seulement 3 colonnes.

Impact : Réduction de 90%+ du volume de données transféré.

3. Utiliser LIMIT systématiquement

❌ Mauvais : Pas de limitation

SELECT * FROM logs_distants  
ORDER BY timestamp DESC;

Transfère potentiellement des millions de lignes.

✅ Bon : Limitation explicite

SELECT * FROM logs_distants  
ORDER BY timestamp DESC  
LIMIT 100;

Transfère seulement 100 lignes.

4. Utiliser les index côté distant

Les index sur le serveur distant sont utilisés lors du pushdown.

Sur le serveur distant :

-- Créer un index sur la colonne filtrée
CREATE INDEX idx_clients_pays ON clients(pays);  
CREATE INDEX idx_produits_categorie ON produits(categorie, prix);

Sur PostgreSQL local :

-- Cette requête bénéficiera de l'index distant
SELECT * FROM clients_distants WHERE pays = 'France';

Vérifier :

EXPLAIN (ANALYZE, VERBOSE)  
SELECT * FROM clients_distants WHERE pays = 'France';

Si le plan distant montre Index Scan, l'index est utilisé. ✅

5. Ajuster fetch_size

Le paramètre fetch_size contrôle le nombre de lignes récupérées par lot depuis le serveur distant.

Par défaut : fetch_size = 100 (trop petit pour gros volumes)

Configuration serveur :

ALTER SERVER serveur_distant OPTIONS (
    SET fetch_size '10000'  -- 10 000 lignes par batch
);

Configuration table spécifique :

ALTER FOREIGN TABLE clients_distants OPTIONS (
    SET fetch_size '50000'  -- 50 000 lignes pour cette table
);

Règle générale :

Petits résultats (<1000 lignes) : fetch_size = 100-1000
Résultats moyens (1000-100K lignes) : fetch_size = 5000-10000
Gros volumes (>100K lignes) : fetch_size = 10000-50000

Attention : Un fetch_size trop élevé peut consommer beaucoup de mémoire.

6. Utiliser use_remote_estimate

Cette option permet à PostgreSQL d'utiliser les statistiques du serveur distant pour optimiser le plan d'exécution.

ALTER SERVER serveur_distant OPTIONS (
    ADD use_remote_estimate 'true'
);

Bénéfice : Plans d'exécution plus précis, surtout pour les jointures.

Coût : Requête supplémentaire vers le serveur distant pour obtenir les statistiques (léger surcoût).

7. Matérialiser les données fréquemment consultées

Si vous interrogez souvent les mêmes données distantes, matérialisez-les localement.

Vue matérialisée :

CREATE MATERIALIZED VIEW clients_actifs_mv AS  
SELECT * FROM clients_distants  
WHERE derniere_commande > NOW() - INTERVAL '1 year';  

-- Créer des index sur la vue matérialisée
CREATE INDEX idx_clients_mv_pays ON clients_actifs_mv(pays);

-- Rafraîchir périodiquement
REFRESH MATERIALIZED VIEW clients_actifs_mv;

Utilisation :

-- Au lieu de
SELECT * FROM clients_distants WHERE pays = 'France';

-- Utilisez
SELECT * FROM clients_actifs_mv WHERE pays = 'France';

Avantages :

✅ Accès ultra-rapide (local)
✅ Possibilité d'indexation
✅ Pas de latence réseau

Inconvénients :

❌ Données pas en temps réel
❌ Nécessite un rafraîchissement régulier

8. Paralléliser avec plusieurs connexions

Pour postgres_fdw, vous pouvez augmenter le nombre de connexions parallèles.

-- Autoriser 4 connexions parallèles par serveur distant
ALTER SERVER serveur_distant OPTIONS (
    ADD parallel_commit 'true',
    ADD parallel_abort 'true'
);

-- Configuration PostgreSQL
SET max_parallel_workers_per_gather = 4;  
SET parallel_setup_cost = 100;

Cas d'usage : Requêtes lourdes sur gros volumes.

Comparaison des Performances par FDW

Tableau récapitulatif

Critère	postgres_fdw	oracle_fdw	mysql_fdw	file_fdw
Pushdown WHERE	✅✅✅ Excellent	✅✅ Très bon	✅✅ Bon	❌ Aucun
Pushdown ORDER BY	✅✅✅	✅✅	✅✅	❌
Pushdown LIMIT	✅✅✅	✅✅	✅✅	❌
Pushdown JOIN	✅✅✅	✅✅	✅ Limité	❌
Pushdown Agrégations	✅✅✅ (PG 10+)	✅✅	✅ Partiel	❌
Pushdown Sous-requêtes	✅✅	✅ Limité	⚠️ Variable	❌
Use remote estimate	✅	✅	✅	N/A
Parallel query	✅	❌	❌	❌
fetch_size configurable	✅	✅	✅	N/A
Performance globale	⭐⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐	⭐⭐

Benchmark indicatif

Scénario : Table de 10 millions de lignes, requête filtrant 1000 lignes.

SELECT * FROM table_distante WHERE status = 'actif' LIMIT 1000;

FDW	Temps d'exécution	Données transférées
postgres_fdw (avec pushdown)	0.05 s	1000 lignes (~100 Ko)
oracle_fdw (avec pushdown)	0.08 s	1000 lignes (~100 Ko)
mysql_fdw (avec pushdown)	0.12 s	1000 lignes (~100 Ko)
file_fdw (sans pushdown)	45 s	10M lignes (~2 Go)
postgres_fdw (SANS pushdown)	35 s	10M lignes (~2 Go)

Conclusion : Le pushdown fait une différence de 700× en performance ! 🚀

Cas Pratiques d'Optimisation

Cas 1 : Requête analytique lente

Problème :

SELECT
    categorie,
    COUNT(*) AS nb_ventes,
    SUM(montant) AS ca
FROM ventes_distantes  
WHERE date_vente >= '2024-01-01'  
GROUP BY categorie;  

-- Temps d'exécution : 45 secondes

Diagnostic avec EXPLAIN :

EXPLAIN (ANALYZE, VERBOSE)  
SELECT categorie, COUNT(*), SUM(montant)  
FROM ventes_distantes  
WHERE date_vente >= '2024-01-01'  
GROUP BY categorie;

Résultat :

Aggregate (actual time=45234.123..45234.145 rows=10)
  Group Key: categorie
  ->  Foreign Scan (actual time=123.456..43210.789 rows=5000000)
        Remote SQL: SELECT categorie, montant
                    FROM ventes
                    WHERE date_vente >= '2024-01-01'

Problème identifié : L'agrégation (COUNT, SUM) est faite localement après avoir transféré 5 millions de lignes.

Solution 1 : Mettre à jour postgres_fdw (si ancien)

# Vérifier la version
SELECT * FROM pg_available_extensions WHERE name = 'postgres_fdw';

# Si < PostgreSQL 10, l'agrégation n'est pas poussée
# Mettre à jour vers PostgreSQL 10+

Solution 2 : Forcer le pushdown avec une sous-requête

-- Créer une vue distante qui fait l'agrégation
CREATE VIEW ventes_agregees AS  
SELECT  
    categorie,
    COUNT(*) AS nb_ventes,
    SUM(montant) AS ca
FROM ventes  
WHERE date_vente >= '2024-01-01'  
GROUP BY categorie;  

-- Sur PostgreSQL local, référencer cette vue
CREATE FOREIGN TABLE ventes_agregees_fdw (
    categorie TEXT,
    nb_ventes BIGINT,
    ca NUMERIC
)
SERVER serveur_distant  
OPTIONS (schema_name 'public', table_name 'ventes_agregees');  

-- Requête instantanée
SELECT * FROM ventes_agregees_fdw;

-- Temps d'exécution : 0.05 secondes

Cas 2 : Jointure entre distant et local

Problème :

SELECT
    c.nom,
    v.montant
FROM clients_distants c  
INNER JOIN ventes_locales v ON c.id = v.client_id  
WHERE c.pays = 'France';  

-- Temps d'exécution : 2 minutes

Diagnostic :

EXPLAIN (ANALYZE, VERBOSE)  
SELECT c.nom, v.montant  
FROM clients_distants c  
INNER JOIN ventes_locales v ON c.id = v.client_id  
WHERE c.pays = 'France';

Résultat :

Hash Join (actual time=120234.456..120456.789 rows=15000)
  Hash Cond: (v.client_id = c.id)
  ->  Seq Scan on ventes_locales v (actual time=0.012..123.456 rows=100000)
  ->  Hash (actual time=120000.123..120000.456 rows=2000000)
        ->  Foreign Scan on clients_distants c (actual time=45.123..119000.789)
              Remote SQL: SELECT id, nom FROM clients WHERE pays = 'France'

Problème : La table distante clients_distants est entièrement rapatriée (2M lignes), puis jointure locale.

Solution 1 : Matérialiser les clients français

-- Créer une table locale avec les clients français
CREATE TABLE clients_france AS  
SELECT * FROM clients_distants WHERE pays = 'France';  

-- Créer un index
CREATE INDEX idx_clients_france_id ON clients_france(id);

-- Utiliser la table locale
SELECT
    c.nom,
    v.montant
FROM clients_france c  
INNER JOIN ventes_locales v ON c.id = v.client_id;  

-- Temps d'exécution : 0.3 secondes

Solution 2 : Inverser la jointure (si possible)

-- Si ventes_locales est plus petite, la pousser vers la foreign table
-- Créer une foreign table pour ventes_locales sur le serveur distant

-- Puis faire la jointure distante
SELECT
    c.nom,
    v.montant
FROM clients c  
INNER JOIN ventes_locales_fdw v ON c.id = v.client_id  
WHERE c.pays = 'France';

Cas 3 : Pagination lente

Problème :

-- Page 1000 d'une grande table
SELECT * FROM produits_distants  
ORDER BY nom  
LIMIT 100 OFFSET 99900;  

-- Temps d'exécution : 30 secondes

Diagnostic :

EXPLAIN (ANALYZE, VERBOSE)  
SELECT * FROM produits_distants  
ORDER BY nom  
LIMIT 100 OFFSET 99900;

Résultat :

Foreign Scan (actual time=29876.123..29876.456 rows=100)
  Remote SQL: SELECT * FROM produits
              ORDER BY nom
              LIMIT 100 OFFSET 99900

Problème : Même avec pushdown, le serveur distant doit trier et parcourir 100 000 lignes.

Solution : Pagination par clé (keyset pagination)

-- Au lieu d'OFFSET, utiliser WHERE avec la dernière valeur
-- Première page
SELECT * FROM produits_distants  
ORDER BY nom  
LIMIT 100;  

-- Page suivante (nom_dernier_produit est le dernier 'nom' de la page précédente)
SELECT * FROM produits_distants  
WHERE nom > 'nom_dernier_produit'  
ORDER BY nom  
LIMIT 100;  

-- Temps d'exécution : 0.05 secondes (constant, peu importe la page)

Cas 4 : Fonction non poussable

Problème :

SELECT * FROM clients_distants  
WHERE age_category(age) = 'adulte';  

-- Fonction personnalisée PostgreSQL non poussable
-- Temps d'exécution : 15 secondes

Solution 1 : Réécrire sans fonction

SELECT * FROM clients_distants  
WHERE age >= 18 AND age < 65;  

-- Temps d'exécution : 0.05 secondes

Solution 2 : Créer la fonction sur le serveur distant

-- Sur le serveur DISTANT, créer la même fonction
CREATE FUNCTION age_category(age INTEGER) RETURNS TEXT AS $$  
BEGIN  
    IF age < 18 THEN RETURN 'mineur';
    ELSIF age < 65 THEN RETURN 'adulte';
    ELSE RETURN 'senior';
    END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Sur PostgreSQL local, la requête devient poussable
SELECT * FROM clients_distants  
WHERE age_category(age) = 'adulte';  

-- Temps d'exécution : 0.05 secondes

Monitoring des Performances

Métriques clés à surveiller

1. Temps d'exécution des requêtes

-- Activer pg_stat_statements (extension)
CREATE EXTENSION pg_stat_statements;

-- Voir les requêtes FDW les plus lentes
SELECT
    substring(query, 1, 100) AS query_short,
    calls,
    total_exec_time / 1000 AS total_time_sec,
    mean_exec_time / 1000 AS mean_time_sec,
    max_exec_time / 1000 AS max_time_sec
FROM pg_stat_statements  
WHERE query LIKE '%Foreign Scan%'  
ORDER BY total_exec_time DESC  
LIMIT 20;

2. Volume de données transféré

Sur le serveur distant, surveillez le trafic réseau :

-- Vérifier les connexions FDW actives
SELECT
    datname,
    usename,
    application_name,
    client_addr,
    state,
    query
FROM pg_stat_activity  
WHERE application_name LIKE '%fdw%';

Utilisez des outils système :

# Surveiller le trafic réseau
iftop -i eth0  
netstat -i

3. Cache hit ratio

-- Ratio de cache des buffers
SELECT
    schemaname,
    tablename,
    heap_blks_read,
    heap_blks_hit,
    ROUND(
        100.0 * heap_blks_hit / NULLIF(heap_blks_hit + heap_blks_read, 0),
        2
    ) AS cache_hit_ratio
FROM pg_statio_user_tables  
ORDER BY cache_hit_ratio ASC  
LIMIT 20;

Un faible ratio peut indiquer des problèmes de mémoire ou de performance.

4. Nombre de connexions ouvertes

-- Comptage des connexions par serveur distant
SELECT
    application_name,
    COUNT(*) AS nb_connexions
FROM pg_stat_activity  
WHERE application_name LIKE '%fdw%'  
GROUP BY application_name;

Trop de connexions peuvent saturer le serveur distant.

Outils de monitoring

1. pg_stat_statements (extension)

CREATE EXTENSION pg_stat_statements;

-- Configuration dans postgresql.conf
shared_preload_libraries = 'pg_stat_statements'  
pg_stat_statements.track = all

2. auto_explain (pour logs automatiques)

-- Configuration dans postgresql.conf
shared_preload_libraries = 'auto_explain'  
auto_explain.log_min_duration = 1000  -- Log requêtes > 1 seconde  
auto_explain.log_analyze = true  
auto_explain.log_verbose = true

Les plans d'exécution apparaîtront dans les logs PostgreSQL.

3. pgBadger (analyse de logs)

# Générer un rapport HTML depuis les logs
pgbadger /var/log/postgresql/postgresql.log -o rapport.html

Identifie les requêtes lentes, les erreurs, etc.

4. Prometheus + Grafana

Utilisez postgres_exporter pour exporter les métriques vers Prometheus, puis visualisez dans Grafana.

Métriques utiles :

pg_stat_database_xact_commit : Transactions validées
pg_stat_database_tup_fetched : Lignes récupérées
pg_stat_statements_max_exec_time : Temps max des requêtes

Troubleshooting des Problèmes de Performance

Problème 1 : Requête très lente

Étapes de diagnostic :

Vérifier le pushdown :
```
EXPLAIN (VERBOSE) SELECT ... FROM foreign_table WHERE ...;
```
Cherchez Remote SQL et vérifiez que le WHERE y apparaît.

Vérifier les index distants :

-- Sur le serveur DISTANT
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'ma_table';

Ajuster fetch_size :

ALTER FOREIGN TABLE ma_table OPTIONS (SET fetch_size '10000');

Matérialiser si nécessaire :

CREATE MATERIALIZED VIEW ma_table_cache AS
SELECT * FROM ma_table_distante;

Problème 2 : Timeout de connexion

ERROR: could not connect to server "serveur_distant"

Solutions :

-- Augmenter les timeouts
ALTER SERVER serveur_distant OPTIONS (
    ADD connect_timeout '60',        -- 60 secondes pour connexion
    ADD tcp_keepalives_idle '60',
    ADD tcp_keepalives_interval '10'
);

Problème 3 : Mémoire insuffisante

ERROR: out of memory

Solutions :

Réduire fetch_size :

ALTER FOREIGN TABLE ma_table OPTIONS (SET fetch_size '1000');

Augmenter work_mem :
```
SET work_mem = '256MB';
```

Utiliser des curseurs :

BEGIN;
DECLARE cur CURSOR FOR SELECT * FROM foreign_table;
FETCH 1000 FROM cur;
COMMIT;

Problème 4 : Trop de connexions

FATAL: sorry, too many clients already

Solutions :

Augmenter max_connections sur le serveur distant :

-- Dans postgresql.conf (serveur distant)
max_connections = 200

Utiliser un connection pooler (PgBouncer) :

# pgbouncer.ini
[databases]
distant_db = host=serveur-distant port=5432 dbname=prod

[pgbouncer]
pool_mode = transaction
max_client_conn = 100
default_pool_size = 20

Recommandations Finales

Checklist de performance FDW

✅ Avant de créer une foreign table :

Vérifier que le FDW supporte le pushdown
Créer les index nécessaires sur le serveur distant
Définir fetch_size approprié
Activer use_remote_estimate si applicable

✅ Lors de l'écriture de requêtes :

Utiliser EXPLAIN (VERBOSE) systématiquement
Vérifier que les WHERE sont dans Remote SQL
Limiter les colonnes sélectionnées
Utiliser LIMIT quand possible
Éviter les fonctions non poussables

✅ Pour les jointures :

Privilégier les jointures entre foreign tables du même serveur
Matérialiser les tables locales petites utilisées en jointure
Considérer les vues matérialisées pour les données fréquentes

✅ Monitoring continu :

Activer pg_stat_statements
Surveiller les temps d'exécution
Surveiller le volume de données transféré
Analyser les logs avec pgBadger

Règles d'or

Le pushdown est ROI : Investir du temps pour maximiser le pushdown donne des gains énormes.
Mesurer, ne pas deviner : Toujours utiliser EXPLAIN pour comprendre ce qui se passe réellement.
Matérialiser intelligemment : Pour les données consultées fréquemment et qui changent peu, la matérialisation est souvent la meilleure solution.
Optimiser les deux côtés : Index et configuration côté distant sont aussi importants que côté local.
Latence réseau = ennemi #1 : Chaque aller-retour coûte cher. Minimisez-les.

Conclusion

Points clés à retenir

✅ Le pushdown d'opérations est le mécanisme qui détermine si le traitement se fait à distance ou localement

✅ Un bon pushdown peut améliorer les performances de 100× à 1000×

✅ postgres_fdw a le meilleur pushdown, suivi d'oracle_fdw et mysql_fdw. file_fdw n'a aucun pushdown.

✅ EXPLAIN (VERBOSE) est votre meilleur ami pour diagnostiquer les problèmes de performance

✅ Les index sur le serveur distant sont essentiels pour de bonnes performances

✅ fetch_size et use_remote_estimate sont des paramètres critiques à ajuster

⚠️ Les jointures entre foreign tables de serveurs différents sont coûteuses

⚠️ Les fonctions personnalisées ne sont généralement pas poussables

🎯 Matérialisez les données fréquemment consultées avec des vues matérialisées

Prochaines étapes

Pour maîtriser les performances FDW :

Pratiquer avec EXPLAIN sur vos propres requêtes
Benchmarker : Comparer les temps avec/sans pushdown
Monitorer : Mettre en place pg_stat_statements et alerting
Optimiser : Itérer sur les requêtes les plus lentes

Ressources

Documentation PostgreSQL - FDW Performance
Explain Visualizer - Outil pour analyser les plans
PgBadger - Analyse de logs
Livre : Mastering PostgreSQL 14 (Chapitre sur FDW)

⏭️ TimescaleDB : Séries temporelles et hypertables

FilesExpand file tree

04.3-pushdown-operations.md

Latest commit

History

04.3-pushdown-operations.md

File metadata and controls

18.4.3. Pushdown d'Opérations et Performance

Introduction

Analogie simple : Le restaurant et la cuisine

Qu'est-ce que le Pushdown d'Opérations ?

Définition

Schéma conceptuel

Impact sur les performances

Types d'Opérations Poussables

Classification des opérations

1. Sélection de colonnes (Projection)

2. Filtrage (WHERE clause)

3. Tri (ORDER BY)

4. Limitation (LIMIT / OFFSET)

5. Agrégations (GROUP BY, COUNT, SUM, etc.)

6. Jointures

7. Sous-requêtes

8. CTE (Common Table Expressions)

Vérifier le Pushdown avec EXPLAIN

La commande EXPLAIN

Syntaxe de base

Exemple d'analyse

Lire le plan d'exécution

Mots-clés à surveiller

Exemple complet avec ANALYZE

Optimisation des Performances

Stratégies d'optimisation

1. Maximiser le pushdown

2. Limiter les colonnes sélectionnées

3. Utiliser LIMIT systématiquement

4. Utiliser les index côté distant

5. Ajuster fetch_size

6. Utiliser use_remote_estimate

7. Matérialiser les données fréquemment consultées

8. Paralléliser avec plusieurs connexions

Comparaison des Performances par FDW

Tableau récapitulatif

Benchmark indicatif

Cas Pratiques d'Optimisation

Cas 1 : Requête analytique lente

Cas 2 : Jointure entre distant et local

Cas 3 : Pagination lente

Cas 4 : Fonction non poussable

Monitoring des Performances

Métriques clés à surveiller

1. Temps d'exécution des requêtes

2. Volume de données transféré

3. Cache hit ratio

4. Nombre de connexions ouvertes

Outils de monitoring

1. pg_stat_statements (extension)

2. auto_explain (pour logs automatiques)

3. pgBadger (analyse de logs)

4. Prometheus + Grafana

Troubleshooting des Problèmes de Performance

Problème 1 : Requête très lente

Problème 2 : Timeout de connexion

Problème 3 : Mémoire insuffisante

Problème 4 : Trop de connexions

Recommandations Finales

Checklist de performance FDW

Règles d'or

Conclusion

Points clés à retenir

Prochaines étapes

Ressources